Excel 2019 Power Programming with VBA. Michael Alexander

force yourself to declare all the variables that you use, include the following as the first instruction in your VBA module:

      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