VBA ByRef

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.

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.

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.

Recommended Articles

This is a guide to VBA ByRef. Here we discuss how to use ByRef function in Excel using VBA code along with practical examples and downloadable excel template. You may also look at the following articles to learn more –

1. Guide to VBA UBound Function
2. OFFSET Excel Function (Example, Uses)
3. Create Hyperlink in Excel VBA
4. How to Use Excel Fill Handle?