A value that is passed to a sub or function with a parameter is passed either by reference or by value.
If it is passed by value, the sub/function gets a copy of the value.
Because it is a copy, the function can change the value of the parameter without it having an effect to the value of the caller.
However, if a parameter is passed by reference, the modifications to the value in the sub/function are reflected in the calling context.
In order to force a parameter to be by reference, the keyword byRef, in order to force it to be by value, the keyword byVal must be used,
In absence of these keywords, the way the function is invoked detemines if a parameter is passed by reference or by value.
Demonstration
The following simple VBA code snippet tries to demonstrate the difference between byVal and byRef parameters:
option explicit
sub s(byVal pVal as string, byRef pRef as string) ' {
pVal = "changed"
pRef = "changed"
end sub ' }
sub main() ' {
dim var1 as string
dim var2 as string
var1 = "some value"
var2 = "some value"
s var1, var2
debug.print "var1 = " & var1 & ", var2 = " & var2
end sub ' }
'
' Executing main prints
'
' var1 = some value, var2 = changed
If a parameter is neither defined as byRef or byVal, it depends on the way how the function or sub is called if the parameter treated as byRef or byVal.
How the sub/function is called
Parameters are
Comment
subName(param1, …)
byVal
With parentheses, the parameters are passed byVal
subName param1, …
byRef
Without parentheses, the parameters are passed byRef
call subName(param1, …)
byRef
With a call statement, the parameters are passed byRef (even though parentheses are used)
option explicit
sub s(p as string) ' {
debug.print "p = " & p
p = "changed"
end sub ' }
sub main() ' {
dim n as string
n = "with" ' With parantheses, the value is passed «byVal»
s(n) ' The assignment of a value to the parameter within the sub
s(n) ' does not affect the value of n
n = "without" ' But… without the parantheses, the value of
s n ' n is passed «byRef», that is, it can be
s n ' changed within the sub
n = "call" ' If the call statement is used, the value of
call s(n) ' the parameter is also passed «byRef» (although
call s(n) ' parantheses are required).
end sub ' }
'
' Executing main prints
'
' p = with
' p = with
' p = without
' p = changed
' p = call
' p = changed
If parameters that are defined byRef get their value from a variable when the sub/function is called, the data type of the parameter and the variable must be equal, otherwise a ByRef argument type mismatch error is thrown:
Such an error can be forced with the following VBA snippet:
option explicit
sub s(byRef p as string) ' {
'
' Note: the sub receives the paramter p «byRef» and
' does not change it.
'
debug.print "s was called with p = " & p
end sub ' }
sub main() ' {
call s(42)
dim var as integer
var = 42
call s(var)
end sub ' }
Note, the line that reads call s(42) does not cause this error, although 42 has a different data type as the parameter. This is because the value for the parameter is now provided from a constant rather than a variable.
