Microsoft Office Tutorials and References
In Depth Information
Variables, Data Types, and Constants
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 don’t 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.
To ensure that the Option Explicit statement is inserted automatically whenever
you insert a new VBA module, enable the Require Variable Declaration option in the
Editor tab of the VBE Options dialog box (choose Tools➜Options). I highly recommend
doing so. Be aware, however, that this option doesn’t affect existing modules.
A variable’s scope determines in which modules and procedures you can use the variable. Table
8-2 lists the three ways in which a variable can be scoped.
Table 8-2: Variable Scope
How a Variable with This Scope Is Declared
Include a Dim or Static statement within the procedure.
Include a Dim or Private statement before the first procedure in a module.
Include a Public statement before the first procedure in a module.
I discuss each scope further in the following sections.
A local variable is a variable declared within a procedure. You can use local variables only in the
procedure in which they’re declared. When the procedure ends, the variable no longer exists, and
Excel frees up the memory that the variable used. 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
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, not just arrays.