Passing Parameters by Reference and by Value with Excel VBA

When calling a procedure, you pass certain parameters to it. Inside the procedure, these parameters can be assigned new values, which will persist after the procedure finishes.

Thus, by default, when variables are passed as parameters, their physical addresses are passed into the procedure. Therefore, the contents of these variables can be modified inside the procedure. To explicitly specify passing parameters by reference, the keyword ByRef is used.

Another way of passing parameters to a procedure is by value. In this case, not the variable itself, but only its value is passed into the procedure. Passing a parameter by value is specified with the keyword ByVal.

The following example demonstrates the difference between passing a parameter by reference and by value.

Passing parameters by reference and by value

Sub DemoByValByRef(ByVal a, b, ByRef c)
    ' a is passed by value
    ' b is passed by reference by default
    ' c is explicitly passed by reference
    a = a + 1
    b = b + a
    c = c + a
End Sub

Sub Test()
    a = 1 : b = 10 : c = 100
    DemoByValByRef a, b, c
    MsgBox a
    ' Displays 1
    MsgBox b
    ' Displays 12
    MsgBox c
    ' Displays 102
End Sub