Curent Rate

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 (and declare the CurrentRate variable), Excel generates an error if it encounters a misspelled variation of that variable.

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

You now know the advantages of declaring variables, but how do you do this? Before getting into the mechanics, I need to discuss one more topic: a variable's scope.

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.

Table 7-2

Variable's Scope


How the Variable Is Declared

Procedure only

By using a Dim or a Static statement in the

procedure that uses the variable

Module only

By using a Dim 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

If you're completely confused at this point, don't despair. I discuss each of these in the following sections.

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 procedure ends, the variable no longer exists, 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 placed between a Sub statement and an End Sub statement (or between a Function and an End Function statement). The Dim keyword is 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 statement and before the procedure's code.

The following example shows some procedure-only variables declared by using Dim statements:



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] ...



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 statement, 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 variables as integers:

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 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, discussed earlier in this chapter, is located.) Figure 7-2 shows how you know when you are working with the Declarations section.

Figure 7-2:

Each VBA module has a Declarations section, which appears before any Sub or Function procedures.

^ Büok2 - Modulel (Code)



| (Declarations)


Option Explicit 1

SP -1

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 procedure 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 ToolsOReferences command in VBE.

Static Variables

Normally, when a procedure ends all of the 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:



Static Counter As Integer

Dim Msg As String

Counter = Counter + 1

Msg = "Number of executions:

" & Counter

MsgBox Msg



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.

^\NG/ Even though the value of a variable declared as Static is retained after a variable 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.

Was this article helpful?

0 0

Post a comment