VBA Passing Arguments ByRef or ByVal


The ByRef and ByVal modifiers are part of a procedure's signature and indicate how an argument is passed to a procedure. In VBA a parameter is passed ByRef unless specified otherwise (i.e. ByRef is implicit if absent).

Note In many other programming languages (including VB.NET), parameters are implicitly passed by value if no modifier is specified: consider specifying ByRef modifiers explicitly to avoid possible confusion.


Passing arrays

Arrays must be passed by reference. This code compiles, but raises run-time error 424 "Object Required":

Public Sub Test()
    DoSomething Array(1, 2, 3)
End Sub

Private Sub DoSomething(ByVal foo As Variant)
    foo.Add 42
End Sub

This code does not compile:

Private Sub DoSomething(ByVal foo() As Variant) 'ByVal is illegal for arrays
    foo.Add 42
End Sub