Declaring and Scoping Variables

DECLARING AND SCOPING VARIABLES

Variables that are not declared to have any of the built-in date types are treated as the default variant type.

Data stored as a variant type acts like a chameleon: it changes type depending on what you do with it.

For example, if a variable of type variant contains a text string that looks like a number (such as "123"), you can use this variable for string manipulation as well as numeric calculations. VBA automatically handles the conversions.

This may seem like an easy way out - but remember that you sacrifice speed and memory.

Therefore to you use a variable in a procedure, it is a good practice to declare your variables.

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 contains any undeclared variables.

You need to use Option Explicit only once. But this statement affect only the module in which it resides.

Supposed you use an undeclared variable (that is, a variant) named CurrentRate. AT some point in your routine, you have the following statement:

CurentRate = 0.075

This misspelled variable, which is difficult to spot, may cause your routine to produce incorrect results. However if you had used the Option Explicit statement, then such a mistaken would have been detected.

To ensure that the Option Explicit statement is inserted automatically, turn on the Require Variable Definition option in the Editor tab of the Options dialog box.

The scope determines which modules and procedures can use a given variable. This is important since a workbook can have any number of VBA modules and a VBA module can have any number of Sub and Function procedures.

The scope of a variable depends on the way it is declared. The following table describes the scope in detail.

Scope of a Variable
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

Each of these cases is discussed below.

Procedure-only variables

The lowest level of scope for a variable is at the procedure level. Variable declared with this scope can be used only in the procedure in which they are declared.

When the procedure ends, the variable no longer exist.

If you execute the procedure again, the variable comes back to life, but it previous value is lost.

The most common way to declare a procedure-only variable is with a Dim statement placed within the procedure.

Typical Dim statements are placed immediately after the Sub or Function statement and before the procedure's first line of code.

If a variable is declared with procedure-only scope, other procedure in the same module can use the same variable name, but each instance of the variable is unique to its only procedure.

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

Sub MySub( )
    Dim Age As Integer
    Dim StudentID As Long
    Dim Height As Single
    Dim BirthDate As Date
    Dim Name As String
    Dim UniqueFeatures
'   ... The procedure's code goes here ...
End Sub

Variables having the same data type cannot be declared as a group by separating them with commas.

In this declaration

Dim i, j, k As Integer

k is declared to be an integer, however i and j are declared to be variants.

If you want to declare all of them to be integers, they have to be declared separately like:

Dim i As Integer, j As Integer, k As Integer
Dim ID As Long, LastName As String

Module-only variables

For a variable to be available to all procedures in a module, it must be declared before the module's first Sub or Function statement - outside any procedures. This is done in the declaration section, at the beginning of your module.

The variable can then be used from any 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 declaration section) by using the Public keyword.

For example:

Public CurrentRate As Long

Then CurrentRate is available to any procedure in the workbook - even those in other VBA modules.

Static variables

Normally, when a procedure ends all the variables are reset. Static variables are a special case in that they retain their value even when the procedure ends.

Static variables are declared at the procedure level.

A static variable may be useful if you need to keep track the number of times you execute a procedure. You can declare a static variable and increment it each time you run the procedure.

In the following example

Sub MySub( )
    Static Counter As Integer
    Dim Msg As String
    Counter = Counter + 1
    Msg = "Number of executions: " & Counter
    MsgBox Msg
End Sub

Even though the value of a variable declared as Static is retained after the end of the procedure, that variable is unavailable to other procedures.

In the above example, the Counter variable and it value are available only within the MySub procedure. In other words, it is a procedure-level variable.