Example
Default modifier
If no modifier is specified for a parameter, that parameter is implicitly passed by reference.
Public Sub DoSomething1(foo As Long)
End Sub
Public Sub DoSomething2(ByRef foo As Long)
End Sub
The foo parameter is passed ByRef in both DoSomething1 and DoSomething2.
Watch out! If you're coming to VBA with experience from other languages, this is very likely the exact opposite behavior to the one you're used to. In many other programming languages (including VB.NET), the implicit/default modifier passes parameters by value.
Passing by reference
-
When a value is passed
ByRef, the procedure receives a reference to the value.Public Sub Test() Dim foo As Long foo = 42 DoSomething foo Debug.Print foo End Sub Private Sub DoSomething(ByRef foo As Long) foo = foo * 2 End SubCalling the above
Testprocedure outputs 84.DoSomethingis givenfooand receives a reference to the value, and therefore works with the same memory address as the caller. -
When a reference is passed
ByRef, the procedure receives a reference to the pointer.Public Sub Test() Dim foo As Collection Set foo = New Collection DoSomething foo Debug.Print foo.Count End Sub Private Sub DoSomething(ByRef foo As Collection) foo.Add 42 Set foo = Nothing End SubThe above code raises run-time error 91, because the caller is calling the
Countmember of an object that no longer exists, becauseDoSomethingwas given a reference to the object pointer and assigned it toNothingbefore returning.
Forcing ByVal at call site
Using parentheses at the call site, you can override ByRef and force an argument to be passed ByVal:
Public Sub Test()
Dim foo As Long
foo = 42
DoSomething (foo)
Debug.Print foo
End Sub
Private Sub DoSomething(ByRef foo As Long)
foo = foo * 2
End Sub
The above code outputs 42, regardless of whether ByRef is specified implicitly or explicitly.
Watch out! Because of this, using extraneous parentheses in procedure calls can easily introduce bugs. Pay attention to the whitespace between the procedure name and the argument list:
bar = DoSomething(foo) 'function call, no whitespace; parens are part of args list DoSomething (foo) 'procedure call, notice whitespace; parens are NOT part of args list DoSomething foo 'procedure call does not force the foo parameter to be ByVal
