Best Practices for Excel VBA Code

There are over a billion Microsoft Office users, including around 750 million Excel users. So it is not surprising that CEO, Satya Nadella, sees Excel as Microsoft’s prime consumer product. Excel VBA (Visual Basic for Applications) significantly boosts Excel’s capabilities. However, when misused, VBA can introduce a lot of problems in your Excel applications.

In this post, I summarise guidelines for writing high-quality Excel VBA code. The article derives from my personal experience and a host of credible sources.

“The art of programming is the art of organizing complexity, of mastering multitude and avoiding its bastard chaos as effectively as possible.”

Edsger Dijkstra

What Is High-Quality Excel VBA Code?

The Software Quality Triangle (SQT) is often used to describe software quality. The backbone of high-quality software is a high-quality underlying code. So, the same attributes stated in the SQT also apply to code quality.

Not all these attributes derive from Excel VBA code though. Excel VBA is a hosted language (Excel being the host), so some of these attributes derive from Excel itself. For instance:

• Usability (ease of use) arises from both the application and Excel’s user interface (UI) and user experience (UX) design;
• Portability (multi-platform use) depends on operating systems supporting Excel or VBA;
• Testability (ease of testing) stems from software architecture rather than construction (coding).

So, we can say that a high-quality Excel VBA code is one that:

• Does what was agreed or intended (no more, no less!) – correctness;
• Runs with no ‘unhandled‘ errors/bugs – reliability;
• Does more with less (time/memory) – efficiency;
• Doesn’t break things in the applications it interacts with – integrity;
• Eases reading and error finding/fixing – maintainability;
• Eases and quickens editing or changing – flexibility;
• Is usable in different contexts or for different purposes – reusability;
• Blends complementary technologies – interoperability.

“A week of coding can often save an hour of thought.”

Josh Bloch

Is High-Quality Excel VBA Code Worth the Effort?

There are obvious benefits to writing high-quality Excel VBA code, here are a few:

• Lower application maintenance costs;
• Improved procedures or macro performance and stability;
• Increased end-user satisfaction;
• Adding new features is easier, quicker, and cheaper;
• Improved lead-times on other projects (reusable code).

“Programmers spend the first 5 years of their career mastering complexity, and the rest of their lives learning simplicity.”

Buzz Andersen

Excel VBA Best Practices : Guidelines Or Dogma?

Coding best practices are consensus guidelines known to improve Excel VBA application quality. But, they are not holy-writ, and individual developers can pick those to adopt.

This article summarises these guidelines. The format is willfully concise, but you can access detailed posts on some of these guidelines by clicking on the headings.

“Correctness is clearly the prime quality. If a system does not do what it is supposed to do, then everything else about it matters little.”

Bertrand Meyer

Best Practices for Correct Excel VBA Code

The road to code correctness begins with a review of the application requirements. These requirements capture stakeholders’ agreement on what the application should do.

Requirements are the basis for ranking correctness. The following guidelines help to improve your Excel VBA code’s correctness:

• The application should not do more than required, neither should it do less;
• Test the application thoroughly, as per the requirements;
• If possible, get your code reviewed by your peers;
• Adopt the best practices for Excel VBA coding.

“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

Best Practices for Reliable Excel VBA Code

Include the Option Explicit Statement in Modules

Add Option Explicit atop modules, so undeclared variables filter out at compilation time. Otherwise, they execute as variant variables which may lead to unpredictable results.

Assign Initial Values to Variables upon their Declaration

To prevent unexpected results, assign default values to variables immediately after declaring them. It is essential that the values you assign fit the variables’ purpose.

Use Variant Variables Rarely or Not At All

Avoid using Variant variables. They don’t validate assigned data types and are internally converted to other data types by VBA. By using specific variable types, you are more in control.

Validate an Argument’s Data Type

When coding procedures, always add code that validates the arguments’ data type. This prevents incorrect argument data type errors.

Employ Enums wherever possible

Use Enumerations to reduce variable assignment errors, whenever appropriate.

Pass Arguments into Procedures Properly

Note the contrasts between the ByVal and ByRef keywords. This is critical when coding procedures (subroutines and functions).

Compile VBA Projects before Running them

Always compile VBA projects before running. This catches syntax and compilation errors before runtime.

Include Error-Handlers in Procedures

Always add error handlers to procedures. This prevents users from getting confusing runtime error messages.

Fully Qualify Object or Object-Members

Always qualify object references and default properties fully. Otherwise, you rely on VBA internal rules which may lead to unexpected results.

Use Caution when exiting Loops

Use Exit For and Exit Do carefully. Specifically, always ensure the execution of all relevant lines of code before mid-loop exits.

Break Execution on All Errors while Coding

Set Excel’s error-trapping setting to Break on All Errors. This allows you to catch both handled and unhandled errors while coding.

Bind Object-Variables Late

Always bind object variables at runtime (i.e. late binding). This prevents your code from crashing in the event that an early-bound object’s COM (Component Object Model) reference is not found on a user’s computer. Note that there are efficiency trade-offs to consider.

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

Martin Golding

Best Practices for Maintainable Excel VBA Code

Name Variables Consistently and Descriptively

Adopt a consistent and descriptive naming convention. This makes your code easier to understand in a few months or years (or if someone else reads it right now).

Include Header Blocks in Modules and Procedures

