Excel VBA coding standard: Always use explicitly declared variable types

Standard

1. Always use "option explicit" at the top of your modules to force variable types to be declared.
2. Never use the Variant type.  It is slow, not memory efficient and can hide errors, which creates potentially nasty bugs.

Rationale

VBA for Excel supports the Variant variable type and by default will not insist that variable types are declared.  Whilst this makes basic coding easier, it can easily lead to very nasty bugs and slow/inefficient code, as we will now explore.

Code speed can often become a problem with complex VBA macros.  If a variable type is not declared it becomes a Variant.  Every time a piece of code containing that variable is run VBA needs to figure out what type to make the variable before doing anything with it, which is slow.  This gets worse if the variable is used for several different purposes, as VBA will need to keep converting the variable between types.

VBA also needs to reserve memory for the variable, for which it has to assume the worst-case.  By telling VBA which variable type to use it will reserve the right amount of memory.

Nasty bugs can arise as a result of failing to declare variables, as the VBA compiler is limited in the amount it can do to help you.  Coding errors can easily be hidden from you, which will lead to difficult-to-find bugs.  See the bottom of this post for an example.

Using "option explicit" at the top of a module tells VBA to enforce variable type declaration.  This means that if you try to use a variable without stating which type it is, VBA will give a compile error "Variable not defined" and refuse to run the macro.

Good
Option Explicit

Sub AGoodMacro()
    Dim count As Long
    Dim fraction As Double
   
    For count = 1 To 10
        fraction = 1 / count
    Next count
End Sub

Bad
Sub ABadMacro()
    For count = 1 To 10
        fraction = 1 / count
    Next count
End Sub

Can you spot the bug?
Sub ABuggyMacro()
    For count = 1 To 10
        fraction = 1 / cout
    Next count
End Sub

Yes, that's right: the variable "count" in spelt incorrectly on one of the lines ("cout").  VBA will not warn of this as it is perfectly valid while "option explicit" is not declared.  When VBA encounters this line of code the behaviour will be incorrect.  Imagine a much more complex macro and you can see how this might result in a very time-consuming bug.