The ByRef and ByVal keywords are used when passing variables to subroutines and functions. Passing arguments by reference (ByRef) passes the memory location of the variable which means that any changes made to the variable during the procedure are permanent. Passing arguments by value (ByVal) only passes a copy of the variable so that any changes to the variable during the procedure are only made on the copy. If neither qualifier is specified the variable is passed ByRef.
In the example below, three variables (i,j,k) are passed to the sub AddOne. The variable i is passed with no qualifier, j is passed ByRef, and k is passed ByVal. The values for all three variables are printed out after returning from the sub. Note that the value for i is updated just as the value for j while the value for k is unchanged.
Public Sub MyMacro()
Dim i As Long
Dim j As Long
Dim k As Long
i = 10
j = 10
k = 10
AddOne i, j, k
Debug.Print "i=" & CStr(i) & ", j=" & CStr(j) & ", k=" & CStr(k)
Private Sub AddOne(i As Long, ByRef j As Long, ByVal k As Long)
i = i + 1
j = j + 1
k = k + 1
Previous VBA Tip: Putting Your Macro to Sleep
Don’t want to miss out on other great information? Subscribe to this blog or our monthly eNewsletter now!
Learn More ◊ Contact us today ◊ Newsletter ◊