Top tips for Excel/VBA programming


The points mentioned below are those that we have found to be the most important to consider when building Excel/VBA applications.
Of course they are subjective, and may not be applicable to your application.

Use Option Explicit

VBA allows you to use variables without pre-declaring then with a Dim statement.
This means that the interpreter will automatically assign a type according to the context in which the variable is being used.
One obvious downside of this is that operations that are not compatible with the assigned type will only become apparent at run-time.
While coding, the type-sensitive features of the VBE will not be available to assist your coding.
Option Explicit means that you must pre-declare your variables and assign a specific type to them.

Avoid using Variants

The Dim variable As Variant declaration allows the variable to have any type.
While this provides a lot of flexibility, it can lead to run time errors caused by type-incompatible operations.
It is better to explictly state the type of a variable, and allow the compiler to flag any issues.
You also benefit from the type-sensitive features of the VBE.

Use the error handler

By including an error handler in your methods, you have the opportunity to handle run-time errors gracefully.
Switching it off means that you may be processing in an uncertain state after a run-time error occurs.

Use the macro recorder sparingly

The macro recorder is a useful tool as an aide-memoire, particularly if you are automating spreadsheet manipulation.
However, the code it leaves behind is very literal, and often contains more operations than you really need.
Due to its literal nature, any cell selections will have explicit range addresses...

Avoid referencing explicit range addresses

Referencing an explict range in code, such as Set aRange = aWorksheet.Range("C3:G6") is problematic.
By inserting rows and/or columns into the sheet of interest, the content is shifted.
Therefore the next time the code is executed, the contents of aRange will be different.