What is ByRef in VBA?

Byref in VBA stands for “By Reference”. With the help of VBA Byref, we can target the original value without changing the value stored in variables. In other words, we will directly be passing the value to Sub procedures instead of going through the regular methods of defining and assigning the values to variables.

In VBA ByRef, we define the sub-procedure after we set the rule for ByRef. This could be done below the sub-procedure where we want to write the code. In ByRef, we redefine the variable which is used in Sub procedure. And it only works properly when we call the ByRef condition in our subprocedure.

How to Use the ByRef Function in Excel VBA?

Below are the different examples to use ByRef Function in Excel using VBA Code.

You can download this VBA ByRef Excel Template here – VBA ByRef Excel Template

Excel VBA ByRef – Example #1

First, let us learn how to insert a ByRef in VBA, for this follow the below steps. In this example, we will see how to use VBA ByRef for a simple mathematical subtraction work. For this, we would need a module.

Step 1: So, go to VBA and open a module from the Insert menu option as shown below.

Step 2: In the newly opened module, write the subcategory of VBA ByRef as shown below.

Popular Course in this category

VBA Training (3 Courses, 12+ Projects)3 Online Courses | 13 Hands-on Projects | 45+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.7 (8,793 ratings)

Course Price

View Course

Related Courses

Code:

Sub VBA_ByRef1()

End Sub

Step 3: Now define a variable let’s say it is an A as Integer.

Code:

Sub VBA_ByRef1()

Dim A As Integer

End Sub

Step 4: Give any number to variable A. Let that number be 1000.

Code:

Sub VBA_ByRef1()

Dim A As Integer
A = 1000

End Sub

Step 5: To print the value stored in variable A, we would use Msgbox.

Code:

Sub VBA_ByRef1()

Dim A As Integer
A = 1000
MsgBox A

End Sub

Step 6: Now we compile and run this code by clicking on the Play button as shown below. We will get a message box with the value stored in variable A as 1000.

Now apply VBA ByRef, create another sub-category below the first one and assign the defined variable from the first subcategory with ByRef.

Step 7: By this, we will allow the second subcategory to use the values stored in variable A.

Code:

Sub VBA_ByRef1()

Dim A As Integer
A = 1000
MsgBox A

End Sub
Sub VBA_ByRef2(ByRef A As Integer)

End Sub

Step 8: Now call the variable A here again and subtract any value from variable A, to get the output value in the same variable. Let’s subtract 100 from the value of variable A so that we would get a measurable number.

Code:

Sub VBA_ByRef1()

Dim A As Integer
A = 1000
MsgBox A

End Sub
Sub VBA_ByRef2(ByRef A As Integer)

A = A - 100

End Sub

Step 9: Now if we compile each step of the code, we will notice that when the cursor reached variable A, we will see it has only 0 stored in it.

Step 10: When the cursor reached End Sub, the output we will get as 1000 in the message box.

Step 11: It is because we haven’t assigned the ByRef to the first subcategory. Now we will assign subcategory name before the message box function of the first subcategory and see what will happen.

Code:

Sub VBA_ByRef1()

Dim A As Integer
A = 1000

VBA_ByRef2 A

MsgBox A

End Sub
Sub VBA_ByRef2(ByRef A As Integer)

A = A - 100

End Sub

Step 12: And now, run the complete code again. We will see, the second value which is stored in variable A as 100 got subtracted from the first value 1000. As a result, we got the output message as 900.

Step 13: This is the main advantage of using ByRef. We don’t need to define multiple variables for a single job. Just one variable is enough to perform the whole task in different ways. We can use more than one ByRef in a single module.

To justify, what we have understood, let’s add another ByRef in the same Module.

Code:

Sub VBA_ByRef1()

Dim A As Integer
A = 1000

VBA_ByRef2 A

MsgBox A

End Sub
Sub VBA_ByRef2(ByRef A As Integer)

A = A - 100

End Sub
Sub VBA_ByRef3(ByRef A As Integer)

End Sub

Step 14: In this subcategory, let’s use multiplication.

Code:

Sub VBA_ByRef1()

Dim A As Integer
A = 1000

VBA_ByRef2 A

MsgBox A

End Sub
Sub VBA_ByRef2(ByRef A As Integer)

A = A - 100

End Sub
Sub VBA_ByRef3(ByRef A As Integer)

A = A * 2

End Sub

Step 15: Again compile and run the code again. We will see that value obtained from the above steps as 900 is now multiplied by 2 to get 1800 as output.

Excel VBA ByRef – Example #2

In this example, we will see, how ByRef works with other kind of integers.

Step 1: Open a module and write the subcategory as shown below.

Code:

Sub VBA_ByRef4()

End Sub

Step 2: Now define a variable A as Double. This will allow us to use decimal values.

Code:

Sub VBA_ByRef4()

Dim A As Double

End Sub

Step 3: Assign any decimal value to variable A.

Code:

Sub VBA_ByRef4()

Dim A As Double
A = 1.23

End Sub

Step 4: Now again use the message box to see the value stored in variable A.

Code:

Sub VBA_ByRef4()

Dim A As Double
A = 1.23

MsgBox A

End Sub

Now if we run the code, we would get 1.23 as output.

Step 5: In a different manner, we will use Function to define ByRef as Double with variable A.

Code:

Sub VBA_ByRef4()

Dim A As Double
A = 1.23

MsgBox A

End Sub
Function AddTwo(ByRef A As Double) As Double

End Function

Step 6: Now add any number to variable A. Let’s say it is 10.

Code:

Sub VBA_ByRef4()

Dim A As Double
A = 1.23

MsgBox A

End Sub
Function AddTwo(ByRef A As Double) As Double

A = A + 10

End Function

Step 7: And again use this defined ByRef function in the first subcategory. Here we will be seeing two message box, one for variable A and other for ByRef.

Code:

Sub VBA_ByRef4()

Dim A As Double
A = 1.23

MsgBox AddTwo(A)
MsgBox A

End Sub
Function AddTwo(ByRef A As Double) As Double

A = A + 10

End Function

Step 8: Same would be reflected in the message box as well.

Step 9: And in the next run it will give the added value of 10 into the original variable value of 1.23 as shown below.

This is how VBA Byref takes the reference of the value defined once and then populate the output as per the new condition.

Pros and Cons of VBA ByRef

When writing big codes, it saves a lot of time by considering the already defined variable, so that its value can be used again and again.
We don’t have to define many variables as per formula we want to apply.
We can apply many ByRef conditions in a single module without even disturbing the process.
We cannot use VBA Byref in complex code structure.

Things to Remember

When considering more than one ByRef conditions, the output will be based on the last sub procedure ByRef we defined, but it also considers all the ByRef conditions used previously.
Final output will have sequential processed output. Not only the latest one.
This process cannot be done by recording the macro.
We can see the value stored in each stage of the variable by compiling the code.
Once done, save the excel file as Macro Enabled excel format, so that we will not lose code in future.

Quiz Result
Total Questions	Correct Answers	Wrong Answers	Percentage

What is ByRef in VBA?

How to Use the ByRef Function in Excel VBA?

Excel VBA ByRef – Example #1

Excel VBA ByRef – Example #2

Pros and Cons of VBA ByRef

Things to Remember

Recommended Articles

About Us

Apps

Resources

Certification Courses