Scoping variables

A variable's scope determines in which modules and procedures the variable can be used. Table 8-2 lists the three ways in which a variable can be scoped.

Table 8-2: VARIABLE SCOPE

Open table as spreadsheet

Table 8-2: VARIABLE SCOPE

Open table as spreadsheet

Scope

How a Variable with This Scope Is Declared

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.

I discuss each scope further in the following sections.

A Note about the Examples in This Chapter

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 do not 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 a variable declared within a procedure. Local variables 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 need the variable to retain its value when the procedure ends, declare it as a Static variable. (See "Static variables," later in this section.)

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 If you're curious about this word, 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

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. For example:

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

Caution Unlike some languages, VBA does not 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:

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.

Another Way of Data-Typing Variables

Like most other dialects of BASIC, VBA lets you append a character to a variable's name to indicate the data type. For example, you can declare the MyVar variable as an integer by tacking % onto the name:

Dim MyVar%

Type-declaration characters exist for most VBA data types. Data types not listed in the following table don't have type-declaration characters.

> Open table as spreadsheet

Data Type

Type-Declaration Character

Integer

a o

Long

&

Single

!

Double

#

Currency

@

String

$

This method of data typing is essentially a holdover from BASIC; it's better to declare your variables by using the other techniques described in this chapter. I list these type declaration characters here just in case you encounter them in an older program._

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 Procedurel and Procedure2 have access to the CurrentValue variable.

Dim CurrentValue as Integer

Sub Procedurel()

End Sub

Sub Procedure2()

End Sub

Normally, the value of a module-wide variable does not change 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 module-wide variables 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 within 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 are 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.

You declare static variables by using the Static keyword:

Sub MySub()

Static Counter as Integer - [Code goes here] -End Sub

Variable Naming Conventions

Some programmers name variables so that users can identify their data types by just looking at their names. Personally, I don't use this technique very often because I think it makes the code more difficult to read, but you might find it helpful.

The naming convention involves using a standard lowercase prefix for the variable's name. For example, if you have a Boolean variable that tracks whether a workbook has been saved, you might name the variable bWasSaved. That way, it is clear that the variable is a Boolean variable. The following table lists some standard prefixes for data types:

; Open table as spreadsheet

Data Type

Prefix

Boolean

b

Integer

i

Long

l

Single

s

Double

d

Currency

c

Date/Time

dt

String

str

Object

obj

Variant

v

User-defined

u

0 0

Responses

  • mewael
    Do you need dim in vba 2007 declare vars?
    7 years ago
  • robert
    How to make variables in VBA lose their value?
    7 years ago

Post a comment