VBA ByRef

Publication Date :

Blog Author :

Download FREE VBA ByRef In Excel Template and Follow Along!
VBA ByRef Excel Template.xlsm

Table Of Contents

arrow

Excel VBA ByRef Function Argument

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.

VBA ByRef
You are free to use this image on your website, templates, etc.. Please provide us with an attribution link

How to Pass Argument using VBA ByRef Argument?

Example #1

Look at the VBA code below.

Code1:

Sub Procedure1()

Dim k As Integer

k = 50

Procedure2 k

MsgBox k

End Sub
vba byref example1.1

Code2:

Sub Procedure2(ByRef k As Integer)

k = k + 10

End Sub
vba byref example1.2

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.

vba byref example1.3
vba byref example1.4

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

vba byref example1.5

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.

vba byref example1.6

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

vba byref example1.7

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.

example1.8

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.

example1.9

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

vba byref example 2.1
Code 2:

Sub Change_ByRef(ByRef NewNumber As Long)

NewNumber = 14

End Sub
example2.2

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.

example2.3