VBA Programming Series...22

Declaring and scoping variables

If you read the previous sections, you now know a bit about variables and data types. In this section, you discover how to declare a variable as a certain data type.

If you don’t declare the data type for a variable you use in a VBA routine, VBA uses the default data type: Variant. Data stored as a variant acts like a chame-leon; it changes type depending on what you do with it. For example, if a vari-able is a Variant data type and contains a text string that looks like a number

(such as “143”), you can use this variable for string manipulations as well as numeric calculations. VBA automatically handles the conversion. Letting VBA handle data types may seem like an easy way out — but remember that you sacrifice speed and memory.

Before you use variables in a procedure, it’s an excellent practice to declare your variables — that is, tell VBA each variable’s data type. Declaring your variables makes your program run faster and use memory more efficiently. The default data type, Variant, causes VBA to repeatedly perform time-consuming checks and reserve more memory than necessary. If VBA knows a variable’s data type, it doesn’t have to investigate and can reserve just enough memory to store the data.

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

Option Explicit

When this statement is present, you won’t be able to run your code if it con-tains any undeclared variables.

You need to use Option Explicit only once: at the beginning of your module, prior to the declaration of any procedures in the module. Keep in mind that the Option Explicit statement applies only to the module in which it resides. If you have more than one VBA module in a project, you need an Option Explicit statement for each module.

Suppose that you use an undeclared variable (that is, a Variant) named

CurrentRate. At some point in your routine, you insert the following statement:

CurentRate = .075

This misspelled variable, which is difficult to spot, will probably cause your routine to give incorrect results. If you use Option Explicit at the beginning of your module (forcing you to declare the CurrentRate variable), Excel gener-ates an error if it encounters a misspelled variation of that variable.

To ensure that the Option Explicit statement is inserted automatically when-ever you insert a new VBA module, turn on the Require Variable Definition option. You find it in the Editor tab of the Options dialog box (in the VBE, choose Tools➪Options). I highly recommend doing so.

Declaring your variables also lets you take advantage of a shortcut that can save some typing. Just type the first two or three characters of the variable name, and then press Ctrl+Space. The VBE will either complete the entry for you or — if the choice is ambiguous — show you a list of matching words to select from. In fact, this slick trick works with reserved words and functions, too.

You now know the advantages of declaring variables, but how do you do this? The most common way is to use a Dim statement. Here are some examples of variables being declared:

Dim YourName As String

Dim AmountDue As Double

Dim RowNumber As Long

Dim X

The first three variables are declared as a specific data type. The last vari-able, X, is not declared as a specific data type, so it’s treated as a Variant (it can be anything).

Besides Dim, VBA has three other keywords that are used to declare variables:

✓ Static ✓ Public ✓ Private

I explain more about the Dim, Static, Public, and Private keywords later on, but first I must cover two other topics that are relevant here: a variable’s scope and a variable’s life.

Recall that a workbook can have any number of VBA modules. And a VBA module can have any number of Sub and Function procedures. A variable’s scope determines which modules and procedures can use the variable. Table 7-2 describes the scopes in detail.

Confused? Keep turning the pages and you’ll see some examples that will make this stuff crystal clear.

Scope	How the Variable Is Declared
Procedure only	By using a Dim or a Static statement in the pro-
	cedure that uses the variable
Module only	By using a Dim or a Private statement before
	the first Sub or Function statement in the
	module
All procedures in all modules	By using a Public statement before the first Sub
	or Function statement in a module

Procedure-only variables

The lowest level of scope for a variable is at the procedure level. (A procedure is either a Sub or a Function procedure.) Variables declared with this scope can be used only in the procedure in which they are declared. When the pro-cedure ends, the variable no longer exists (it goes to the great bit bucket in the sky), and Excel frees up its memory. If you execute the procedure again, the variable comes back to life, but its previous value is lost.

