VBA ByRef

What is ByRef in Excel VBA?

“ByRef” stands for “By Reference” in Excel VBA. By using this we can point to the original variable value without altering the variable value. This is like passing the variable value directly to the VBA subprocedure or VBA function.

How to Use the ByRef Function in Excel VBA?

Below are the examples to understand how to use ByRef VBA function argument in excel.

VBA ByRef – Example #1

For an example look at the below VBA code.

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, I have declared the variable “k” as Integer.

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

After that I have added a new line i.e.

Procedure2 k

This is the second procedure name. In this procedure, I have declared the variable within the parenthesis as String in VBA but I have used the word “ByRef”.

ByRef k As Integer

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

k = k + 10

Ok, now I will run the code step by step by pressing F8 key.

Press 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 is showing the value as 50. Now it has highlighted the line Procedure2 k which is the second procedure name.

If I press F8 key now it will jump out of the current procedure and goes to the second procedure.

Now since we have used ByRef word in VBA it carried the variable “k” value from the above procedure.

Press F8 key twice it will go back to the previous sub procedure. If you notice in the second procedure I have applied the formula as k = k + 10. i.e. “k” value is 50 then adds 10 more to that i.e. 60 in total.

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

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

The reason why we got 60 because in the second procedure we have applied “ByRef “so, it carried the equation result (k = k + 10) to the current procedure.

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

In the first procedure 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.

VBA ByRef – Example #2

Now 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

This works exactly the same as the previous code.

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

Call Change_ByRef(MyNumber)

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

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

Recommended Articles

This has been a guide to VBA ByRef. Here we discuss how to use ByRef function argument in Excel VBA along with the examples and downloadable excel sheets. You can learn more about VAB from the following articles –

• How to Copy Files using VBA Code?
• VBA FileDialog as Object
• VBA Application.GetOpenFilename
• Operator Sign in VBA
• Join in VBA
• Now in VBA
• VBA Beginners Tutorial
• Integer Data Type in VBA
• Types of Loop in VBA