Declaring Variables In VBA Code
This page describes why you should always declare your variables in VBA code.
When you are writing code, VBA allows you to create variables "on the fly"
simply by using a name. When a new name is encountered as your code runs, VBA
will create a new variable with that name and assign it the specified value.
While this may seem like a nice feature, it is actually an invitation for bugs
in your code. The reason is that a simple misspelling of a variable name will
cause a new variable to be created, which may cause your code not to work at all
or work in a manner that is very difficult to debug.
The solution to all this is to put Option Explicit as
the very first line in the code module, before and outside of any procedures.
This tells the compiler that all variables must be declared with the
Dim statement (the word "Dim" goes all the way back to
the earliest roots of BASIC and is short for "Dimension"). With
Option Explicit in effect, any misspelled or otherwise
incorrect variable name will be caught by the compiler, which will raise a
"Variable Not Defined" error. You can configure the VBA editor to always
include the Option Explicit statement in all newly created modules from the
Options dialog. Check the "Require Variable Declaration" option as shown below.
Note that Option Explicit applies only to the code
module that contains it -- it does not apply to the whole project. Every code
module must have its own Option Explicit declaration.
Explicitly declaring variables will prevent coding errors like the following:
Dim VariableName As Long
VaraibleName = 123
Here, the second line has a misspelled variable name. It is easy to see when the
two lines are sequential, but much more difficult to see if they are separated
by 100 lines of code. With Option Explicit in effect,
the compiler would catch the error immediately.
A second reason for declaring your variables is efficiency in execution. When an
undeclared variable is created at run time, it is created as a Variant type. A
Variant can hold any type of data -- numeric, text, doubles, objects, etc. This
flexibility comes at a price. There is considerable overhead behind the scenes
to make Variants work, such as testing the data type of the value stored in the
variable.
In summary, you should always use Option Explicit
and declare your variables.
This page last updated:
10-July-2007