VBA ByRef Argument

What is Excel VBA ByRef Argument?

In Excel VBA, you can pass arguments to functions by reference using the ByRef keyword. When you pass an argument by reference, any changes made to the argument inside the function will affect the original variable in the calling code.

Consider the following example. It creates a sub-routine where you call a function that references the variable using ByRef. Using the VBA ByRef argument, we can call the value an integer and increment it by 2, resulting in the output being printed in the Immediate tab, as seen below the code.

VBA ByRef Intro
VBA ByRef Intro - Output

ByRef is commonly used to pass arguments to functions or subroutines. When the VBA ByRef argument is passed, changes made to the parameter inside the function or subroutine persist outside the scope of that routine. This allows for more flexible and dynamic data manipulation, providing enhanced control over variables.

Key Takeaways
  • ByRef is a parameter-passing mechanism in VBA. When an argument is passed ByRef, changes made to the parameter inside a function or subroutine affect the original variable in the calling code.
  • ByRef allows for direct modification of variables or data structures in memory.
  • It provides flexibility and control over variables, enabling dynamic data manipulation.
  • ByRef is commonly used with arrays to efficiently update and manipulate data.
  • It can improve code efficiency by avoiding unnecessary data copying, especially for large data structures.
  • ByRef is essential for implementing certain advanced techniques like recursive functions or custom error-handling routines.

How to pass using VBA ByRef Argument?

To pass VBA ByRef Argument, we perform the following steps:

Step 1: Go to the “Developer tab” in the toolbar and click the “Visual Basic” option. Now, the VBA Editor opens to add functions and sub-procedures. Then, click the “Insert” and “Module” buttons to create a new module or blank page.

VBA Byref - pass - Step 1 - Visual Basic
VBA Byref - pass - Step 1 - Module

Step 2: Define a sub-routine to change string values.

VBA Byref - pass - Step 2.jpg

Step 3: Define a variable ‘name’ as the String data type.

VBA Byref - pass - Step 3

Step 4: Initialize the value for the string variable ‘name.’

VBA Byref - pass - Step 4

Step 5: Print the name before calling the ByRef sub-routine to display the changes in the Immediate tab in Excel VBA.

VBA Byref - pass - Step 5

Step 6: Call the sub-routine using VBA ByRef Argument as a String variable.

VBA Byref - pass - Step 6.jpg

Step 7: Define the sub-routine called in Step 5.

VBA Byref - pass - Step 7

Step 8: Define the value of ‘str’.

VBA Byref - pass - Step 8

Code:

Sub StringByRef()
Dim name As String
name = “John”
Debug.Print “Before calling the subroutine: ” & name
ModifyNameByRef name
Debug.Print “After calling the subroutine: ” & name
End Sub

Sub ModifyNameByRef(ByRef str As String)
str = “Jane”
End Sub

Step 9: Run the code by pressing F5 or the ‘run’ button in Excel VBA. The output is shown below:

VBA Byref - pass - Step 9

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

Let us look at some examples depicting how to use ByRef in functions.

Example #1

Consider an example where we need to implement a VBA ByRef argument in a function to return a boolean value.

Step 1: Initialize a sub-procedure to pass VBA ByRef Boolean.

VBA Byref - Example 1 - Step 1

Step 2: Declare ‘n’ as an Integer datatype and ‘res’ as a Boolean datatype in VBA.

VBA Byref - Example 1 - Step 2

Step 3: Get value on ‘n’ from the user with the help of the InputBox() function.

VBA Byref - Example 1 - Step 3

Step 4: Call the function which returns a Boolean function and accepts VBA ByRef argument Integer and Boolean variables.

VBA Byref - Example 1 - Step 4.jpg

Step 5: Print the res value using Debug.Print.

VBA Byref - Example 1 - Step 5

Step 6: Define the function BooleanExample, which checks whether the given number is even or not.

VBA Byref - Example 1 - Step 6

Step 7: Initialize an If-Else condition to check whether the number given by the user is even or not. It is done by using VBA Mod Operator.

VBA Byref - Example 1 - Step 7

Code:

Sub AnotherByRefExample()
Dim n As Integer
Dim res As Boolean
n = InputBox(“Enter number”)
Debug.Print “Value is: ” & n
BooleanExample n, res
Debug.Print “Is it Even?: ” & res
End Sub

Function BooleanExample(ByRef num As Integer, ByRef value As Boolean)
If num Mod 2 = 0 Then
value = True
Else: value = False
End If
End Function

Step 8: Go to Step 5. Run the code, and the answer will be printed in the Immediate tab in Excel VBA.

VBA Byref - Example 1 - Step 8
VBA Byref - Example 1 - Step 8 - Even-Output

Consequently,

