Maintainable Excel VBA Code: Best Practices

This post is a companion to Best Practices for Excel VBA Code. It is a fuller version of the guidelines for writing maintainable Excel VBA code. It also touches on aspects of Excel VBA code flexibility and reusability.

“Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.”

Martin Golding

Adopt a Consistent & Descriptive Naming Convention

It is common to see Excel VBA code with object names such as Chart1, Sheet1, ObjectA. Imagine how difficult it will be to read through such code in a few months or years.

To avoid that and ensure maintainable Excel VBA code, here are a few things you might want to adopt while naming ‘stuff’ in your projects:

• Variable names should show the purpose, scope, and data type.
• Name constants like variables, but capitalize the name part.
• Name arrays like variables, but pluralize the name part and state the upper and lower bounds.

• Name procedures using a [datatype]VerbNoun[Adjective] format. Functions will need a datatype prefix to make clear their return value type.
• Name procedure arguments with datatypeVariableName_[IN][OUT][INOUT] format. The suffix prevents coding errors by clearly identifying whether the argument can:
  • Only be referenced by other variables (IN, i.e., right of ‘=’);
  • Itself only reference other variables (OUT, i.e., left of ‘=’);
  • Do both (INOUT).

• Change default UserForm controls’ names before writing any code for them. The control type should be clear from the name.
• Change default worksheets and workbooks’ names. Use more meaningful names, considering their purpose.

Add Header Blocks to Major Code Blocks

Summarising critical data about significant modules, subroutines, and functions boost code readability and ensure maintainable Excel VBA code. This is not necessary for simple procedures.

It takes a lot of discipline. But you won’t get stung by the ‘had I known’ bug a few months or years down the line.

I use the following header block on my projects, adapt it as required.

Add Proper (Non-trivial) Comments

Use both in-line (i.e., no code on the comment line) and end-of-line (placed at the end of code lines) comments. But do so sparingly, you shouldn’t aim to rewrite the code in English.

Don’t comment on code that is clear by itself. Most code readers will be competent VBA programmers, so there’s no need to explain the code. Comment on the ‘why’ and not the ‘how’.

• Add in-line comments to state what you’re about to do, what you have done so far, the reasoning behind decisions taken, any assumptions or need-to-knows.
• Add end-of-line comments to state the ‘why’ of a line of code.

Adopt a Consistent Indentation & Spacing Style

Indenting and spacing your code makes no difference to computers. But it is essential to boosting human readability of the code and ensuring maintainable Excel VBA code.

You can indent using the space or tab keys. The default tab indent is four spaces, but you can change that in the Visual Basic Editor (VBE) as shown below. I agree with the commonly recommended indentation of three spaces.

The visual basic editor (VBE) automatically puts white space before and after operators (+, -, =, etc.). Separate large code blocks from other code by inserting a blank line between them.

Indenting is excellent, but it doesn’t work well with heavily nested code blocks. The code block’s logic becomes harder to understand as nesting levels increase.

The code sample below illustrates proper code indentation and spacing.

‘if not obvious, comment on what below block does and why it does it (not how!)

Careful Use of Exit For/Do in Nested Loops

Mid-loop exits boost efficiency, but code correctness and maintainability should always trump efficiency. In heavily nested loops, mid-loop exits usually lead to logic comprehension issues.

Avoid Lengthy Code Lines or Blocks

Public media adopts the thin-column format which is more natural for humans to read. It is good practice to keep code lines less than 120 characters.

You can do this by breaking long code lines into multiple lines, using the line continuation character, “_”.

There is no consensus limit on the vertical length of procedures. But more than 500 lines would be pushing it.

Avoid Using the GoTo Statement

Unconditionally branching from one code line to another may be handy at times, but the impact on readability is usually negative.

Multiple GoTo statements make it harder to read, maintain and change your code, the exception being code for error handlers (On Error GoTo statement).

Don’t Repeat Yourself (DRY)

If you find that you need to repeat a code block, devise a procedure for it instead. Eliminating code clones makes it easier to maintain or change your code.

Keep a definitive version of the procedure, so if you need to make changes or add more functionality, you will only need to do so in one place.

The DRY principle improves code reusability. You scope the procedures correctly, then use the Call statement to implement them wherever and whenever required in your code.

Write Good-Neighbour (De-Coupled) Procedures

Good neighbours help when needed but mind their own business otherwise (at least mine do). Your procedures should imitate this and focus on a single task.

If your procedure strays into doing more than its single primary job, you probably need a new one for that stray task.

When your procedures tackle only one problem, you can edit, change, or add to them without crashing other parts of your code.

Use Enumerations instead of Unchanging-Value Variables

Avoid ‘magic numbers’, constants, and strings for variables with unchanging values. The first two impede readability while strings evaluate slowly.

Instead, use meaningful enumerations to ensure maintainable Excel VBA code. The code sample below illustrates this.

“Indeed, the ratio of time spent reading versus writing is well over 10 to 1. We are constantly reading old code as part of the effort to write new code. …[Therefore,] making it easy to read makes it easier to write.”

Robert C. Martin