Procedure arguments, either for a subroutine or a function, are declared either as ByVal or ByRef. The use of ByVal means that the called procedure does not want to change the value of the argument passed to it whereas the use of ByRef means that the called procedure may change the value.
This note looks at how the called procedure and the calling routine are affected by three different kinds of arguments passed ByVal and passed ByRef.
Scalar (simple) variables passed ByVal and ByRef
Object variables passed ByVal and ByRef
How to pass an array variable ByVal
Return a value in a ByVal parameter
From Bryan on December 29, 2012:
AWESOME! You have answered many questions that have been bothering me. I must have opened 20 pages in Google before I found yours -which was exactly what I was looking for and in very plain English. Great Job! Thank you!!
A scalar variable, i.e., a simple variable, is one that represents a single value such as a number or a string. In VBA a number is represented by a variable of a type such as Integer, Long, Single, or Double. The subroutine signature may look like
Option Explicit
Sub Callee(ByRef I As Integer, ByVal J As Integer)
I = I + 1
J = J + 1
End Sub
Sub Caller()
Dim K As Integer, L As Integer
K = 1
L = 2
Callee K, L
Debug.Print K, L
End Sub
The result of the Debug.Print statement will be
2 2
Figure 1
When a parameter is declared as ByRef, the subroutine may change the value of the argument passed to it at runtime. On the other hand, when the subroutine has a ByVal parameter, it means it does not want to change the value of the argument passed to it. To ensure that no change actually happens, the system makes a copy of the current value of the variable and passes this temporary variable to the called routine. Then, when the called routine returns control to the caller, the temporary variable is discarded.
That is why the Debug.Print statement prints 2 and 2. Even though Callee adds 1 to the ByVal parameter J, the system discards the change when control returns to the Caller. Consequently, the value of L (the actual argument passed as the ByVal parameter J) is unchanged when control returns to Caller.
A similar concept applies to object variables except that now the by value or by reference applies not to the object itself but to the pointer to the object. Since an object can be fairly complex structure, the system implements an object somewhere in memory and provides the program with a pointer to that memory location. So, it is this pointer that is the subject of the by value or by reference decision.
Obviously, if an object variable is declared as ByRef any change made in the called subroutine will affect the pointer and the object itself.
However, since a ByVal object means that the system only duplicates the pointer, this temporary copy of the pointer still points to the actual object. So, if the called subroutine makes a change to the object, the change will affect the actual object. However, if the called subroutine changes only the pointer, the change will be lost when control returns to the caller.
In the code below, the first call is to a subroutine that declares its object parameter as ByVal. The second call is to a subroutine that declares its parameter as ByRef. In each case the only thing that the subroutine does is to change the pointer – there is no attempt to affect the actual object.
Option Explicit
Sub CalleeVal(ByVal LocalColl As Collection)
Set LocalColl = Nothing
End Sub
Sub calleeRef(ByRef LocalColl As Collection)
Set LocalColl = Nothing
End Sub
Sub Caller()
Dim aColl As Collection: Set aColl = New Collection
aColl.Add ActiveSheet, ActiveSheet.Name
CalleeVal aColl
Debug.Print aColl Is Nothing
calleeRef aColl
Debug.Print aColl Is Nothing
End Sub
Code Sample 2
The result of the two calls is shown below. Since the system makes a copy of the pointer for the ByVal parameter, the Set LocalColl = Nothing affects only the copy. Consequently, when control returns to the Caller subroutine after the CalleeVal aColl statement the aColl variable remains unaffected. By contrast, after the call on CalleeRef the change affects the actual argument and consequently, aColl itself becomes Nothing.
False
True
Figure 2
So, why doesn’t the system simply duplicate the object and thereby have a consistent behavior across simple variables and object variables? The answer is that an object is a complex data structure and the system does not know how to duplicate an object. For instance, there might be a recursive reference – an object may refer to another object, which, in turn, points back to the first object (an employee object points to the immediate manager object, which in turn, points to all the direct reports, which would include the employee). Or, an object might exist in a particular context and duplicating the object may violate the rules for the object’s existence. For example, a node in a binary tree may have at most 2 child nodes. So, if one were to duplicate a node in a binary tree, would it exist in a vacuum? Because if it were made part of the tree, the rules for the binary tree may be violated!
Suffice it to say, it is practically impossible for the system to simply make a copy of an object.
So far we have looked at a simple variable and an object variable. In the case of an array variable, the language disallows the use of a ByVal parameter. As shown below, declaring an array parameter as ByVal results in a syntax error.
Figure 3
Does that mean it is impossible to pass an array by value? It turns out we can circumvent the restriction by making the parameter a variant.
Option Explicit
Sub CalleeArray(ByVal Arr)
Debug.Print Arr(LBound(Arr))
Arr(LBound(Arr)) = 0
Debug.Print Arr(LBound(Arr))
End Sub
Sub CallerArray()
Dim MyArr(2): MyArr(0) = 10: MyArr(1) = 20: MyArr(2) = 30
CalleeArray MyArr
Debug.Print MyArr(0)
End Sub
Code Sample 3
The result of the 3 Debug.Print statements:
10
0
10
Figure 4
Declaring the parameter as a ByVal Variant meant that the system made a copy of the array argument being passed to it!
This workaround applies only to simple variables. Some languages insist that when a parameter is declared as ByRef, the actual argument must be a variable. However, VBA allows one to pass the result of a formula. The way it does this is by creating a temporary variable to hold the result of the formula and passing this variable to the called subroutine – thereby mimicking the behavior for a ByVal parameter!
In the example below, identical to Code Sample 1 except the call to Callee uses (K) instead of just K. Enclosing K within parenthesis forces the system to treat the result as an arithmetic formula, thereby triggering the need for a temporary variable.
Option Explicit
Sub Callee(ByRef I As Integer, ByVal J As Integer)
I = I + 1
J = J + 1
End Sub
Sub Caller()
Dim K As Integer, L As Integer
K = 1
L = 2
Callee (K), L
Debug.Print K, L
End Sub
Code Sample 4
As the result of the Debug.Print statements show, neither K nor L is changed after control returns to the caller.
1 2
Figure 5
In contrast to the previous workaround, which applied only to a simple variable, this workaround applies only to an object variable. Recall that a ByVal object makes a copy of only the pointer to the object – leaving the object itself untouched. So, if one passes an object to a ByVal variant variable, the called subroutine can actually modify the object and the result would propagate back to the caller. This is the technique used in A ByRef argument with the Application.Run method (http://www.tushar-mehta.com/publish_train/xl_vba_cases/1022_ByRef_Argument_with_the_Application_Run_method.shtml)
