Is Your Excel VBA Running SLOW? Here are 6 Tips to Improve Your Excel VBA Performance

A prospect asked me once if I can improve his VBA code performance. After 30 minutes analysis of his code, I committed to improve performance in 95% * How can your Excel VBA code run faster, much faster?

Last week I offered some advice on improving the performance of your Excel Workbook. This week, we’ll improve your VBA code!

A story of frustration with slow VBA

Most of the VBA code out there is a result of advanced Excel users who learned some VBA statements. They know how to reference Worksheet cells, how to run a loop and maybe manage multiple Workbooks and Worksheets.

They learned looping over a range of cells in an online YouTube example over a range of 50 cells, and they now believe that this is how “VBA” updates cells with data. When they run a loop over 400,000 cells – they are frustrated and ask: “why is my VBA slow?”.

How is it that huge performance gaps are observed with VBA?

Isn’t it amazing that the same tool, VBA, can produce a 5-seconds processing time by one programmer, and 5-minutes processing time by another programmer – for the same task? They both “know” VBA, don’t they?

I believe this can be explained by the way people perceive VBA and how they were taught VBA.

VBA is a blessing for its ability to unleash endless automation solutions in the hands of almost every advanced Excel user.

However, VBA can turn into a source of frustration for most of them, as they are not taught how to be computer programmers.

People miss out on the fact that VBA is a computer programming language, and not a scripting add-on to move data about Excel Worksheets.

As a programming language, one needs to be a computer programmer in order to write efficient code. This means understanding data structures, data types, data storage, functional programming (for functional languages such as VBA), code design, error handling, and other aspects of computer programming.

Unfortunately, most Excel VBA courses out there were planned and delivered by people who became very good in VBA, but never really learned, and gained experience, as computer programmers.

In my mind, this is the number one reason for the huge performance gap you can achieve with VBA in two different implementations, for the same task.

With that in mind, let’s examine my “top 6” for writing efficient, performant VBA code.

5 tips for writing faster VBA code

1. Learn functional computer programming

If you are writing VBA code on a regular basis – this would be the best investment of your time!

At the very least, understand data storage, structures and structural design of a program.

This is the holy grail of writing good, efficient code in any computer programming language and it will serve you in many other ways in designing and implementing large computer programs, not only performance-wise.

2. Turn off unneeded resource-consuming processes

As your VBA code is executed, other processes are kicking in and responding to changes your code makes. If you’re updating Worksheet cells – these may trigger a Workbook calculation upon every cell update, and also re-painting of the screen for the user (including other cells, formatting and charts). Worksheet events may also fire and trigger a cascade of processes you did not mean to run unless the user manually changed something.

There are some application-level flags you may want to turn off while you are running your code. These include:

ScreenUpdating = False

EnableEvents = False

Calculation = xlCalculationManual

If you want your own event handlers (such as in Worksheet_Change()) to be skipped as well, you may want to maintain your own flag and write a condition querying that flag before your events handlers’ code if called for execution.

You must, of course, remember to restore the application state to the user as you received it. I elaborate on that and demonstrate the use of these application-level flags in this Blog post: Excel VBA: Restoring the State of the Application Before Returning Control to the User.

3. Process data in-memory and not directly over Worksheet cells

Interaction with the Workbook (such as when updating Worksheet cells) is one of the more expensive operations in terms of processing time. It is unfortunate that updating Worksheet cells is one of the more popular operations most people need to do.

That’s why you should opt to upload your data into an efficient data structure that is designed for efficient in-memory processing, and run all of your updating there. Once ready – you flush the complete data buffer to your Worksheet cells in one shot.

This is so powerful and important, that I dedicated a shocking example in this Blog post: The HUGE performance difference: Worksheet cells vs. Arrays.

Besides arrays, other data structures for consideration include Collections, user-defined Data Types and Enumerations.

4. Leverage built-in Excel functions

In your VBA programs, you may often need to perform an operation for which Excel has already produced a function for you to use out-of-the-box. Some typical examples that come to mind include: looking up a value in a table, sorting and filtering.

In addition, by smartly combining multiple Worksheet functions, you can compute complex results on your data directly in a Worksheet cell and reference that cell in your VBA code when needed.

The beauty and power of Excel is the ability to combine several features and capabilities together, and this a good example.

One way is to call for Excel Worksheet functions directly in your VBA code (as opposed to inside a Worksheet cell). Here’s an example of calling the Worksheet MAX() function in VBA, to find the highest of three numbers (may be used to two or more numbers):

WorksheetFunction.Max(lngBase, lngInterest, lngPremium)

I did find that calling Worksheet functions that way sometimes is less reliable, therefore the way I usually go about this is by calculating my results in a hidden helper Worksheet, and reference these cells for their value in my VBA code.

For example, if I’m looking up a value in a table using INDEX() + MATCH() functions (or you can use the newer XLOOKUP() function), I’d write this function in a cell. I’d assign a name to that cell, for instance: Customer_Age. Then I will fetch the customer’s age in my VBA code like that:

ThisWorkbook.Names(“Customer_Age”).RefersToRange.Value

Keep in mind that as soon as you update any cell from your VBA program, all dependent cells are calculated in the Workbook BEFORE VBA continue to process the next statement (assuming your calculation mode is not set to be manual). The calculation state of the Workbook is resolved before control moves back to your VBA code flow; therefore, you can rely on this “dance” between your VBA program and Excel Worksheet.

5. Query your tables with SQL

Querying table(s) data in many slices and dices is a popular requirement. Excel does offer few great tools for analyzing data (such as Pivot Table, Power Query), but these do not intuitively address the need to query data in your VBA program.

You can always define a Pivot Table in a hidden Worksheet and query its values from your VBA program, but this has some drawbacks and may not fit your business requirements.

Here I’d recommend using an extremely fast tables-querying engine available for us in VBA: The Jet (or ACE) Database engine used in MS-Access.

You can construct standard SQL queries over your Excel tables (or ranges) as if they were tables in a relational database – including aggregates and joins.

If this is appealing to you, I’d recommend you read my Blog post: The Power of SQL Applied to Excel Tables for Fast Results in which I explain and offer some VBA code that demonstrates how this is done.

6. Use plain TEXT files when appropriate

Our data is usually arranged in Worksheets’ cells. This is great both as a data store as well as for user interaction and presentation.

But in some cases, you need to just store and process a large amount of data, and you naturally allocate a Worksheet for that.

If this data need not be seen, or updates, by the user – consider storing it as a flat text file (or CSV file) instead!

A flat text file is very lite, carrying no fancy formatting attributes and therefore highly performant. In addition, there are optimized mechanisms in Windows for highly efficient processing of text files, another reason to consider them for storage.

I have not yet written a Blog post on working with text files, but in my online course: Business Automation with VBA: Working with Files, I explain and work through in detail how to import and export data in two different ways using VBA.

7. Re-design your model to work with a Relational Database Management System

Relational databases are a whole world in itself, and definitely not just a “VBA Trick”, but if your Excel-based solution is data intensive, with several connected large tables – you may want to consider this major shift.

If you’re new to Databases, you may find some guidance in this Blog post: Understanding Relational Databases: The Basics.

If you found this post useful, help me and your colleagues by sharing!