VBA ByRef

ByRef in VBA is a function called as by reference where we provide a reference to any arguments in our code. When we make custom functions and want to use the value of any variable defined earlier before the function, we use the ByRef function. The syntax is simple as Function-Name(ByRef Variable as Data Type).

Using ByRef, we can point to the original variable value without altering the variable value. It is like passing the variable value directly to the VBA subprocedure or VBA function.

Example #1

Look at the VBA code below.

Code1:

Sub Procedure1()

Dim k As Integer

k = 50

Procedure2 k

MsgBox k

End Sub

Code2:

Sub Procedure2(ByRef k As Integer)

k = k + 10

End Sub

In the first procedure, we have declared the variable "k" as "Integer."

Then we have assigned the value to this variable as 50.

After that, we added a new line i.e.

Procedure2 k

It is the second procedure name. In this procedure, we have declared the variable within the parenthesis as String in VBA, but we have used the word "ByRef."

ByRef k As Integer

Here I have assigned the value of the variable “k” as

k = k + 10

We will run the code step by step by pressing the F8 key.

Press the F8 key two more times and place a cursor on variable "k" to see the value of the variable "k."

Since we have assigned the value as 50, it shows the value as 50. Now, it has highlighted the line Procedure2 k, which is the second procedure name.

If we press the F8 key now, it will jump out of the current procedure and go to the second procedure.

Since we used the ByRef word, it carried the variable "k" value from the above procedure.

Press the F8 key twice. It will go back to the previous subprocedure. If you notice, in the second procedure, we have applied the formula as k = k + 10. i.e., the "k" value is 50, then adds 10 more to that, i.e., 60 in total.

Now, the code is running in the first procedure, and in this procedure, the variable "k" value is 50. But press the F8 key and see the result in a message box.

We got the result of 60 instead of the default value of 50 in this procedure.

We got 60 because, in the second procedure, we applied "ByRef, "so it carried the equation result (k = k + 10) to the current procedure.

Here, the first variable "k" value is 50, and in the second procedure, the variable "k" value is k + 10, i.e., 60, carried to the first procedure.

In the first procedure the original value of the variable "k" was 50, so By Ref has changed the original value from 50 to 60 by executing the equation k = k + 10 i.e., k = 50 +10 = 60.

Example #2

Take a look at one more example.

Code 1:

Sub P1()

Dim MyNumber As Long

MyNumber = 1

Call Change_ByRef(MyNumber)
' MyNumber is changed by the Change_ByRef procedure
MsgBox "My Number is now: " & MyNumber

End Sub

Code 2:

Sub Change_ByRef(ByRef NewNumber As Long)

NewNumber = 14

End Sub

It works the same as the previous code.

Initially, the value of the variable "MyNumber" is 1. Then we call the procedure below by its name.

Call Change_ByRef(MyNumber)

In that procedure, the value of the variable is 14.

So, when it returns to the previous procedure, it will assign the new value to the variable as 14.

This article has been a guide to VBA ByRef. Here, we discuss passing an argument using the VBA ByRef function, examples, and a downloadable Excel template. You can learn more about VBA from the following articles: -

• VBA Exit Sub
• Excel VBA Operators
• Integer Data Type in VBA
• VBA Loop