Writing Clean VBA Code

After some recent projects that consisted of fixing other people's code I thought it would be worth making some notes about good practices when writing VBA code.

1. Declare variables.

Add the Option Explicit directive to the start of each module and force yourself to declare every variable used. This can be written as the top line of a module or specified in the Tools->Options dialogue in the VBA Editor.

When an undeclared variable is used it causes a compiler error. If there is one single reason for doing this it is to catch spelling mistakes. Without the Option Explicit directive a misspelt variable will simply take a value of zero and the code continues to run.

Such a bug can be very difficult to trace.

2. Declare variables with the correct type.

This is particularly important when using objects because the editor will prompt you for available methods and properties as you type. Each variable must be declared on a separate line.

The following is wrong because the first variable will be a Variant type, not a Worksheet.

This is correct:

3. Do not assume a workbook is current or that a worksheet is active.

If a cell or a range is referenced without specifying the sheet, it is assumed to be in the active sheet. If a sheet is referenced without specifying the workbook, it is assumed to be in the active workbook. Most of the time this works, but it can lead to strange behaviour that is difficult to trace.

Use ThisWorkbook to specify the workbook containing the macro code and name the sheet within it, or use the index number:

If the sheet is going to be referenced throughout a subroutine, declare it as a Worksheeet object:

4. A cell rarely needs to be selected from VBA code.

Macros produced by the macro recorder will retrace the steps taken during the recording, and that means selecting cells. When writing code from scratch the cells do not need to be activated and selected. Simply write to the range or the cell required.

5. Indent your code.

Programme code can be made much more readable by indenting with the tab character rather than running all the text down the left hand margin. For example:

This makes it much easier to follow the logic of the code and match the start and end of a loop. The number of spaces in a tab can be set in the Tools->Options dialogue.

6. Comment your code.

Adding comment text to your code can be very helpful if you, or anybody else, needs to understand it at a later date. Extra line breaks can be added to break the code up and make it easier to read.