none
return value with fuction in VBA for excel

    Question

  • 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 Function
    Thanks!

    Tuesday, May 11, 2010 9:51 PM

Answers

  • This should work

     

    Public
     Function
     theta(hAboveGWT As
     Double
    ) as double
    theta = 59.6518 * hAboveGWT ^ -0.12197
    MsgBox (theta & " in function" )
    End Function
    • Marked as answer by Tim Li Thursday, May 13, 2010 10:50 AM
    Tuesday, May 11, 2010 10:03 PM

All replies

  • This should work

     

    Public
     Function
     theta(hAboveGWT As
     Double
    ) as double
    theta = 59.6518 * hAboveGWT ^ -0.12197
    MsgBox (theta & " in function" )
    End Function
    • Marked as answer by Tim Li Thursday, May 13, 2010 10:50 AM
    Tuesday, May 11, 2010 10:03 PM
  • Refine format:

    Public  Function theta(hAboveGWT As Double) as Double

        theta = 59.6518 * hAboveGWT ^ -0.12197

         MsgBox (theta & " in function")

    End  Function

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Thursday, May 13, 2010 10:53 AM
  • 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"
    End 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
    End Sub

     

    Friday, May 14, 2010 8:59 AM