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
- 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 (,)
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.