Passing Parameters In Excel VBA

In VBA, parameters are passed by reference by default. This means that if a parameter is changed inside a procedure or function, the original variable used as an argument is also affected.

If you want to prevent such side effects, you must use the keyword ByVal before the parameter declaration. This passes the variable by value — that is, a copy of the variable is passed, and changes inside the procedure do not affect the original variable.

Example Comparing ByVal and ByRef

Sub PassingExample()
    Dim x As Integer, y As Integer
    ThisWorkbook.Worksheets("Sheet2").Activat
    x = 5
    y = 12
    Cells(1, 1).Value = x
    Cells(1, 2).Value = y
    SwapCopy x, y
    Cells(2, 1).Value = x
    Cells(2, 2).Value = y
    SwapReference x, y
    Cells(3, 1).Value = x
    Cells(3, 2).Value = y
End Sub
Sub SwapCopy(ByVal a As Integer, ByVal b As Integer)
    Dim c As Integer
    c = a
    a = b
    b = c
End Sub
Sub SwapReference(ByRef a As Integer, ByRef b As Integer)
    Dim c As Integer
    c = a
    a = b
    b = c
End Sub

Explanation:

• The procedure PassingExample() assigns initial values 5 and 12 to variables x and y.
• It then calls two swap procedures: SwapCopy() and SwapReference().
• The values of x and y are output after each swap to observe the effect.
• In SwapCopy(), parameters are declared with ByVal. The swap happens only within the procedure’s local copies, so the original variables remain unchanged.
• In SwapReference(), parameters are declared with ByRef (the default). The swap affects the original variables permanently, so the values of x and y are exchanged.