Skip to main content

Passing arguments efficiently

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–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 value copies the original variable. Changes to the argument within the procedure aren't reflected back to the original variable. For example:

Function Factorial(ByVal MyVar As Integer)  ' Function declaration.
    MyVar = MyVar - 1
    If MyVar = 0 Then
        Factorial = 1
        Exit Function
    End If
    Factorial = Factorial(MyVar) * (MyVar + 1)
End Function
' Call Factorial with a variable S.
S = 5
Print Factorial(S) ' Displays 120 (the factorial of 5)
Print S ' Displays 5.

Without including ByVal in the function declaration, the preceding Print statements would display 1 and 0. This is because MyVar would then refer to variable S, which is reduced by 1 until it equals 0.

Because ByVal makes a copy of the argument, it allows you to pass a variant to the Factorial function. You can't pass a variant by reference if the procedure that declares the argument is another data type.

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>