Excel VBA Formula for multiple inputs

Question

I am trying to write an Excel VBA code that allows me to take values from 5 different cells and run it through a formula to produce a single number. I would like it to work as the prebuilt functions in excel work but can't get the inputs right. This is what I have so far.

Public Function compression(Limit, time, timeset, alpha, beta)
Sub compression(Limit, time, timeset, alpha, beta)
'Limiting Compression Strength = LCS
'Time = t
'Alpha =a
'Beta = b
'Setting Time = ts
'Compression Strength = f
Limit = LCS
time = t
timeset = ts
alpha = a
beta = b
x = -((t - ts) / a) ^ b
y = 1 - Exp(x)
f = y * LCS
Range("targetRange") = f
End Sub
End Function

Any help you can give would be greatly appreciated.

You don't need the `Sub`, the code goes within the `Function` that you should end like this: `Public Function compression(params) As X` being X the type you are returning. Then at the end of the function `compression = f`. You should also declare the input type for all your parameters. — Damian, Feb 26 '21 at 07:33

JohnnieL · Accepted Answer · 2021-02-26T08:14:30.400

A few observations and pointers for a self-declared newbie

Always use option explicit - it will force you to declare all variables so you dont get any errors from wrong types/typos - see here
always declare the type of the variable
always include byref/byval in function signatures - see here
your function needs to return a value
When you define a range or use any of[Range/Cells/Sheets/Worksheets/Workbooks], explicitly specify the parent - "fully qualify"
Try not to change ranges in a function - use subs to do that

Happy coding

Tweaking your code:

option explicit
' underscore is line continuation marker
Public Function compression(byref Limit as double, _
       byref time as double, _
       byref timeset as double, _
       byref alpha as double, _
       byref beta as double) as double
'Limiting Compression Strength = LCS
'Time = t
'Alpha =a
'Beta = b
'Setting Time = ts
'Compression Strength = f

'Limit = LCS
' this is an error, Limit, time, alpha, beta and timeest are passed in and 
' you are setting them to undeclared variables. 
' because you do not have option explcit you are able to use undeclared variables
' they will default to zero in this instance and so you are overwriting the passed values of
' Limit, time, alpha, beta and timeest to be zero with these statements
'time = t
'timeset = ts
'alpha = a
'beta = b
  dim x as double, y as double, f as double

  x = -((time - timeset) / alpha) ^ beta
  y = 1 - Exp(x)
  f = y * Limit
  ' this sets the return value
  compression = f
End Function

Last piece of advice - using the macro recorder is a great way to learn VBA and the object model — JohnnieL, Feb 26 '21 at 08:12

Excel VBA Formula for multiple inputs

1 Answers1