Page 76 - Excel Progamming Weekend Crash Course

d540629 ch04.qxd 9/2/03 9:28 AM Page 51

Session 4 — Syntax and Data in VBA 51

Variable Declaration and Option Explicit

One of VBA’s weaknesses is that it lets programmers get away without declar-
ing variables. In fact, this is its default behavior — just use variables in code
without worrying about declarations or Dim statements. This sounds pretty
easy, so why do I call it a weakness?

Working without variable declarations is an invitation to errors and bugs. The
reason is because if you misspell a variable name, VBA just treats the misspelling
as a new variable name. This can lead to all sorts of problems. In addition, if a
variable is not declared, it is automatically created as type Variant, which is a
real hog when it comes to processor time and memory resources.
If variable declaration is required, VBA automatically catches a misspelled
variable as “undeclared,” preventing the kind of errors mentioned above. Also,
because you must explicitly declare each variable, you can use the data type
that is best suited to the data.
To require variable declaration, include the Option Explicit statement in
each module, at the beginning before any procedures. You can also turn this
setting on for all modules (highly recommended!) by selecting Tools ➪ Options
to display the Options dialog box; then, on the Editor tab, select the Require
Variable Declaration option.

Numeric Variables

VBA’s numeric types fall into two categories:
Integers. Numbers with no fractional part. For example, -5, 1, and 1,234.
Floating point. Numbers with a fractional part. For example, 1.01 and -0.06.

Within each of these two categories, there are three different types that differ in the
range of values they can hold, and, for the floating-point types, their accuracy (or preci-
sion). These are summarized in Table 4-1. When declaring a variable, choosing between an
integer type and a floating-point type is usually easy, and you should use an integer type
whenever possible because they take less memory and are faster in calculations — although,
to be honest, with today’s computers the difference is not likely to be noticeable. Within
each category, you must select a specific type based on the nature of the data that the vari-
able will hold. Note that the Currency type is specialized for holding money values.

Table 4-1 VBA’s Numeric Data Types

Name Type Range Precision
Byte Integer 0 to 255 N/A
Integer Integer -32,768 to 32,767 N/A

Continued