Working with Variables

You must consider many issues when creating VBA variables. The way you declare a variable determines its scope, its lifetime, and more. The following sections will help you better understand how to declare and work with 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 variables at all; you're essentially declaring variables as you use them. This method is quite dangerous. It lends itself to typos and other problems. If you follow the practice recommended previously—of always using the Option Explicit statement—Access will not allow you to declare variables in this manner.

You can also type Dim intCounter to declare the variable intCounter because the Dim statement declares a variable. The only problem with this method is that you haven't declared the type of the variable to the compiler, so you have declared a variant variable. Variant variables are slow and are also dangerous in that the compiler cannot do type checking to try to ensure that you store valid data within them (for example, a number in an integer 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, you've only explicitly declared the last variable as an integer variable. You've implicitly declared the other variables as variants. If you're going to declare multiple variables on one line, you need to make sure you specifically declare each variable, as in the following example:

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

The most efficient and bug-proof way to declare variables is to strongly 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, strongly 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 a program runs. If implemented properly, this method can also reduce the resources needed to run programs by selecting the smallest practical data type for each variable.

VBA Data Types

VBA offers several data types for variables. Table 23.1 lists the available data types, the standards for naming them, the amount of storage space they require, the data they can store, and their default values.

Table 23.1. VBA Data Types

Data Type	Naming Conv Default Example	Storage of Data	Range	Value
Byte	bytValue	1 byte	0 to 255	0
Boolean	boolAnswer	2 bytes	True or False	False
Integer	intCounter	2 bytes	–32768 to 32767	0
Long Integer	lngAmount	4 bytes	–2,147,483,648 to 2,147,483,647	0
Single	sngAmount	4 bytes	–3.402823E38 to –1.401298E-45 for negative values; from 1.401298E-45 to 3.402823E38 for positive values	0
Double	dblValue	8 bytes	–1.79769313486231E308 to –4.94065645841247E-324 for negative values; from 4.94065645841247E-324 to 1.79769313486232E308 for positive values	0
Currency	curSalary	8 bytes	–922,337,203,685,477.5808 to 922,337,203,685,477.5807	0
Date	dtmStartDate	8 bytes	1/1/100 to 12/31/9999	12/30/1899
Object Reference	objExcel	4 bytes	Any object	N/A
Fixed String	strName	varies	Up to 65,526 characters	""
Variable String	strName	varies	Up to approximately 2 billion characters	""
Variant	varData	varies	Can contain any of the other data types except Fixed String	Empty
User-Defined Data Type	typEmp	varies	Based on Elements	N/A
Decimal	decTaxAmount	12 bytes	+/–79,228,162,514,264,337,593,543,950,335 with 0 decimal places to ±7.9228162514264337593543950335 with 28 decimal places	0

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

In this hour you have read about the different types of variables available in VBA. Like procedures, variables also have scope. You can declare a variable as Local, Private (Module), or Public in scope. You should try to use Local variables in code because they're shielded from being accidentally modified by other routines.

Variables have an attribute that is 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 the compiler retains the value of the variable. The following sections 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 Chap23Ex.mdb):

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 two ways. If Option Explicit were in effect, meaning that you must declare all variables before you use them, this code would generate a compiler error. If you don't use the Option Explicit statement, the code would change strAnimal to Cat only within the context of the subroutine ChangeAnimal.

Notice the Debug.Print statement in the cmdOkay_Click event routine shown previously (see Figure 23.8). The code prints the expression that follows the Debug.Print statement in the Immediate window. The Immediate window is a tool that helps you to troubleshoot applications. You can invoke the Immediate window from almost anywhere within an application. The easiest way to activate the Immediate window is by using the Ctrl+G key combination. Access then places you in the VBE within the Immediate window, where you can view the expressions that the compiler printed.

Static Variables

Static variables are a special type of Local variables. The following examples illustrate the difference between Local and Static variables. The compiler reinitializes Local variables each time you call the code.

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 code displays the numeral 1 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, it increments the variable called sintAge and retains its value. You can test this by opening 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. You can see Private (Module) variables in any routine in the module you declared them 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

The code can change the value of a variable declared as Private by any subroutine or function within that module. For example, the following subroutine increments the value of the Private variable mintAge by 1:

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

You can run this code by opening the form frmScopeAndLifeTime and clicking the Module Age button.

Notice the naming convention of using the letter m to prefix the name of the variable. This 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. You should aim to make most of your variables Local variables in order to make your code modular and more bugproof.

Public Variables

You can access Public variables from any VBA code in an application. They're usually limited to things such as login IDs, environment settings, and other variables that an entire application must see. 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), which is the proper prefix for a Public variable declared in a Standard module. You should use this standard 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:

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

You can run this code by opening the form frmScopeAndLifeTime and clicking the Public Age button.