Add header blocks to major, lengthy, or complex code blocks. Header blocks summarise the essential information about the code block below it. This makes your code easier to understand.

Write Comments Properly and Use them Judiciously

Use both in-line (i.e., full line) and end-of-line comments, but appropriately. Comment on the ‘why’ and not the ‘how’ of a code line or block.

Indent and Space Code Consistently

Adopt a consistent indentation and spacing style. This will boost your Excel VBA code’s readability to humans.

Don’t Exit Complex Loops Prematurely

Avoid mid-loop exits in heavily nested loops. As you can imagine, logic comprehension problems may arise for code readers.

Adopt a Code-Writing format that is Easy on the Eye

Avoid lengthy code lines or blocks. People find thin-column content easy to read, think of public media like magazines and newspapers. Adopt this format in your code.

Don’t Use the GoTo Statement

Avoid the GoTo statement. Branching between code lines may be handy at times (e.g. error handling), but it makes reading tougher.

Don’t Repeat Yourself

Follow the Don’t Repeat Yourself (DRY) principle. Editing or altering your code is easier when you only have to do it in one place.

Keep Procedures Simple and Single-Task Oriented

Write de-coupled procedures. When procedures tackle a single task, editing or modifying them doesn’t impact code outside them. So, the code is more maintainable and flexible.

Declare Variables with Fixed-Values Appropriately

Avoid using magic numbers, constants, or strings for variables with fixed values. The first two hinder readability, while strings eat up memory and run slow. Instead, use Enumerations.

“There is nothing so useless as doing efficiently that which should not be done at all.”

Peter F. Drucker

Best Practices for Efficient Excel VBA Code

Disable Resource Consuming Features

Disable resource-consuming features like Screen Updating, Display Status Bar, Automatic Calculation, Enable Events, Display Page Breaks, and Display Alerts.

Speed Up Garbage Collection & Avoid Memory Leaks

Speed up garbage collection and prevent memory leaks by setting all unused objects’ references to Nothing.

Avoid the SELECT and ACTIVATE Object Methods

Avoid the Select and Activate object methods. Instead, explicitly reference the object property or method of interest.

“Efficiency is intelligent laziness.”

David Dunham

Avoid Repetitive Exchange of Data between Code and Excel

Exchange chunks of data between VBA code & Excel in one-shot. This is much faster than cell-by-cell transfer.

Avoid Repeated Qualification of Object References

Use the With statement to store qualified object references for use in with-blocks. This avoids object reference requalification.

Use the Set statement to pass qualified object references to variables. This avoids reference requalification.

Avoid Loops when Searching in Ranges & Arrays

Avoid loops when searching in ranges and arrays. Excel’s Range methods and worksheet functions help in this regard.

Avoid Loops when Copying Data between Worksheet Ranges

Avoid loops when copying data between worksheet ranges. Instead, assign the destination range to the source range directly.

“Simplicity is the soul of efficiency.”

Austin Freeman

Avoid Loops when Sorting Data in Ranges or Arrays

Avoid loops when sorting data in ranges or arrays. Instead, use the Range object’s Sort method.

If You Must Use Loops, Use Smart Ones

Ensure loops are smart. Refer to Guidelines for Efficient Excel VBA Code for more details.

Pick Byte or Long Variables over Integer

Declare whole numeric data type variables as Long rather than Integer. This speeds up execution as VBA does not first convert them into Long type before use.

If a variable holds only whole numbers between 0 and 255, then declare it as Byte. This takes up less memory and boosts run speed.

Eliminate or Minimise the Use of Variants

Avoid using Variants as they are bulky (memory-wise) and run slow.

Bind Application-level Objects Early

Bind application-level objects early (at compile time). Compared to binding objects at runtime (i.e. late binding), this improves execution speed. Note that there are reliability trade-offs to consider relative to the magnitude of speed increase.

Use ‘Index’ instead of ‘Name’ to Reference Collection Items

Use ‘Index’ instead of ‘Name’ to reference Collection items for improved execution speed.

Instead of Evaluating Strings use Enumerations

Use Enumerations instead of strings. Strings are bulky (memory-wise) and run slow.

Use vbNullString instead of “”

Use vbNullString instead of an empty string, “”. vbNullString is a VB constant set to zero bytes and representing a zero-length string. It executes slightly faster than an empty string.

“Write code that isn’t a bull in a china shop. It shouldn’t break things in the applications it interacts with.”

Olusola B. Oguntuberu

Best Practices for Excel VBA Code Integrity

Besides Microsoft Office applications, VBA also runs on other applications. These include ArcGIS, AutoCAD, CorelDraw, LibreOffice, Reflection, SolidWorks, MicroStation, and WordPerfect.

Your Excel VBA code shouldn’t be a bull in a china shop. It shouldn’t break things in the applications it interacts with, including Excel itself.

Your Excel VBA code should leave applications in the same state in which it found them. How you do this depends on the particular application.

Best Practices for Interoperable Excel VBA Code

You can interact with VBA-enabled applications from your Excel VBA code. You can also use .NET libraries written in any of the .NET programming languages (C# or VB). These are also known as Dynamic Linked Libraries (DLL).

This is an excellent interoperability feature of VBA that you should take advantage of. For example, why write code to create drawings from Excel shapes when you can interop with AutoCAD to do so.

Check out posts by Pragmateek, John Franco, and GeeksEngine to get started on VBA and .NET Library interoperability.