Using Variables (Properly) In VBA
Visual Basic For Applications (VBA) is extremely flexible in the way it allows you, the programmer, to use and declare variables. This flexibility makes the language quite easy to learn and use. However, it also makes it very easy to make mistakes, and it encourages you to develop bad habits. This page describes some "do's and don'ts" of declaring variables, both simple types like Integers and Strings, and object types like Ranges and Worksheets.
Do Always Explicitly Declare Your Variables
VBA does not require you to explicitly declare your variables. If you don't declare a variable using the Dim statement, VBA will automatically declare the variable for you the first time you access the variable. While this may seem like a nice feature, it has two major drawbacks -- it doesn't ensure that you've spelled a variable name correctly, and it declares new variables as Variants, which are slow (see below). For example, consider the following code:
If X = 40 Then
You can force VBA to require explicit declaration be placing the statement Option Explicit at the very top of your code module, above any procedure declaration. With this statement in place, you would receive a Compiler Error - Variable Not Defined message when you attempt to run the code, and this makes it clear that you have a problem. You can fix the problem immediately, rather than later (when an angry user calls!).
The best thing to do is tell the VBA Editor to include this statement in every new module. From the Tools menu, choose Options, and then choose the Editor tab. Check the Require Variable Declaration checkbox.
Do Always Give Your Variables Explicit Data
When you declare a variable, you may give it a specific data type, such as String or Range or Double. However, you are not required to do this. If you don't VBA will declare it as a Variant type. For example, the following statements are equivalent.
A variant data type can hold any type of data. Again, this may seem useful, but it has disadvantages. The largest disadvantage is performance. At run time, VBA must execute good amount of code each time you access the variable to determine what sort of data already exists in the variable, and what sort of data you're setting the variable to. Also, it will execute code to convert from one type to another when you are calling other procedures.
In a large application with lots of variables, this can add up to a great deal of overhead. There are some instances in which you will want to use a variant, such as when using the Split function. If you need a variant data type, explicitly declare it as such. This will make the code easier to interpret and debug in the future.
NOTE: When you declare more than one variable on a single line, each variable must be given its own type declaration. The declaration for one variable does not affect the type of any other variable. For example, the declaration
Dim X, Y, Z As Single
It is the same as
or, more explicitly, as
Dim X As Variant
This is a common mistake among novice programmers. For clarity, I always declare each variable on a separate line of code, each with an explicit data type. Yes, it requires more typing, but I don't bill my clients for the number of keys I type. I bill them for good quality code. Some programmers disagree with this approach, but I feel it ensures very readable and clear code.
Unfortunately, there is no option in the VBA Editor to force you to do this. It would be nice if there were an Option Type Explicit declaration. Alas, MS doesn't take my recommendations to heart. It is a matter of self-discipline to give variables data types. It is a habit you won't regret.
Don't Use Object Type Variables
Just as a Variant data type can store any type of variable, such as a String or a Double, the Object data type can store any type object, such as a Range or a Worksheet. (NOTE: A variant may also store an object type variable.) The performance implications of using the Object data type are more serious that those of the Variant data type (and if you store an Object in a Variant, you compound the problem further). You should always declare your object variables as specific types (this is called early binding). For example, use
Dim MyRange As Range
If you use the Object type, VBA must determine at run time what properties and methods are supported by that object. This process is called late binding, and can be vastly slower (up to 200 times slower!!) than early binding.
For the programmer, using an explicit object type also allows VBA to display the Intellisense popup, which lists all the available properties and methods, making it easier to type in code (and spell things correctly).
Don't Use The New Keyword In A Dim
Dim MyCollection As New Collection ' auto-instancing
Dim MyCollection As Collection
You may be tempted to save yourself from some typing and use auto-instancing variables. However, this causes VBA to execute additional code at run time. Contrary to intuition and popular belief, when you use auto-instancing, the object is not created by the Dim statement. It is created at run time the first time your code uses the variable. To do this, VBA compiles some hidden code immediately before each line of your code that references the variable. For example, if your code is
VBA will actually compile the following.
If MyCollection Is Nothing Then
In other words, the New keyword acts more like a compiler directive rather than an "action" word. It doesn't really create the variable; it merely tells the compiler to write the code to create it as needed.
It will do this every time your code uses the variable. In a large application, this checking can create a good deal of unnecessary code. Moreover, your code can never test to determine whether the object is in fact empty, depriving you of a very useful debugging and diagnostic tool. If an object is Nothing, it may mean that something went wrong in your code, and some specific action needs to be taken. For example, your code may need to load a collection with some specific values. To ensure that this gets done, a procedure may execute code like the following:
If MyCollection Is Nothing Then
However, if you used the New keyword in the Dim statement, VBA will compile the following code, and the object will never be empty.
If MyCollection Is Nothing Then
In this code, your test of MyCollection Is Nothing will always be false!
VBA's flexibility may seem useful, but it can also lead to inefficient and hard-to-maintain code. Just because VBA allows you do certain things doesn't mean that you should do them. You should develop good coding habits early, rather than trying to break bad habits later. You'll be glad you did.
See also Declaring Variables In VBA for additional information.