Learn Access 2003 VBA with The Smart Method

98

www.LearnAccessVBA.com

Lesson 5-5: Understand ByRef

and ByVal argument types

In the last two lessons you’ve learned that a sub is used when no return

value is required and that a function is simply a sub that returns a value.

In this lesson you’ll learn that there is a way to make a function return

more than one value, and even a way to return a value from a sub, by

passing arguments by Reference rather than by Value.

There is a crucial difference between passing by reference and by value

and this lesson will enable you to decide upon the most appropriate

method for your needs.

1

If it is not already open, open the VBACode.mdb

spreadsheet and open frmTest in Design View.

2

Add a new command button. Set the

Caption

property to

&By Reference

and the

Name

property to

cmdByReference

.

3

Add another new command button. Set the

Caption

property

to

&By Value

and the

Name

property to

cmdByValue

.

4

Add two new subs to

frmTest

called

ByReference

and

ByValue

with the following code:

Sub ByReference(ByRef strMessage As String)

strMessage = "Changed by the ByReference sub"

End Sub

Sub ByValue(ByVal strMessage As String)

strMessage = "Changed by the ByVal sub"

End Sub

Note the ByRef and ByVal prefixes for each argument. The

significance of this will become apparent later in the lesson.

5

Add the following code to the Click event handlers for each

of the two new command buttons:

Private Sub cmdByReference_Click()

Dim strMessage As String

strMessage = "You can change me"

Call ByReference(strMessage)

Call MsgBox("The message is: " & strMessage)

End Sub

Session5c

Session Five: Professional Grade VBA

© 2007 The Smart Method Ltd

99

Private Sub cmdByValue_Click()

Dim strMessage As String

strMessage = "You can't change me"

Call ByValue(strMessage)

Call MsgBox("The message is: " & strMessage)

End Sub

6

Click the cmdByReference command button.

It can be seen that when a variable is passed By Reference it is

possible for a sub to change the variable’s contents.

7

Click the cmdByValue command button.

It can be seen that when a variable is passed By Value it is

impossible for a sub to change the variable’s contents.

8

Delete the cmdByReference and cmdByValue command

buttons and associated code.

When to pass variables By Reference

Some programmers would argue that you should never pass a variable

by reference because it violates the principle of encapsulation and

introduces a source of potential bugs into your code.

In VBA all arguments are passed by reference as the default (you don’t

even have to use the ByRef keyword). Microsoft have probably made

this decision because passing arguments by reference is slightly faster

than by value as the contents of the variable don’t have to be duplicated

in memory.

In our own code we generally side with the first school of thought and

declare all arguments ByVal but we do use ByRef as a quick and

convenient way of returning multiple values from functions (or subs).