Passing by Value or by Reference

PASSING BY VALUE OR BY REFERENCE

When a variable appears in a program, a location is specified in the computer's memory. When a value is assigned to the variable, the value is stored in that memory location.

When a variable name is passed to a procedure via a parameter list, a new memory location is not created. Rather, the computer goes back to the original memory location designated in the main program in order to obtain the value of the variable. This approach of getting data is referred to as passing the parameter by reference.

Passing by reference is the default method to pass variables in a procedure.

In such cases, changing the value of the parameter in the Sub will result in its value being changed in the memory location specified in the main program.

In some case, you may want to perform operation on a variable in a procedure that change the value of the variable. At the same time, you may want to retain the original value for some other purpose. This can be accomplished by passing the variable using the passing by value method.

You need to enclose the variable within a pair of parentheses when the subroutine is called.

The following example (ValRef.xls) will illustrate the differences between passing by value and passing by reference.

Sub Test()
x = 1
y = 0
MsgBox "Before Call: x = " & x & " and y = " & y
Call ValRef((x), y)
MsgBox "After Call: x = " & x & " and y = " & y
End Sub

Sub ValRef(x, y)
y = x + 1
x = 0
MsgBox "Within Sub: x = " & x & " and y = " & y
End Sub

When you run Sub Test, the first Msgbox yields

Before Call: x = 1 and y = 0

After the OK button is clicked, the Sub procedure ValRef is called. Notice that (x)) is passed by value, whereas y is passed by reference. Inside the Sub, y is assigned the value of x + 1. Since x is equal to 1, y is set equal to 2. Then x is assigned a value of 0. The MagBox shows:

Within Sub: x = 0 and y = 2

After OK is clicked, control is transferred back to the calling program, where the final MsgBox is displayed:

After Call: x = 1 and y = 2

Note that because x is passed by value, even though it is changed to 0 in the procedure, its original value of 1 is maintained in the main program. The same is not true for y, which was passed by reference.

If an expression is passed as an argument of a procedure, then that argument is treated as a value argument (passing by value).

For example

Call Example(a + 5, b, c)

The computer first evaluates the expression and places the result in a temporary memory location. The reference for this location is then passed to the procedure as the first argument. As a result, the first argument is treated as a call by value parameter.

Now it should be clear why the parentheses are used to pass a variable by value. In a sense, the parentheses tick the computer into thinking that you are passing an expression.

Besides using the parentheses to place around arguments that are to be passed by value into a function, we can also use the key words, ByVal (or ByRef for reference arguments) in the function heading. The above example can therefore be written as:

Option Explicit
Sub Test()
Dim x As Integer, y As Integer
x = 1
y = 0
MsgBox "Before Call: x = " & x & " and y = " & y
Call ValRef(x, y)
MsgBox "After Call: x = " & x & " and y = " & y
End Sub

Sub ValRef(ByVal x, ByRef y)
y = x + 1
x = 0
MsgBox "Within Sub: x = " & x & " and y = " & y
End Sub

Here is another example (TestSwitch.xls) of a Sub having reference parameters.

We want to write a Sub procedure having two parameters, say x and y. The purpose of the procedure is to exchange the values in those two parameters.

The original values of x and y (named as a and b in the Excel sheet (called TestSwitch) are contained in cells B4 and B5, respectfully.
The new results for x and y will be written to cells B8 and B9, respectfully.

The programs are:

Sub TestSwitch()
' program to test the switch sub procedure
' K. Ming Leung, Oct. 16, 2007
Sheets("TestSwitch").Select
Range("B4").Select
a = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
b = ActiveCell.Value
Call switch(a, b)
ActiveCell.Offset(3, 0).Select
ActiveCell.Value = a
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = b
End Sub

Sub switch(x, y)
' first store the value of x in a temporary variable z
z = x
' place the value of y into variable x
x = y
' place the value of z (original value of x) into variable y
y = z
End Sub

Here is a slightly more complicated example of functions having value and reference parameters:

Option Explicit
Sub testValRef()
Dim a As Integer, b As Integer, c As Integer
Dim msg As String
a = 10: b = 20: c = 30
msg = "1st message in main: " & a & " " & b & " " & c & Chr(13)
Call bumble(b, c, a, msg) 'NOTE THE ORDER is b, c, a
msg = msg & "2nd message in main: " & a & " " & b & " " & c & Chr(13)
MsgBox msg
End Sub

Sub bumble(ByRef x As Integer, ByRef y As Integer, ByVal z As Integer, _
ByRef msg As String)
msg = msg & "Starting message in bumble: " & x & " " & y & " " & z & Chr(13)
x = x + 1
y = y + jumble(4, z, msg)
z = z + 3
msg = msg & "Ending message in bumble: " & x & " " & y & " " & z & Chr(13)
End Sub

Function jumble(ByVal x As Integer, ByRef y As Integer, ByRef msg As String)
msg = msg & "In function jumble." & Chr(13)
x = y
y = y - 1
jumble = x
End Function

The result of the program is:

1st message in main: 10 20 30
Starting message in bumble: 20 30 10
In function jumble.
Ending message in bumble: 21 40 12
2nd message in main 10 21 40

Make sure you can follow every line in the program to see why the output message is the way it is. Keep track of each procedure call and the value and reference parameters that are passed to the procedures.