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
foo parameter is passed
ByRef in both
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.
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 Sub
Calling the above
Test procedure outputs 84.
DoSomething is given
foo and 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 Sub
The above code raises run-time error 91, because the caller is calling the
Count member of an object that no longer exists, because
DoSomething was given a reference to the object pointer and assigned it to
Nothing before returning.
Using parentheses at the call site, you can override
ByRef and force an argument to be passed
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