Passing Arguments Efficiently

This page is specific to the Visual Basic for Applications (VBA) Language Reference for Office 2010.

All arguments are passed to proceduresby 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 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 above. You can't pass a variant by reference if the procedure that declares the argument is another data type.

Show:
© 2014 Microsoft