VBA Byref - Example 1 - Step 8 - odd-Output

Example #2

Consider an example where we need to pass VBA ByRef Array and perform multiplication to all the elements in the array.

Step 1: Initialize a sub-procedure to perform ByRef VBA Array.

Example 2 - Step 1

Step 2: Declare the variable number() as an Integer datatype. The parentheses after the variable in Excel VBA denote an array.

Example 2 - Step 2

Step 3: Declare the array size in VBA and initialize values for them till the size capacity of the array, which is 3 in this case.

Example 2 - Step 3

Step 4: Print the variables before performing the operations on them. Create a user-defined subroutine to print the elements in the array.

Example 2 - Step 4

Step 5: Call the sub-routine which multiplies each value by 2. (Go to Step 6)

Example 2 - Step 5

Both the sub-routines in Step 4 and Step 5 are defined after defining the main sub-routine.

Step 6: After calling the sub-routine, print the values.

Example 2 - Step 6

Step 7: Define the sub-routine called in Step 5.

Example 2 - Step 7

Step 8: Declare an iterative variable ‘I,’ which will iterate through all the array elements in a FOR loop.

Example 2 - Step 8

Step 9: Initialize a FOR loop that starts from the array’s leftmost element, which is declared using LBound(), and the rightmost element, which is found using the UBound() function.

Example 2 - Step 9

Step 10: Multiply all the elements by 2.

Example 2 - Step 10

Step 11: Define the sub-routine used to print the array elements called in Steps 4 and 6.

Example 2 - Step 11

Here, we call the sub-routine with VBA ByRef Array.

Step 12: Declare an iterative variable that will loop through the array.

Example 2 - Step 12

Step 13: Initialize a FOR loop that starts from the array’s leftmost element, which is declared using LBound(), and the rightmost element, which is found using the UBound() function.

Example 2 - Step 13

Step 14: Print all the elements using Debug.Print along with the index.

Example 2 - Step 14

Code:

Sub ArrayByRef()
Dim numbers() As Integer
ReDim numbers(1 To 3)
numbers(1) = 10
numbers(2) = 20
numbers(3) = 30
Debug.Print “Before calling the subroutine:”
DisplayArray numbers
DoubleArrayValues numbers
Debug.Print “After calling the subroutine:”
DisplayArray numbers
End Sub

Sub DoubleArrayValues(ByRef arr() As Integer)
Dim i As Integer
For i = LBound(arr) To UBound(arr)
arr(i) = arr(i) * 2
Next i
End Sub

Sub DisplayArray(ByRef arr() As Integer)
Dim i As Integer
For i = LBound(arr) To UBound(arr)
Debug.Print “Element ” & i & “: ” & arr(i)
Next i
End Sub

Step 15: Print the above code. The output is printed in the Immediate tab.

Example 2 - Step 15

Important Things To Note

  • Do use ByRef when modifying the original variable or retrieving updated information.
  • Ensure that the ByRef parameter is correctly declared with the ByRef keyword in both the function/subroutine declaration and call.
  • Consider the scope of the variable being passed ByRef to ensure it is accessible within the function/subroutine.
  • Don’t use ByRef with read-only variables, such as constants or expressions.
  • Do not assume that ByRef will always be the best choice; evaluate whether ByVal or other approaches may be more suitable for your specific scenario.
  • Don’t overlook the potential side effects of modifying variables directly with ByRef; consider the impact on other parts of your code.

Frequently Asked Questions (FAQs)

1. What is the syntax of ByRef in VBA?

ByRef with an Integer parameter:
Sub MultiplyByTwo(ByRef num As Integer)
ByRef with a String parameter:
Sub ChangeText(ByRef text As String)
ByRef with an Array parameter:
Sub UpdateArray(ByRef arr() As Integer)

2. What is the difference between ByRef and ByVal in Excel?

Difference Byref - ByVal

3. What is the use of ByRef in VBA?

ByRef allows you to pass arguments to functions or subroutines by reference.
It enables direct modification of variables or data structures inside a function or subroutine.
ByRef is useful when you want changes made to a parameter to affect the original variable in the calling code.
It is commonly used with arrays to efficiently manipulate and update data.
ByRef can enhance code efficiency by avoiding unnecessary data copying for large data structures.

4. Why is ByRef in VBA not working?

The data type of the parameter should match the data type of the argument being passed.
Verify that the variable being passed ByRef is accessible within the scope of the function/subroutine.
ByRef cannot be used with read-only variables, such as constants or expressions.
Ensure that the function/subroutine containing the ByRef parameter is called in your code.

Download Template

This article must help understand the VBA ByRef Argument formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA ByRef Argument. We will learn how to pass ByRef argument to functions for reference with examples and downloadable excel template. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *