Learn Access 2003 VBA with The Smart Method

102

www.LearnAccessVBA.com

Lesson 5-7: Understand scope

What is scope.

Every variable that you declare has scope.

The word: visibility is often used in place of scope and is a more intuitive

word for describing the concept.

There are three possible scopes for a variable:

Local

Some programmers insist that this is the only type of variable that should

ever be declared (though in reality life would be very difficult without

the use of Module scoped variables).

A local variable is only available within the sub or function in which it is

declared.

Every variable declared with a Dim statement within a sub or function

has local visibility.

Module (or Private)

Module level variables have many indispensable uses but should be used

sparingly and with caution. Module level variables are visible within the

code module in which they are declared. There’s a code module behind

every form and report and you can also create modules that are not

associated with any form or report.

Every variable declared with a Dim statement at the top of a module (just

after the Option Explicit statement) will have module visibility.

Good naming convention requires that module level variables be

prefixed with a lowercase letter m, for example mstrFormMode. (This rule

and others are included in Appendix A – The Rules).

Public

Many programmers believe that the use of Public variables should never

be tolerated. They have the ability to completely destroy the integrity of

your application and to make it almost impossible to debug. We’d agree

with this view.

Public variables shouldn’t be confused with public constants which are

extremely useful. In a later lesson we’ll create a public constant to store

the name of the application so that changing it in one place will

propagate the change throughout the application.

For now it is enough to remember that the use of Public variables is

almost always a bad idea.

1

Declare a module-level (Private) variable

Add the following code to the very top of the form module (just

under Option Explicit.

Dim mlngCounter As Long

note

Why Module level

variables can be

dangerous

Many beginner or self-taught

programmers will

inappropriately use a module

level variable instead of sub

and function arguments. This

approach results in very buggy

code.

For example: a novice

programmer might think that

instead of having the argument

strMessage as String passed into

the ThankYouMessage sub it

would be easier to declare a

module-level variable called

mstrMessage.

The mstrMessage variable

would then be set before calling

ThankYouMessage with no

arguments like this:

mstrMessage = “Hello"

Call ThankYouMessage

This approach introduces the

possibility of a new class of

bugs as the contents of

mstrMessage can easily be

corrupted.

Session5d

Session Five: Professional Grade VBA

© 2007 The Smart Method Ltd

103

Because the variable is declared outside any sub it is visible

throughout this module (but nowhere else).

2

Add code to update the counter every time a command

button is clicked.

Add the following code to each command button’s event handler:

mlngCounter = mlngCounter + 1

Me.Caption = "You have now clicked: " & _

mlngCounter & " times this session"

The code will update the module-level variable to keep a running

total of the number of times either command button has been

clicked since the form was opened. The total number of clicks will

then be displayed in the caption of the form.

3

Test your form.

Code listing

Option Explicit

Dim mlngCounter As Long

Private Sub cmdAddTwoNumbers_Click()

Dim dblFirstNumber As Double

Dim dblSecondNumber As Double

Dim dblSumOfNumbers As Double

Dim strSumOfNumbers As String

mlngCounter = mlngCounter + 1

Me.Caption = "You have now clicked: " & mlngCounter & "

times this session"

dblFirstNumber = Me.txtFirstNumber.Value

dblSecondNumber = Me.txtSecondNumber.Value

dblSumOfNumbers = AddTwoNumbers(dblFirstNumber,

dblSecondNumber)

strSumOfNumbers = CStr(dblSumOfNumbers)

lblResult.Caption = strSumOfNumbers

End Sub

Private Sub cmdPressMe_Click()

Dim strMessage As String

Dim strTitle As String

mlngCounter = mlngCounter + 1

Me.Caption = "You have now clicked: " & mlngCounter & "

times this session"

strMessage = "Thank you for pressing me"

strTitle = "Thank you"

Call ThankYouMessage(strMessage, strTitle)

End Sub