VBA ByRef


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 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.


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