Option Explicit
When this statement is present, VBA won't even execute a procedure if it contains an undeclared variable name. VBA issues the error message shown in Figure 3.1, and you must declare the variable before you can proceed.
FIGURE 3.1 VBA's way of telling you that your procedure contains an undeclared variable
TIP
To ensure that the Option Explicit statement is inserted automatically whenever you insert a new VBA module, enable the Require Variable Declaration option on the Editor tab of the VBE Options dialog box (choose Tools ➪ Options). It is generally considered a best practice to enable this option. Be aware, however, that this option will not affect existing modules; the option affects only those modules created after it is enabled.
Scoping variables
A variable's scope determines in which modules and procedures you can use the variable. Table 3.2 lists the three ways in which a variable can be scoped.
TABLE 3.2 Variable Scope
| Scope | To Declare a Variable with This Scope |
| Single procedure |
Include a Dim or Static statement within the procedure.
|
| Single module |
Include a Dim or Private statement before the first procedure in a module.
|
| All modules |
Include a Public statement before the first procedure in a module.
|
We discuss each scope further in the following sections.
A note about the examples in this chapter
This chapter contains many examples of VBA code, usually presented in the form of simple procedures. These examples demonstrate various concepts as simply as possible. Most of these examples don't perform any particularly useful task; in fact, the task can often be performed in a different (perhaps more efficient) way. In other words, don't use these examples in your own work. Subsequent chapters provide many more code examples that are useful.
Local variables
A local variable is one declared within a procedure. You can use local variables only in the procedure in which they're declared. When the procedure ends, the variable no longer exists, and Excel frees up the memory that the variable used. If you need the variable to retain its value when the procedure ends, declare it as a Static variable. (See the section “Static variables” later in this chapter.)
The most common way to declare a local variable is to place a Dim statement between a Sub statement and an End Sub statement. Dim statements usually are placed right after the Sub statement, before the procedure's code.
NOTE
Dim is a shortened form of Dimension. In old versions of BASIC, this statement was used exclusively to declare the dimensions for an array. In VBA, the Dim keyword is used to declare any variable, not just arrays.
The following procedure uses six local variables declared by using Dim statements:
Sub MySub() Dim x As Integer Dim First As Long Dim InterestRate As Single Dim TodaysDate As Date Dim UserName As String Dim MyValue ' - [The procedure's code goes here] - End Sub
Notice that the last Dim statement in the preceding example doesn't declare a data type; it simply names the variable. As a result, that variable becomes a variant.
You also can declare several variables with a single Dim statement. Here's an example:
Dim x As Integer, y As Integer, z As Integer Dim First As Long, Last As Double
CAUTION
Unlike some languages, VBA doesn't let you declare a group of variables to be a particular data type by separating the variables with commas. For example, the following statement, although valid, does not declare all the variables as integers:
Dim i, j, k As Integer
In VBA, only k is declared to be an integer; the other variables are declared variants. To declare i, j, and k as integers, use this statement:
Dim i As Integer, j As Integer, k As Integer
If a variable is declared with a local scope, other procedures in the same module can use the same variable name, but each instance of the variable is unique to its own procedure.
In general, local variables are the most efficient because VBA frees up the memory that they use when the procedure ends.
Module-wide variables
Sometimes, you want a variable to be available to all procedures in a module. If so, just declare the variable before the module's first procedure (outside of any procedures or functions).
In the following example, the Dim statement is the first instruction in the module. Both Procedure1 and Procedure2 have access to the CurrentValue variable.
Dim CurrentValue as Long Sub Procedure1() ' - [Code goes here] - End SubSub Procedure2() ' - [Code goes here] - End Sub
The value of a module-wide variable retains its value when a procedure ends normally (that is, when it reaches the End Sub or End Function statement). An exception is if the procedure is halted with an End statement. When VBA encounters an End statement, all variables in all modules lose their values.
Public variables
To make a variable available to all the procedures in all the VBA modules in a project, declare the variable at the module level (before the first procedure declaration) by using the Public keyword rather than Dim. Here's an example:
Public CurrentRate as Long
The Public keyword makes the CurrentRate variable available to any procedure in the VBA project, even those in other modules in the project. You must insert this statement before the first procedure in a module (any module). This type of declaration must appear in a standard VBA module, not in a code module for a sheet or a UserForm.
Static variables
Static variables are a special case. They're declared at the procedure level, and they retain their value when the procedure ends normally. However, if the procedure is halted by an End statement, static variables do lose their values. Note that an End statement