How to Pass Arguments Efficiently in Visual Basic

This is from Microsoft Excel 2000 Visual Basic Help, with changes and additions.

All arguments are passed to procedures by reference, unless you specify otherwise. This is efficient because all arguments passed by reference take the same amount of time to pass and the same amount of space (4 bytes) within a procedure regardless of the argument’s data type.

You can pass an argument by value if you include the ByVal keyword in the procedure’s declaration. Arguments passed by value consume from 2 to 16 bytes within the procedure, depending on the argument’s data type. Larger data types take slightly longer to pass by value than smaller ones. Because of this, String and Variant data types generally should not be passed by value.

Passing an argument by reference uses the original variable. Changes to the argument within the procedure are returned to the original variable.  Thus, a Sub or Function procedure can pass many changed values back to the calling procedure.

Passing an argument by value copies the original variable. Changes to the argument within the procedure are not returned to the original variable. For example:

' Demonstration of ByVal to prevent changing calling variable, using Factorial Function:
Option Explicit

Function FactorialByVal(ByVal MyVar As Long)  ' Correct Function declaration.
  MyVar = MyVar - 1
  If MyVar = 0 Then
    FactorialByVal = 1
    Exit Function
  End If
  FactorialByVal = FactorialByVal(MyVar) * (MyVar + 1)
End Function
 
Function Factorial(MyVar As Long)   ' Function declaration without ByVal.
  MyVar = MyVar - 1
  If MyVar = 0 Then
    Factorial = 1
    Exit Function
  End If
  Factorial = Factorial(MyVar) * (MyVar + 1)    ' Passes by reference
' Factorial = Factorial((MyVar)) * (MyVar + 1)  ' To fix: Pass by value
End Function
 
Sub Demo()    ' Call Factorial with a variable S.
  Dim S As Long
  S = 5
  Debug.Print "S="; S     ' Displays 5 (starting value).
  Debug.Print "Factorial ByVal="; FactorialByVal(S) ' Displays 120 (the factorial of 5)
  Debug.Print "S="; S     ' Displays 5 (unchanged due to ByVal).
  Debug.Print "Factorial="; Factorial(S) ' Erroneously Displays 1
  Debug.Print "S="; S     ' Displays 0 (altered by function).
End Sub

Function FactorialByVal includes ByVal in the function declaration. The second Print statement displays 120, the factorial of 5.  The third Print statement shows that S is unchanged due to ByVal. This is because changes to MyVar are not fed back to variable S.

Function Factorial omits ByVal from the function declaration.  The final two Print statements display 1 and 0. This is because MyVar would then refer back to variable S, which is reduced by 1 until it equals 0.

Because ByVal makes a copy of the argument, it would allow you to pass a variant to the FactorialByVal function above.  You can’t pass a variant by reference if the procedure that declares the argument is another data type.

Using ByVal in the called procedure’s declaration means that the procedure forces passing by value.  Alternatively, the calling procedure can pass by value.  We do this by enclosing each such variable in parentheses.  Thus, the calls Factorial((MyVar)), or Call MySub(A$, (B#), (C#)) would mean that the values of MyVar, B#, and C# are passed to the called procedures.  Even without any ByVal keywords in the called procedures, changed values would not pass back to calling procedures.  Meanwhile, if MySub did not have a ByVal before its first parameter, it would pass any changes in A$ back to the calling procedure.

See also: Visual Basic conceptual topics.