Introduction:

Nearly all non-trivial VBA code involves declaring variables. While VBA allows a great deal of flexiblity in declaring your variables, this flexiblity can easily lead to poor coding practices. This page describes practices you should embrace regarding variable declaration. While adopting these practices can lead to more typing, following them makes code run faster, and makes the code easier to debug and maintain.

Use Option Explicit:

By default, VBA doesn't require that you declare your variables using the Dim statement. If the compiler encounters a name that it doesn't recognize as an existing variable, one of the VBA reserved words, or a property or method of a referenced typelib, it will create a new variable by that name. While this may seem convenient, it can lead to bugs in the code that are difficult to find (although once found they are simple to fix). Suppose you declare a variable with the name Index1 and later misspell that name as Idnex1, the compiler will not flag that as an error. Instead, it will create a new variable named Idnex1 and initialize it to an empty string, a value of 0, or a Nothing object reference, depending on the context in which it is used. This means that the (correct) variable Index1 will not contain the expected value. If there is a large amount of code between the initial declaration of the Index1 variable and the point at which it was misspelled as Idnex1, it will be hard to track down just what is wrong. This is only made worse by the mindset when reading code. Your brain "knows" that the proper name is Index1 and you might read right over Idnex1 without noticing the error.

You can prevent this type of mistake by requiring that all variables be declared with a Dim statement. As the first line of code in the module, above and before any other lines, use:

Option Explicit

This statement requires that all variables be declared using a Dim statement. Returning to the problem described above, the compiler will throw an error when it encounters the misspelled Idnex1 variable, alerting you to the problem. The code will not execute at all until the error is fixed.

You can set an option in the VBA Editor that will automatically add an Option Explicit directive in all newly created code modules (but not retroactively to existing code modules -- this must be done manually). In the VBA Editor, go to the Tools menu, choose Options and then select the Editor tab, shown below. There, check the Require Variable Declaration option.
ForceDeclaration     Setting the Require Variable Declaration option automatically inserts Option Explicit in new code modules.

Avoid Using The Variant Data Type:

Most of the time, you should declare your variables with specific data types, such as String, Long, or Double. VBA supports the Variant data type that can hold any type of data. If you omit the As Type clause in a variable declaration, Variant is the default type. While this may seem useful, it increases processing time when encountered in code because behind the scenes, the compiler has added no small amount of code to test what type of data is actually stored in the variable. Moreover, using a Variant can mask possible Type Mismatch errors that should be caught during testing. Instead of using a Variant type, declare the variable with a specific data type.

This is not to say that Variant types are always undesirable. The can and do serve a purpose. As an example, consider the return type of the Application.GetSaveAsFilename method. If the user cancels out of the dialog, the method returns a Boolean type with a value of False. If the MultiSelect parameter is False, the method returns a String. If the MultiSelect parameter is True, the method returns an array of String variables. By returning a Variant whose type can be tested, the GetSaveAsFilename can be quite flexible, as shown in the following code:

Dim V As Variant
Dim N As Long

V = Application.GetOpenFilename(MultiSelect:=True)
If IsArray(V) = True Then
    For N = LBound(V) To UBound(V)
        Debug.Print "Files selected: (" & CStr(N) & ")"
        Debug.Print "File: " & CStr(N), V(N)
    Next N
Else
    If V = False Then
        Debug.Print "No file name selected."
    Else
        Debug.Print "One file selected: " & V
    End If
End If

Don't Use Auto-Instancing Object Variables:

For object type variables, it is possible to include the New keyword in the Dim statement. Doing so create what is called an auto-instancing variable. Again, while this may seem convenient, it should be avoided. Contrary to what some programmers may believe, the object isn't created when the variable declaration is processed. Instead, the object is created when it is first encountered in the code. This means that, first, you have limited control when an object is created. Second, it means that you cannot test whether an object is Nothing, a common test within code and a common testing and diagnostic technique. If the compiler's output were in VBA code, the code to handle auto-instancing variables would look like the following:


Pay Attention To Variables Declared With One Dim Statement

VBA allows declaring more than one variable with a single Dim statement. I don't like this for stylistic reasons, but others do prefer it. However, it is important to remember how variables will be typed. Consider the following code:

Dim J, K, L As Long

You may think that all three variables are declared as Long types. This is not the case. Only L is typed as a Long. The variables J and K are typed as Variant. This declaration is functionally equivalent to the following:

Dim J As Variant, K As Variant, L As Long

You should use the As Type modifier for each variable declared with the Dim statement:

Dim J As Long, K As Long, L As Long