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
   MyValue = "OK"
End If

If MyValeu = "OK" Then
    '
    ' do something important here
    '
End If

As you can see, the second use of the variable  MyValue is misspelled.  Here, VBA will create a new variable named MyValeu and give it a value of an empty string ""  (actually it gives it a value of  Empty, and the next time the variable is used it will be either 0 or an empty string, depending on the context).  Of course, it will never be equal to "OK" and therefore the code inside the IF  statement will never execute.  In long and complicated procedures, this can be very difficult to debug, mostly because your eyes will fool you.  You know that MyValeu is supposed to be MyValue, and that is what you'll see. 

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 Types
(And Don't Use Variants)

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.

Dim MyVar
Dim MyVar As Variant

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

is not the same as declaration

Dim X As Single, Y As Single, Z As Single

It is the same as 

Dim X As Variant, Y As Variant, Z As Single

or, more explicitly, as 

Dim X As Variant
Dim Y As Variant
Dim Z As Single

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. 

Dim MyRange As Range

instead of 

Dim MyRange As Object

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 Statement

Some objects can (or must) be declared as new instances.  For example, a Collection object variable must be created as a new collection unless you are going to set it an existing object. VBA allows you to do this in two ways:  putting the New keyword in the Dim statement (called auto-instancing), or in the Set statement.  For example, you can use either of the following pieces of code, and the code will compile properly. 

Dim MyCollection As New Collection ' auto-instancing

 or 

Dim MyCollection As Collection
Set MyCollection = New 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

Debug.Print MyCollection.Count

VBA will actually compile the following. 

If MyCollection Is Nothing Then
   Set MyCollection = New Collection
End If
Debug.Print MyCollection.Count

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
   RunSetupProcedures
End If

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
   Set MyCollection = New Collection
End If
If MyCollection Is Nothing Then
   RunSetupProcedures
End If

In this code, your test of MyCollection Is Nothing  will always be false! 

See also Declaring Variables In VBA for additional information.