Efficient 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 efficient Excel VBA code. It focuses on boosting execution speed and system memory performance of Excel Applications.

“Simplicity is the soul of efficiency.” – Austin Freeman

“Efficiency is intelligent laziness.” – David Dunham

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

Disable Resource Consuming Features

Set Screen Updating to FALSE, so Excel doesn’t redraw the screen each time your code sends data to a worksheet. Exception: If your code must display a change on the worksheet or update an animation/chart while running.

Set Display Status Bar to FALSE. This stops Excel showing the execution status of the code. In so doing it reduces the rate of sending data to worksheets. Exception: If the code must update users on execution progress (without using a progress bar) or update the screen while running.

Set Automatic Calculation to xlCalculationManual. This stops Excel from recalculating dependent cells in your worksheet(s). That occurs each time your code makes changes to cells linked to those cells. If needed, you can recalculate a specific worksheet or cell range via code.

Set Enable Events to FALSE. This stops Excel’s event listeners running extra code. That occurs each time code makes changes in the worksheet(s) that fires follow-on events.

Set Display Page Breaks to FALSE. This stops Excel from recalculating page breaks. That occurs when code modifies rows, columns, or the PageSetup object’s properties.

Set Display Alerts to FALSE. This stops Excel displaying system alerts dialog boxes. That occurs whenever code issues specific commands, dialog boxes such as, ‘Do you want to save before closing?’.

Set Print Communication to FALSE. This stops Excel from communicating with the printer when code manipulates the PageSetup object.

Ensure that each of these features is reset to the users’ original settings after the code runs. This is an essential step along the road to efficient Excel VBA code.

I wrote the following module to implement these guidelines on my projects. The extra bit of coding in the ‘Display Page Break’ segment allows it to work on all worksheets in the workbook.

Speed Up Garbage Collection & Avoid Memory Leaks

Declaring objects in VBA (e.g., Dim lObj_A As Range) causes the creation of an instance of the object’s class (e.g., Range class). The created instance consumes system memory. A reference (or pointer) is also created for referring to that location in memory.

The instance and the reference/pointer share the name chosen in declaring the object. References for several objects can be set to a single object if they are of the same class (e.g., Set lObj_B = lObj_A: Set lObj_C = lObj_A).

When all pointers to an object are de-referenced (i.e., Set lObj_B = Nothing: Set lObj_C = Nothing: Set lObj_A = Nothing). It will do so until Excel VBA’s garbage collector (GC) destroys it and releases the memory used up.

Make every effort to assist the internal VBA garbage collection process. To that end, keep the following in mind:

• The GC will only destroy an object when it can’t count any references for it.
• Setting the object’s references to Nothing doesn’t destroy the object or free memory. Rather, it only speeds up the GC’s reference counting.
• Regardless if all an object’s references were set to Nothing, it is destroyed once it goes out of scope. For example, when a procedure ends for local or procedure-level objects).
• For objects of public scope, all references in the module in which it was declared must be set to Nothing. This is necessary for the GC to destroy it and free up memory. This applies to objects of global scope – all pointers in the VBA project must be set to Nothing.
• Avoid circular references when writing new classes. They fool the GC into thinking references are still assigned. Hence, they prevent clean-up leading to objects that are never destroyed – memory leaks.

Jens G. Balchen and Bruce McPherson delve into the nitty-gritty of VBA’s garbage collection, check them out.

The take away here is that you should always set unused object’s references to Nothing while being scope-aware. This is crucial to ensuring efficient Excel VBA code.

Avoid the SELECT and ACTIVATE Object Methods

Directly reference the object property/method that interests you instead. This is particularly crucial within loops.

One-shot Exchange of Data Chunks between Code & Excel

Compared to cell-by-cell transfer to/from an array or range, this dramatically improves code efficiency by doing it in just one operation that doesn’t involve looping.

This post by Charles Pearson details this using the Range object’s Resize property and Transpose method.

Avoid Repeated Qualification of Object References

Use the With statement so Excel stores fully qualified object references for use in forthcoming code lines. This way references don’t have to be qualified repeatedly.

