Passing Arguments by Value – VBA

In the previous part, we learn about passing procedure arguments by reference. Today we would learn another approach to passing parameters to a procedure. This is with the help of value.

So let us start learning.

Actual and Formal Parameters

In visual basic, we can pass arguments to a procedure by value or by reference. This is known as the argument passing mechanism. The arguments act as the input to procedures that are required for the execution of the code.

The arguments that are passed to the Called Procedure at the time of the Procedure Call are known as Actual Parameters.

Arguments for a procedure need to be defined in the called Procedure declaration statement in brackets known as Formal Parameters.

Passing Arguments by Value

When we pass the arguments to a procedure by value method then the value of the actual parameter is copied into the formal parameters defined in the procedure definition. The computer allocates separate internal memory blocks to both the actual and formal parameters. The changes made in formal parameters do not reflect in the actual parameters.

Syntax to Pass Parameter By Value

[Private:Public] Sub <procedure_name> (ByVal <name> As <data_type>)

We need to tell the mechanism at the time of procedure definition.

• [Private:Public] – This is optional as by default a procedure is public.
• Sub – This is a keyword used to declare a procedure.
• <procedure_name> This is the name of the procedure.
• ByVal – We start bracket to define the procedure parameters. Use the ByVal keyword to pass that parameter using the By Value method.
• <name> – This is the name of the formal parameter.
• As – A keyword used to tell the data type of parameter.
• <data_type> – actual data type that depends on the type of data and memory space allocated to the parameter.

We can define multiple parameters by separating them with commas inside the brackets.

Simple Example of Pass by Value Method

We have a Procedure named Increment that asks for a parameter named x. It increments the value of x by 1 and then prints the incremented value of x.

Sub Increment(ByVal x As Integer)
x = x + 1          'Incrementing Value
MsgBox "We are in Increment Sub. x is:- " & x            'printing the value
End Sub

When we call Increment, we would need to pass the parameter to the Increment Sub Procedure to increase the value by 1 and then display using Message Box.

Now we define the procedure named Driver that calls the Increment Procedure. At the time of the function call, we pass the actual parameter x.

Sub Driver()
Dim x As Integer
MsgBox "Before Increment Call. x is:- " & x         'iinitial
Call Increment(x)          'calling the increment function
MsgBox "After Increment Call. x is:- " & x          'final
End Sub

When we run the Driver Procedure, this is what happens:-

• The value of x is zero by default. A message box loads.

MsgBox "Before Increment Call. x is:- " & x

• Next, we call the Increment procedure using the Call keyword. This x (actual parameter) copies into another x (in the Increment procedure definition). The execution jumps to Increment Procedure now.

Call Increment(x) 

• In the Increment function x increments by 1, and then a message box displays incremented value of x.

x = x + 1          '
MsgBox "We are in Increment Sub. x is:- " & x 

• The execution of the Increment Procedure ends and the control jumps back to the Driver. We printed the value of x in the Driver procedure which is the same as before the Increment Function call. It is unchanged.

MsgBox "After Increment Call. x is:- " & x 

The value of x=0 was passed to the Increment procedure by the Driver Procedure. Increment increased the value of x by 1 but the change was only reflected until the control was in Increment. When control jumped back to Driver, we see that the x is the same before and after the increment function call.

This works because the actual x in Driver and formal x in Increment have separate internal memory allocated. Changes to formal x cannot affect actual x.

This is all about passing arguments by Value. You should also learn about Passing Parameters by Reference.

This brings us to end.

Thank you for reading.