Reliable Excel VBA Code: Best Practices

This post is a companion to Excel VBA Code – Best Practices to Boost Software Quality. It is a fuller version of the guidelines for writing reliable Excel VBA code.

“The price of reliability is the pursuit of the utmost simplicity. It is a price which the very rich may find hard to pay.”

C.A.R. Hoare

Add Option Explicit atop Modules

By default, VBA considers all undeclared variables to be variants. Meaning they consume more memory and run slower than variables of other types.

This applies to declared variable names typed incorrectly. And these errors are especially difficult to find during debugging.

To avoid these problems, always place an option explicit statement at the top of your modules. This statement should be outside any of your procedures.

Option Explicit enforces variable declaration. A compilation error occurs if undeclared variables arise. So, by fixing these errors at compile-time, you prevent runtime errors.

Minimize Use of Variant Variables

The flexibility you get from variants is hardly worth the accompanying reliability concerns. Variants don’t validate the data passed to them. And, VBA automatically converts them into other data types.

Your procedures fail if the input data is of a different type than expected. And, the converted data type may not fit your specific needs, so unexpected results arise.

A better approach is to think about the kind of data the variable will handle (i.e. it’s nature and value range). Then declare them as a specific type rather than as variants.

Pick Enumerations over Magic Numbers

Some parameter values will not change during execution (e.g. specific weekday or month in a year). The urge is to denote these using constants of arbitrary value (e.g. Monday’s value will be 1, Tuesday’s 2, etc.). But, you reduce coding assignment errors by using Enumerations instead.

The image below exemplifies this. By using arbitrary-value constants (a.k.a. magic numbers) errors are more likely. More so, considering lengthy procedures may be unavoidable sometimes. You can also glimpse the impact on the ease of reading (readability) in this example.

Assign Default Values to Variables

When you declare new variables in VBA, they get default values assigned to them. As you can expect, it is often better for the programmer to control this process to avoid surprises.

You do this by immediately assigning default values to declared variables. So you seize control and ensure things are on the up. And, make sure the assigned values fit the variable’s purpose.

Validate Procedure Arguments

Procedures (subroutines and functions) usually need some input data, passed using arguments. These data have to be of a specific data type, matching the arguments housing them.

It is a good idea to always test the validity of these data relative to the procedure’s expectations. Place these tests above any code that uses the data, in the body of the procedure.

The benefits of these coding practices with regards to reliability are self-evident. They need no further elaboration.

Careful Use of ByVal and ByRef

By default, VBA passes arguments to subroutines and functions ByRef. So, the reference (pointer) to the original variable outside the procedure is passed. And changes to the variable in the procedure affect it everywhere else.

But, passing variables ByVal transfers only a copy of the original variable to the procedure. So, changes to that copy affect it only inside the procedure.

Keep this distinction, and VBA’s default behaviour, in mind when coding procedures.

Compile VBA Projects before Runtime

This catches all syntax and compilation errors before runtime. Make sure to repeatedly do this until the option is greyed out (see below graphic). Note that error handlers can’t help you with these kinds of errors.

Add Error Handlers to Procedures

Inexperienced developers routinely omit Error handlers in their Excel applications. This usually leads to those confusing (and annoying) runtime error messages users contend with. Check out Paul Kelly’s post which delves into the nitty-gritty of Excel VBA error handling.

Avoid using On Error Resume Next as it skips the code line the error occurred on, without fixing the error. Use On Error GoTo instead.

You should always fix the error and return to either:

• The line that caused it (using the Resume command), or
• The line right after that (using the Resume Next command).

If you can’t fix the error, undo all the actions done by the error-causing procedure. And, exit the procedure to avoid the error’s cascading effects in other areas of your code.

Use Proper Error-Trapping Settings

VBA’s error-trapping settings determine when code breaks to display error messages. While coding you will want to Break on All Errors (both handled or unhandled), so you don’t miss a beat.

But, in the application’s final version, it should be set to Break on Unhandled Errors. Why? so only errors not dealt with by the handlers trigger breaks and error messages at runtime.

Some users may have changed this setting in their Excel application. And, you can’t adjust it through code (except in Microsoft Access VBA). So, you should add instructions (e.g., in User Guide) so users can adjust it to the proper setting.

Qualify Object/Property References

When referencing objects on many workbooks or worksheets, always qualify their full references. Otherwise, you rely on VBA’s rules which may lead to incorrect or unexpected results.

This applies to object default properties too. Fully qualifying the default property values helps to avoid errors if default property changes in future versions of Excel VBA.

Careful use of Exit For or Exit Do

To boost efficiency, it is handy to use conditional code blocks in loops to exit mid-loop. So, instead of running the entire loop regardless of the status of its aim, the code exits once the loop’s goal is met.

But, exercise caution when you do this to avoid skipping any relevant code lines. Remember, the code must be correct and error-free before being optimized for speed.

Sometimes it is not possible to add the exit-condition to your loop control statement (i.e., first or last line). In such a scenario, consider adding critical code lines to the conditional code block, above the Exit For/Do statement of course.