Automating Microsoft Access with VBA

What's Scope?

In this chapter, you add an important building block to your programming foundation. Specifically, this chapter discusses when a variable or procedure is accessible to code in another procedure. Two interlocking concepts control which variables a procedure can use: the scope of a variable dictates its visibility to other code, and the lifetime of a variable dictates when it contains valid data. Although the examples use variables, the information in this chapter also applies to constants.

Most variables work within the confines of the procedure in which they're declared. Sometimes you need a variable that can be seen by (is accessible to) other procedures. A variable's visibility is known as its scope. Variable scope is similar to the concept discussed in "Declaring Procedures as Public or Private" in Chapter 4. Just as a procedure can be called from another module, a variable can be seen by other procedures. There are three levels of variable scope:

• Local Variables exist only within the procedure that declares them.

• Module Variables are available to all procedures in the module in which they're declared.

• Public Variables are available to the entire application.

Procedure-Level Variables

At this point, you've seen plenty of procedure-level variables. These are variables that are available to only the procedure that declares them. What that means is that the variable can be referenced only by the procedure that defines it.

Because procedure-level variables are limited to just the one procedure, you can have several variables with the same name in the same module (as long as they're in different procedures). However, you can't have more than one variable with the same name in the same procedure.

To illustrate procedure-level scope, launch the VBE (press Alt+F11), open a blank standard module, and enter the following procedures:

Function ProcedureLevel1() Dim strScope As String strScope = "Procedure Level Variable" Debug.Print strScope End Function Function ProcedureLevel2() strScope = "No procedure level declaration here" Debug.Print strScope End Function

Or use the Chapter 9 sample module in the TrackTime sample database.

NOTE

The sample database includes some changes made over the course of the chapter. If you want to follow along with the code from start to finish, you should re-create the samples in your own database.

With the insertion point somewhere in ProcedureLevel1, press F5. The procedure assigns the string "Procedure Level Variable" to strScope and then prints the variable's value to the Immediate window. There's nothing new here you've seen this many times by now.

Notice that ProcedureLevel2 refers to a variable by the same name. However, this procedure doesn't declare the variable. With the insertion point in ProcedureLevel2, press F5. Doing so returns the error shown in Figure 9.1. That's because the variable strScope doesn't exist for ProcedureLevel2. Click OK, and then click Reset to clear the error.

There are two ways to fix this error:

• Declare strScope in both procedures.

• Make strScope a module-level variable.

Module-Level Variables and Constants

Module-level variables can be seen by all the procedures in the module. The key to creating a module-level variable (or constant) is to declare the variable in the module's Declarations section not in a procedure.

We can easily illustrate a module-level variable by moving the Dim statement from ProcedureLevel1 from the previous example to the Declarations section. After doing so, both procedures run without producing an error. To make the switch, complete the following steps:

1. Using Figure 9.2 as a guide, delete the Dim strScope As String statement in ProcedureLevel1 and copy it to the General Declarations section.

   Figure 9.2. Copy the declaration statement from the procedure to the General Declarations section.

2. After moving the declaration statement, position the insertion point in ProcedureLevel1 and press F5 to print strScope's value in the Immediate window. (The procedure prints the string "Procedure Level Variable".)

3. Now, position the insertion pint in ProcedureLevel2 and press F5. This time, the procedure runs properly and assigns a string value to strScope and then prints that value, "No procedure level declaration here" to the Immediate window, as shown in Figure 9.3. That's because strScope is now available to all the procedures in the module.

   Figure 9.3. Both procedures use the module-level variable declared in the module's General Declarations section.

Public Variables and Constants

Public is the third and last level of scope in this discussion. A public variable is accessible from outside the module where it's created. Let's test strScope to determine whether it's currently a public variable. To do so:

1. Create a new standard module by choosing Insert, Module.

2. Enter the following procedure in the empty module:

   Function PublicVariableTest() strScope = "Now the variable's public" Debug.Print strScope End Function Or, open the module Chapter9PublicExample in the TrackTime sample database.

3. With the insertion point in PublicVariableTest, press F5. The result is the same error you saw earlier (refer to Figure 9.1). The variable strScope doesn't exist within the scope of the current module.

4. Click OK and then click Reset to clear the error.

There are two ways to fix this error:

• Declare a procedure-or module-level variable in the new module.

• Make strScope a public variable in the first module (Chapter 9's sample module).

To create a public variable, return to the Chapter 9 sample module without closing the new module. In the Chapter 9 sample module's General Declaration's section, change Dim to Public, as shown in Figure 9.4.

After changing the declaration to public, return to the second module (the one with PublicVariableTest). With the insertion point somewhere in PublicVariableTest, press F5. This time, the procedure works fine, printing the string as shown in Figure 9.5. The variable, strScope, is now a public variable. That means PublicVariableTest can use strScope even though it's actually created (seemingly) by another module.

TIP

To declare a public constant, precede the appropriate Const statement with the Public keyword as follows:

Public constant As datatype = value

NOTE

Object modules (for forms and reports) and class modules have an extra scope level, known as the friend level. Friend level gives access to a variable or procedure to other object modules within the same project, similarly to the public level. The difference is that public variables and procedures are accessible by other software objects outside the Access database, whereas friend variables and procedures aren't. To declare a friend-level variable or procedure, use the Friend keyword instead of Public or Private.