| Microsoft Office 2000/Visual Basic Programmer's Guide | |
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, VBA actually passes the variable's address in memory to the procedure, which can then 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. The procedure then 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 explicitly denote that an argument is passed by reference, you can preface the argument with the ByRef keyword in the argument list. It's not necessary to use the ByRef keyword since passing by reference is VBA's default behavior.
Passing by reference can be useful as long as you understand how it works. For example, you must pass arrays by reference; you'll 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 continue working with the modified array in the calling procedure.
You can see how this works by running the TestQuickSort procedure, available in the modArrays module in VBA.mdb in the ODETools\V9\Samples\OPG\Samples\CH07 subfolder on the Office 2000 Developer CD-ROM. TestQuickSort calls the QuickSortArray procedure in the same module. Even if you don't completely understand how the QuickSortArray procedure works, you can see that it takes an array (or a Variant variable containing an array) as an argument, and that it's a Sub procedure, so it doesn't return a value. When you pass an array to the QuickSortArray procedure, it receives the memory location for the array and sorts the array. Once it's finished, TestQuickSort can continue working with the sorted array.
The danger in passing by reference lies in the fact that you may unwittingly allow another procedure to modify a value that you have passed it. To prevent this from happening, either pass the argument by value or use a temporary variable to store an argument, and then modify the temporary variable.
For the most part, VBA programmers pass arguments by value when calling functions in the Windows API; many API calls require that an argument be passed by value. For more information about calling the Windows API, see Chapter 10, "The Windows API and Other Dynamic-Link Libraries." For more information about passing by value and by reference, see the Visual Basic Language Developer's Handbook by Ken Getz and Mike Gilbert (Sybex, 1999).