Basic programming question. I am writing a macro in excel and need help calling a function updating a variable and then returning the value.
Here is my code
Public thetaValue As Double 'volumetric water content Public Sub Macro1() ' ' Wet-up Curve ' Define variables Dim j As Integer Dim i As Integer ' Define initial conditions thetaValue = 27.87 MsgBox (thetaValue & " initial value") thetaValue = theta(5) MsgBox ("back to main" & thetaValue) End Sub Public Function theta(hAboveGWT As Double) thetaValue = 59.6518 * hAboveGWT ^ -0.12197 MsgBox (thetaValue & " in function") End FunctionThanks!
There is absolutely no need to have a MsgBox in the function, function return a result
Public Function theta(hAboveGWT As nDouble ) As String
theta = 59.6518 * hAboveGWT ^ -0.12197 & "in function"
You shouldn't put the message text in parentheses, that will evaluate the expression and might bite you one day.
Another point is that defining variables as Integer is a bad practice, they only hold values upto 23K+, and the system converts it to Long under the covers, does the work, then converts it back to Integer to return a resuly, a lot of unnecessary work. Better to use Longs in the irst place.
Public Sub Macro1()
' Wet-up Curve '
' Define variables
Dim j As Long
Dim i As Long
' Define initial conditions
thetaValue = 27.87
MsgBox thetaValue & " initial value"
thetaValue = theta(5)
MsgBox "back to main" & thetaValue