Microsoft Access 2010 Bible

Chapter 11: Mastering VBA Data Types and Procedures

433

address that is known by anyone passing by the building; each office or suite within that building

will has a number that is private within that building.

Variables declared within a procedure are local to that procedure and can’t be used or referenced

outside of that procedure. Most of the listings in this chapter have included a number of variables

declared within the procedures in the listings. In each case, the Dim keyword was used to define

the variable. Dim is an instruction to VBA to allocate enough memory to contain the variable that

follows the Dim keyword. Therefore, Dim intMyInt As Integer allocates less memory (2

bytes) than Dim dblMyDouble As Double (8 bytes).

The Public (or Global) keyword makes a variable visible throughout an application. Public

can only be used at the module level and can’t be used within a procedure. Most often, the

Public keyword is used only in standard (standalone) modules that are not part of a form. Figure

11.8 illustrates variables declared with three very different scopes.

FIGURE 11.8

Variable scope is determined by the variable’s declaration.

Standard Module A

Procedure A

Public X1 As Integer

Private Y1 As Integer

Dim X2 As Integer

X2 = 99

Y1 = 12

Procedure B

Dim Y2 As Integer

Y2 = 55

X1 = 19

Every variable declared in the general section of the standard module is public throughout the

application unless the Private keyword is used. Private restricts the visibility of a variable to

the module in which the variable is declared. In Figure 11.8, the X1 integer declared with Public

scope at the top of the module will be seen everywhere in the application while the Private Y1

integer declared in the next statement is accessible only within the module.

Misunderstanding variable scope is a major cause of serious bugs in many Access applications. It’s

entirely possible to have two same-named variables with different scopes in an Access VBA project.

When ambiguity exists, Access always uses the “closest” declared variable.