Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Working with Variables

You must consider many issues when creating VBA variables. The way that you declare a variable determines its scope, its lifetime, and more. The following topics will help you better understand declaring variables in VBA.

Declaring Variables

There are several ways to declare variables in VBA. For example, you could simply declare x=10. With this method of variable declaration, you really aren't declaring your variables at all; you're essentially declaring them as you use them. This method is quite dangerous. It lends itself to typos and other problems. If you follow the practice recommended previouslyof always using the Option Explicit statementAccess will not allow you to declare variables in this manner.

You could also type Dim intCounter; the Dim statement declares the variable. The only problem with this method is that you haven't declared the type of the variable to the compiler, so it's declared as a variant variable.

Another common mistake is declaring multiple variables on the same line, as in this example:

Dim intCounter, intAge, intWeight As Integer

In this line, only the last variable is explicitly declared as an integer variable. The other variables are implicitly declared as variants. If you're going to declare multiple variables on one line, make sure each variable is specifically declared, as in the following example:

Dim intCounter As Integer, intAge As Integer, intWeight As Integer

The most efficient and bug-proof way to declare your variables is to strong-type them to the compiler and declare only one variable per line of code, as in this example:

Dim intCounter As Integer
Dim strName As String

As you can see, strong-typing declares the name of the variable as well as the type of data it can contain. This enables the compiler to catch errors, such as storing a string in an integer variable, before your program runs. If implemented properly, this method can also reduce the resources needed to run your programs by selecting the smallest practical data type for each variable.Chapter 12, "Advanced VBA Techniques."

VBA Data Types

VBA offers several data types for variables. Table 7.1 shows a list of the available data types, the standard for naming them, the amount of storage space they require, the data they can store, and their default values.

Table 7.1. Data Types and Naming Conventions

Scope and Lifetime of Variables: Exposing Your Variables as Little as Possible

You have read about the different types of variables available in VBA. Like procedures, variables also have a scope. A variable can be declared as local, private (Module), or public in scope. You should try to use local variables in your code because they're shielded from being accidentally modified by other routines.

Variables also have an attribute referred to as their lifetime. The

lifetime of a variable reflects the time during which the variable actually exists and, therefore, the time during which its value is retained. In the following sections, we take a closer look at how you can set the scope and lifetime of variables.

Local Variables

Local variables are available only in the procedure where they are declared. Consider this example (not included in Chap7ex):

Private Sub cmdOkay_Click
Dim strAnimal As String
strAnimal = "Dog"
Call ChangeAnimal
Debug.Print strAnimal ''Still Dog
End Sub
Private Sub ChangeAnimal
strAnimal = "Cat"
End Sub

This code can behave in one of three ways. If Option Explicit were in effect, meaning that all variables must be declared before they're used, this code would generate a compiler error. If the Option Explicit statement isn't used, strAnimal would be changed to Cat only within the context of the subroutine ChangeAnimal. If the Dim strAnimal As String statement is moved to the General Declarations section of the module, the variable's value is changed to "Cat".Chapter 15, "Debugging: Your Key to Successful Development."

Static Variables: A Special Type of Local Variable

The following examples illustrate the difference between local and static variables. Local variables are reinitialized each time the code is called. You can run the following procedure by opening the form named frmScopeAndLifeTime and clicking the Local Age button. Notice that each time you run the procedure, the numeral 1 is displayed in the txtNewAge text box.

Private Sub cmdLocalAge_Click()
Dim intAge As Integer
intAge = intAge + 1
Me.txtNewAge.Value = intAge
End Sub

Each time this code runs, the Dim statement reinitializes intAge to zero. This is quite different from the following code, which illustrates the use of a static variable:

Private Sub cmdStaticAge_Click()
Static sintAge As Integer
sintAge = sintAge + 1
Me.txtNewAge.Value = sintAge
End Sub

Each time this code executes, the variable called sintAge is incremented, and its value is retained. You can test this by opening on the accompanying CD the form named frmScopeAndLifeTime and clicking the Static Age button.

Private Variables

So far, this discussion has been limited to variables that have scope within a single procedure. Private (module-level) variables can be seen by any routine in the module they were declared in, but not from other modules. Thus, they are private to the module. You declare private variables by placing a Private statement, such as the following, in the General Declarations section of a form, report, or Access module:

[General Declarations]
Option Explicit
Private mintAge As Integer

You can change the value of a variable declared as private from any subroutine or function within that module. For example, the following subroutine increments the value of the private variable mintAge by 1. You can run this code by opening the form named frmScopeAndLifeTime on the accompanying CD and clicking the Module Age button.

Private Sub cmdModuleAge_Click()
mintAge = mintAge + 1
Me.txtNewAge.Value = mintAge
End Sub

Notice the naming convention of using the letter m to prefix the name of the variable, which denotes the variable as a private module-level variable. You should use private declarations only for variables that need to be seen by multiple procedures in the same module; aim for making most of your variables local to make your code modular and more bulletproof.

Public Variables

You can access public variables from any VBA code in your application. They're usually limited to things such as login IDs, environment settings, and other variables that must be seen by your entire application. You can place declarations of public variables in the General Declarations section of a module. The declaration of a public variable looks like this:

Option Explicit
Public gintAge As Integer

Notice the prefix g (a relic of the old Global variables), the proper prefix for a public variable declared in a Standard module. This standard is used because public variables declared in a Standard module are visible not only to the module they were declared in, but also to other modules. The following code, placed in the Click event of the cmdPublic command button, increments the public variable gintAge by 1. You can run this code by opening the form frmScopeAndLifeTime and clicking the Public Age button.

Private Sub cmdPublicAge_Click()
gintAge = gintAge + 1
Me.txtNewAge.Value = gintAge
End Sub