Call Proc (By Value / By Reference)?

There are several different methods of calling subroutines from within VBA and it is sometimes hard to know which is the right syntax for the desired effect. Consider the following:
[tt]
Call MySubroutine(a, b)
Call MySubroutine ((a), (b))
Call MySubroutine ((a), b)
Call MySubroutine (a)
MySubroutine a, b
MySubroutine (a), (b)
MySubroutine a, (b)
MySubroutine (a)
[/tt]
Each of these procedure calls the subroutine MySubroutine, but they are not all doing the same thing.

First letÆs look at the use of the keyword Call which appears in four of the calls, but not in the other four. If you use the keyword Call, then the argument list must be enclosed in parenthesis, but if you do not use the keyword Call, then the argument list must not be enclosed in parenthesis. The net effect is that
Call MySubroutine(a, b) is identical to MySubroutine a, b
Call MySubroutine ((a), (b)) is identical to MySubroutine (a), (b)
Whereas
Call MySubroutine ((a), b) is NOT identical to MySubroutine a, (b)
Call MySubroutine (a) is NOT identical to MySubroutine (a)

To verify this, Remove the keyword Call and its associated parenthesis around the argument list from [color blue]Call[/color] MySubroutine [color blue]([/color]a, b[color blue])[/color], and youÆre left with MySubroutine a, b. Doing the same for the other three examples will show the equality and inequality between the statements.

Once the Call and associated parenthesis are removed, weÆre left with the following six calls.
[tt]
MySubroutine (a), b
MySubroutine a
MySubroutine a, b
MySubroutine (a), (b)
MySubroutine a, (b)
MySubroutine (a)
[/tt]
What is the difference between them? The difference is that some arguments are enclosed in parenthesis and some of the arguments are not. Whenever a scalar variable is enclosed in parenthesis in a subroutine call, it is evaluated as an expression and the value of the expression is passed By Value to the subroutine. When an argument is not enclosed in parenthesis, the variable will be passed the default variable passing mechanism, which in VBA, is By Reference. So the difference between these six call lies in which variables are passed by reference, and which are passed by value.

MySubroutine (a), b => a is passed By Value, b is passed By Reference
MySubroutine a, (b) => a is passed By Reference and b is passed By Value
MySubroutine (a) => a is passed By Value
MySubroutine a => is passed By Reference

This leads us to the question, what is the difference between passing by value and passing by reference. When a variable is passed by reference, then the subroutine actually received a pointer (a reference) to the variable so that if inside the subroutine you change that variable, the variable will also be changed in the calling procedure. Remember that the subroutine gets a pointer to the original, so when you make a change, you are actually changing the original. On the other hand, when you pass by value, the subroutine gets is own copy of that value, and any changes that are made, will not be affected in the calling routine. In other words, the subroutine is changing the copy, but not the original.

To see it in action consider the following code, triggered by a button from a form. After running the code, the immediate window will show the following:

In Ref Val: A = 1 B = 2
Call with Pass by Value
In MySubroutine: A = 3 B = 4
In Ref Val: A = 1 B = 2
Call with Pass by Reference
In MySubroutine: A = 3 B = 4
In Ref Val: A = 3 B = 4

ItÆs easy to see that when passing by value, the originals are not changed, but when passing by reference, the originals are changed.

To further confuse the issue, the use of parenthesis in the Call statement is not the only way to define the desired parameter passing mechanism. In the Sub definition, you can use either of the two keywords ByRef or ByVal to indicate how that parameter is to be received.
[tt]
Private Sub MySubroutine (ByVal a As Integer, ByRef b As Integer)
[/tt]
As you might expect, the parameter a will accepted as a By Value, and b will be accepted as By Reference. Before you stop reading, think this through. What happens if the Call statement indicates that a is to be passed By Reference and b is to be passed By Value, when the Sub is expecting a to be By Value and b to be By Reference?
[tt]
Call MySubroutine (a, (b))
[/tt]
In all cases, a specific declaration in the Sub will dictate the behavior. ThatÆs easy to accept when both the Call and Sub agree, but maybe not as easy to accept when the two are at odds. When the call says pass By Reference, and the Sub expects By Value, then the sub will resolve the reference and grab the value because it wants a value and not a reference, and proceed as if passed By Value. The hard case is when the Call says pass By Value, and the Sub expects By Reference. The behavior will still appear as if passed by Value! Why? Because when the Call statement is executed, the expression is evaluated and a result is returned and stored in temporary memory, and what actually gets passed is a reference (pointer) to the expression result in temporary memory, not a reference to the original variable. The Sub is happy because it gets a valid reference, and the original variable is safe because it is not what is being referenced. Thus it appears to behave as if passed By Value.

Special thanks to RoyVidar and Ed2020 for their suggestions.

Good luck in your subroutine and function calling and parameter passing.