Use the Set statement so Excel stores fully qualified object references in declared variables of identical type. In forthcoming code lines, rather than requalifying object references repeatedly, they are accessed directly from those simple variables.

In the sample code below, I illustrate the impact using the With and Set statements. Coding time and resource efficiency improve by a lot, the very meaning of efficient Excel VBA code. In case it isn’t clear the sample code creates a chart and formats it.

Avoid Loops when Searching in Ranges & Arrays

Excel’s built-in Range methods and worksheet functions can help narrow the search field (check out the AutoFilter and SpecialCells methods) and pick out desired values faster than loops could (check out the Find method and VLookup, HLookup, Match functions).

Avoid Loops when Copying Data between Worksheet Ranges

Rather than transferring each data between worksheet ranges one cell at a time using loops, directly assign the destination range to the source range (keep sizes the same) for faster execution.

Avoid Loops when Sorting Data in Ranges or Arrays

The fastest way to sort data in Excel is to use the Range object’s Sort method. Writing a sort routine will involve loops which eat up programming time. Also, it will likely be slower than using this built-in sort method.

Charles Pearson’s post gives a simple version of code to sort one-dimensional arrays using the sort method.

If You Must Use Loops, Use Smart Ones

There are several ways to extract every last nanosecond of speed from loops. I recommend spending a few minutes on this post.

Here are a few things to keep in mind that result in efficient Excel VBA code:

• Thoroughly review and optimize your loops, especially inner loops.
• Add conditional blocks within loops to exit mid-loop. Do this instead of running through the entire loop regardless if you have achieved what you want.
• If you exit mid-loop, make sure that every code line that must run before leaving the loop runs. Do this by adding these lines to conditional code exit blocks. Correctness trumps efficiency every time!
• When looping through collections, use For-Each loops instead of index-based For loops. Execution speed improves since the collection’s properties are not re-evaluated.
• If a variable’s value does not change inside a loop, then declare it as a constant. Else, it will be evaluated each time a line of code uses it (e.g., Pi = 3.14).

Pick Byte or Long Variables over Integer

Declaring whole numeric variables as Long rather than Integer (slightly) speeds up the code. This is because VBA doesn’t need first to convert them into Long type before use.

If the variable doesn’t hold whole numbers less than ‘0’ or greater than ‘255’, declare it as Byte. This takes up less memory and boosts speed as Byte doesn’t need conversion to Long before use.

Eliminate or Minimise the Use of Variants

The flexibility obtained when using variants isn’t free (surprised?). Variants require 16 to 22 bytes of memory depending on whether they contain numbers or text. This is excluding the memory taken up by the string passed to the variant.

Try to declare variables as a specific data type instead. And add the Option Explicit statement atop your modules. This forces variable declaration while coding. It also prevents VBA from considering undeclared variables (even typos) as variants.

This guideline is crucial to ensure efficient Excel VBA code due to the memory implications of the excess use of variants.

Early Binding of Application-level Objects

If an object represents Microsoft Word documents, declare it as such (Early Binding). Don’t declare it generically and then set it to the specific application-level object type later (Late Binding).

There is significant time-saving in binding variables early (at compile time) compared to doing it late (at runtime).

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

The index-approach, e.g., WorkSheets(1), for picking items in collections is faster than the name-approach, e.g., WorkSheets(“Sheet1”). Use the index approach whenever possible.

But, take care when items in a collection are rearranged, removed, or new items added. This is because the index-approach relies on the order of items in the collection.

Instead of Evaluating Strings use Enumerations

Strings are bulky (memory-wise) and run slower than other data types. Replace them with faster running data types when possible. Enums or Enumerations are great for this purpose.

Consider that you’re building a profile generator, and your code will perform different tasks based on users’ sex. The sample code below illustrates how Enums can help boost performance.

Use vbNullString instead of “”

vbNullString is a VB constant set to zero bytes that represents a string of zero-length. So, it executes slightly faster than an empty string (“”) which still takes up 10 bytes of memory.