The most common way to declare a procedure-only variable is with a Dim statement. Dim doesn’t refer to the mental capacity of the VBA designers. Rather, it’s an old programming term that’s short for dimension, which simply means you are setting aside memory for a particular variable. You usually place Dim statements immediately after the Sub or Function state-ment and before the procedure’s code.

The following example shows some procedure-only 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 declares only the variable itself. The effect is that the variable MyValue is a Variant.

By the way, you can also declare several variables with a single Dim state-ment, as in the following example:

Dim x As Integer, y As Integer, z As Integer Dim First As Long, Last As Double

Unlike some languages, VBA doesn’t allow you to declare a group of variables to be a particular data type by separating the variables with commas. For example, though valid, the following statement does not declare all the vari-ables as Integers:

Dim i, j, k As Integer

In this example, only k is declared to be an Integer; the other variables are declared to be Variants.

If you declare a variable with procedure-only 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, variables declared at the procedure level are the most efficient because VBA frees up the memory they use when the procedure ends.

Module-only variables

Sometimes, you want a variable to be available to all procedures in a module. If so, just declare the variable (using Dim or Private) before the module’s first Sub or Function statement — outside any procedures. This is done in the Declarations section, at the beginning of your module. (This is also where the Option Explicit statement is located.) Figure shows how you know when you’re working with the Declarations section.

As an example, suppose that you want to declare the CurrentValue variable so that it’s available to all the procedures in your module. All you need to do is use the Dim statement in the Declarations section:

Dim CurrentValue As Integer

With this declaration in place — and in the proper place — the CurrentValue variable can be used from any other procedure within the module, and it retains its value from one procedure to another.

Public variables

If you need to make a variable available to all the procedures in all your VBA modules in a workbook, declare the variable at the module level (in the Declarations section) by using the Public keyword. Here’s an example:

Public CurrentRate As Long

The Public keyword makes the CurrentRate variable available to any proce-dure in the workbook — even those in other VBA modules. You must insert this statement before the first Sub or Function statement in a module.

If you would like a variable to be available to modules in other workbooks, you must declare the variable as Public and establish a reference to the workbook that contains the variable declaration. Set up a reference by using the Tools➪References command in VBE. In practice, sharing a variable across workbooks is hardly ever done. In fact, I’ve never done it once in my entire VBA programming career. But I guess it’s nice to know that it can be done, in case it ever comes up as a Jeopardy! question.

Static variables

Normally, when a procedure ends, all the procedure’s variables are reset. Static variables are a special case because they retain their value even when the procedure ends. You declare a static variable at the procedure level. A static variable may be useful if you need to track the number of times you execute a procedure. You can declare a static variable and increment it each time you run the procedure.

As shown in the following example, you declare static variables by using the

Static keyword:

Sub MySub()

Static Counter As Integer

Dim Msg As String

Counter = Counter + 1

Msg = “Number of executions: “ & Counter

MsgBox Msg

End Sub

The code keeps track of the number of times the procedure was executed. The value of the Counter variable is not reset when the procedure ends, but it is reset when you close and reopen the workbook.

Even though the value of a variable declared as Static is retained after a vari-able ends, that variable is unavailable to other procedures. In the preceding MySub procedure example, the Counter variable and its value are available only within the MySub procedure. In other words, it’s a procedure-level variable.

Life of variables

Nothing lives forever, including variables. The scope of a variable not only determines where that variable may be used, it also affects under which cir-cumstances the variable is removed from memory.

You can purge all variables from memory by using three methods:

✓ Click the Reset toolbar button (the little blue square button on the Standard toolbar in the VBE).

✓ Click “End” when a runtime error message dialog box shows up.

✓ Include an End statement anywhere in your code. This is not the same as an End Sub or End Function statement.

Otherwise, only procedure-level variables will be removed from memory when the macro code has completed running. Static variables, module level variables, and global (public) variables all retain their values in between runs of your code.

If you use module-level or global-level variables, make sure they have the value you expect them to have. You never know whether one of the situations I just mentioned may have caused your variables to lose their content!