ByVal and ByRef

A few people have asked me what the difference between ByVal and ByRef actually is. In a nutshell it's the difference between creating a new variable (ByVal) and rebadging an existing one (ByRef).  Here's an example of some code you can run:

Option Explicit

Public Sub Test()
    Dim x As Double
   
    x = 1
   
    AddTwoByVal x
    Debug.Print x
   
    AddTwoByRef x
    Debug.Print x
End Sub

Private Sub AddTwoByVal(ByVal Value As Double)
    Value = Value + 2
End Sub

Private Sub AddTwoByRef(ByRef Value As Double)
    Value = Value + 2
End Sub

Paste this into a blank module and run the "Test" macro.  You should see the following output in the Immediate Window (press CTRL+G to bring it up if it's hidden):

1
3

But what's going on?

Using ByVal creates a whole new variable.  VBA takes the value of x and stores it in a new variable called "Value".  When AddTwoByVal finishes the variable Value is thrown away along with everything you've done to it, so there's no effect on "x" in Test.

Using ByRef effectively relabels an existing variable.  So in AddTwoByRef you are actually using the same variable as in Test but, figuratively speaking, you've stuck it in a box labelled "Value".  Therefore when AddTwoByRef finishes the changes to "Value" will also be reflected in "x".

If you don't specify ByVal or ByRef then VBA assumes ByRef.

You can use this in all sorts of ways. One common application is to address some of the weaknesses of Functions. Functions return a single value (which could be an object or  user-defined type) but what if you want to amend 2 or more values?  Simply create a Sub with the "answers" as arguments passed ByRef - neat trick!