Written by Rod Wing.

Declare all your variables

VBA does not require you to declare any variables, but it is highly recommended that you do so. Variables that are not declared are set to the Variant type.  Do not use the Variant data type unless absolutely necessary. Using all Variant types can cause many different problems in your code including:

• Attempting to pass variables by reference to another Sub, Function, etc. that require explicitly declared data types.
• Interpreting a value incorrectly
  • VBA has to make it’s best guess and sometimes it guesses incorrectly
• Decrease the overall performance of your application

How do you stop this?  Require variable declaration in all of your VBA modules.  From the VBA menu bar select Tools -> Options to display the following dialog box.

Select the “Require Variable Declaration” checkbox and click on the OK button. When that option is selected every new form, module, and class created will start with the Option Explicit statement. To enforce variable declaration in existing modules you will need to manually place an Option Explicit statement at the top before any procedures.

Another insidious error that can occur when you don’t use Option Explicit is mis-typing variable names. Look at the common variable name iCount as an example. For those of us who are not expert typists this can easily be mis-typed as iCoumt, iCout, or some other variation. Without requiring variable declaration VBA will allow all of these variations to pass through compilation. Trying to find this kind error can be very time consuming and frustrating. By using the Option Explicit statement in every code module VBA will catch this type of error automatically every time.

Using the Dim statement correctly

Now that you are required to declare all of your variables make sure you do it correctly. General VBA coding guidelines promote the one variable per line standard as follows:

Dim X As Double

Dim Y As Double

Dim Z As Double

This promotes legibility and decreases the likelihood of mis-declaration. Compare the above method with the following uses of the Dim statement for declaring multiple variables on the same line:

Dim X, Y, Z As Double

Dim X As Double, Y As Double, Z As Double

Contrary to what you might think, these are not the same.  In the first line only the variable Z is declared As Double.  The X, Y variables are declared As Variant. The second line correctly declares all three variables As Double.

Last Month’s Tips: