Passing Procedure Arguments by Reference – VBA

We already know about Sub Procedures and Functions in VBA programming. The Sub Procedure is used to execute a block of code without returning any value whereas functions can return a value to the Calling Procedure. We would now learn about passing procedure arguments by reference.

So let us begin.

Function Parameters and Method to Pass Parameters

Parameters or Arguments work as an input to the Function or a Sub Procedure. The inputs need to be defined at the time of function declaration. We need to specify the function arguments at the time of the function call so that the function processes those inputs.

There are two methods to pass arguments to a procedure.

• By Reference
• By Value

You can have the practice workbook to follow along with the article.

Passing Arguments by Reference

When we pass the function arguments by reference, the reference to the actual parameters from the calling procedure is passed to the called procedure. This means, all the changes made by the called procedure in the passed arguments will be reflected in the actual parameters being passed by the calling procedure.

Now, this might seem confusing, but an example is a saver.

Basic Example for Understanding

Just know that here we have two Functions named Function1 and Function2. Function1 calls Function2 which needs one Parameter named x.

Function function1()
Dim x As Integer
Debug.Print x       'printing value of x before function2 call
Call function2(x)   'calling function2
Debug.Print x       'printing value of x after function2 call
End Function

Function function2(ByRef x As Integer)
x = x + 10          'changing the value of x in function2
End Function

Put this code in a new module in VBE.

• Press the Ctrl G keys to open the Immediate Window.
• Put your cursor anywhere in between the code of Function1 and press the F8 key to execute the code line by line.
• First, we print the value of x which is zero by default.
• Then the function2 is called and the execution would jump to the function2.
• The value of x increases by 10 in function2.
• The function2 execution completes and the control goes back to the next statement in the function after the function2 Call.
• The value of x is Printed again on the Immediate Window.

Here, function2 changes the value of x in its scope, and that change reflects in the x declared in the scope of function1 when we print the value of x after the function2 call statement.

This is how references work.

Syntax to Pass Value by Reference

We need to define the arguments of the Function or Sub Procedure in its declaration statement. Enclose the parameters in brackets after the name of the procedure.

Function <function_name> ( ByRef <parameter_name1> As <data_type1> ) As <return_type>

if it is a sub procedure then,

Sub <sub_procedure_name> ( ByRef <parameter_name1> As <data_type> )

• We specify the procedure type followed by the name of procedure.
• Start the bracket and use the ByRef keyword to signify that we are using the reference to the actual parameter.
• Specify the name of parameter, As keyword and the data type of parameter.
• You can specify multiple function arguments separated with comma (,)

For Example

Function change(ByRef number As Integer) As Integer
'code
End Function

Sub swapping(ByRef x As Integer, ByRef y As Integer)
'code
End Sub

If we remove the ByRef keyword, then a reference is passed by default and not the value.

Different Examples to Pass Parameters by Reference

We would now implement some examples to pass procedure parameters by reference.

Example 1 – Passing One Parameter to Sub Procedure

Here we have the code for a Sub Procedure that increments the value of passed parameter by 25 each time it is called.

Sub Increment(ByRef counter As Integer)
counter = counter + 25
End Sub

So when we call this Sub Procedure from another procedure, each time we call this Increment along with the actual parameter, the value is increased by 25.

Here we have the Example code which is driver code and calls the Incremen Sub Procedure.

Sub Example()
Dim num As Integer
Debug.Print num
'increasing the value by 25
Call Increment(num)
Debug.Print num
'increasing the value by 25, two times
Call Increment(num)        'calling the increment 
Call Increment(num)
Debug.Print num
End Sub

You run the Example() and get this output on immediate window.

Example 2 – Swapping two Variables

We can swap the values of two integer type variables with the following swap function.

Function swap(num1 As Integer, num2 As Integer)
Dim temp As Integer
temp = num1
num1 = num2
num2 = temp
End Function

When we call this function, we need to specify the two variables and their values would be swapped.

Function Swap_Result()
Dim x As Integer, y As Integer
x = 10
y = 20
Debug.Print "Before swap: x=" & x & " and y=" & y
Call swap(x, y)
Debug.Print "After swap: x=" & x & " and y=" & y
End Function

We would now run Swap_Result line by line using the F8 key.

We have learned about passing procedure arguments by reference.

Thank you for reading.