Passing Arguments by Value or by Reference

When you define a procedure, you have two choices regarding how arguments are passed to it: by reference or by value. When a variable is passed to a procedure by reference, Microsoft® Visual Basic® for Applications (VBA) actually passes the variable's address in memory to the procedure, which can modify it directly. When execution returns to the calling procedure, the variable contains the modified value.

When an argument is passed by value, VBA passes a copy of the variable to the procedure. Then, the procedure modifies the copy, and the original value of the variable remains intact; when execution returns to the calling procedure, the variable contains the same value that it had before being passed.

By default, VBA passes arguments by reference. To pass an argument by value, precede the argument with the ByVal keyword in the procedure definition, as shown here:

Function SomeProc(strText As String, _
                  ByVal lngX As Long) As Boolean

If you want to denote explicitly that an argument is passed by reference, you can preface the argument with the ByRef keyword in the argument list.

Passing by reference can be useful as long as you understand how it works. For example, you must pass arrays by reference; you will get a syntax error if you try to pass an array by value. Because arrays are passed by reference, you can pass an array to another procedure to be modified, and then you can continue working with the modified array in the calling procedure.

See Also

Tips for Defining Procedures in VBA | Using Optional Arguments | Using Parameter Arrays