VBA Passing Arguments ByRef or ByVal

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

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


Got any VBA Question?