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

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 and 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 * 20 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.

By the way, 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

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 of VBA data types. (Data types not listed don't have type-declaration characters.)

Data Type Type-Declaration














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

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.

Was this article helpful?

0 0

Post a comment