VBA Passing Arguments ByRef or ByVal
Introduction
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.
Remarks
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
