Microsoft Office Tutorials and References
In Depth Information
Variables, Data Types, and Constants
Thanks to VBA, the data type conversion of undeclared variables is automatic. This process may
seem like an easy way out, but remember that you sacrifice speed and memory — and you run
the risk of errors that you may not even know about.
Declaring each variable in a procedure before you use it is an excellent habit. Declaring a variable
tells VBA its name and data type. Declaring variables provides two main benefits:
h Your programs run faster and use memory more efficiently. The default data type,
Variant , causes VBA to repeatedly perform time-consuming checks and reserve more
memory than necessary. If VBA knows the data type, it doesn’t have to investigate, and it
can reserve just enough memory to store the data.
h You avoid problems involving misspelled variable names. This benefit assumes that you
use Option Explicit to force yourself to declare all variables (see the next section).
Say that you use an undeclared variable named CurrentRate . At some point in your
routine, however, you insert the statement CurentRate = .075 . This misspelled
variable name, which is very difficult to spot, will likely cause your routine to give incorrect
Forcing yourself to declare all variables
To force yourself to declare all the variables that you use, include the following as the first
instruction in your VBA module:
When this statement is present, VBA won’t even execute a procedure if it contains an undeclared
variable name. VBA issues the error message shown in Figure 8-1, and you must declare the
variable before you can proceed.
Figure 8-1: VBA’s way of telling you that your procedure contains an undeclared variable.