Passing Variables out of Subroutines

Is is possible to pass variables out of a subroutine? I know how to pass them in, but I'm not sure how to get them out. Thanks!

Hello Cmcconnehey,

I am not sure what you're asking. VBA has both a Sub procedure and a Subroutine construct called by the keyword GoSub. My assumption is you are referring to Sub procedures. A variable would be passed to another Sub or Function by including the variable in the call to it, provided of course it takes arguments. The other way would be to declare a variable as Public in the declarations section of a Standard Module. This way any routine can access the variable as well as change it. If I didn't answer your question, you should post an example of what you want to do.

Sincerely,
Leith Ross

You can pass a variable out of a sub either by using Module-wide or Public variables. Or the result can be passed through the arguments.

Module wide variable are declared OUTSIDE the sub, as in this example. resultValue can be used by any routine in the module.

Dim resultValue As Double

Sub test()
  Dim inputValue As Double
  inputValue = 1
  Call increase(inputValue)
  MsgBox resultValue
End Sub

Sub increase(inVal As Double)
    resultValue = inVal + 1
End Sub

If the statment were

Public resultValue As Double

resultValue could be used by any routine in any module. Public variable are sometimes used to pass values to/from a Userform.

The argument method passes the result as an argument of the sub.

Sub test()
  Dim inputValue As Double
  Dim resultValue As Double
  inputValue = 1
  Call increaseArguments(inputValue, resultValue)
  MsgBox resultValue
End Sub

Sub increaseArguments(ByVal inVal As Double, resultVal As Double)
    resultVal = inVal + 1
End Sub

Note that the returning value cannot be passed By Val, it must be passed By Ref.

I use this technique when a "function" returns two values. The following routine takes the polar coordinates of a point and returns the rectangular coords.

Sub polarToRect(theta As Double, r As Double, x As Double, y As Double)
    x = r * Cos(theta)
    y = r * Sin(theta)
End Sub

finaly, you can return the output value in the same variable as the input value. Running testOne and testTwo shows the difference between ByVal and ByRef arguments. (It impressed me the first time I ran it.)

Sub testOne()
    Dim testNum As Integer
    testNum = 1
    MsgBox testNum
    Call incOne(testNum)
    MsgBox testNum
End Sub

Sub incOne(aValue As Integer)
  aValue = aValue + 1
End Sub

Sub testTwo()
    Dim testNum As Integer
    testNum = 1
    MsgBox testNum
    Call incTwo(testNum)
    MsgBox testNum
End Sub

Sub incTwo(ByVal aValue As Integer)
  aValue = aValue + 1
End Sub