Overview

When automating Excel operations with VBA, there are many ‘tricks’ which can be implemented to significantly improve performance. As the automations run in a non-interactive fashion, features that primarily exist for the benefit of an end user can be temporarily disabled resulting in performance gains. The following Excel settings fall into this category:

Excel Application Properties

• Calculation
• EnableEvents
• ScreenUpdating
• EnableAnimations
• DisplayStatusBar

Calculation

One of the best features of a spreadsheet is its magical ability to instantly update data contained in the worksheet cells. While these real-time updates are useful when users are interactively working in the Excel document, this feature robs performance and isn’t necessary when executing automation processes. By changing the Application.Calculation property, we can toggle the calculation logic between Automatic, Manual, and SemiAutomatic.

Toggling between Manual and Automatic during your VBA code execution will typically result in significant performance gains.

Figure 1 – Disable Automatic Calculations in Excel

Figure 2 – Enable Automatic Calculations

To illustrate the potential performance difference, I created a simple VBA routine to generate a 22×22 grid of numbers. Formulas are contained on the bottom of the sheet which sum up the numbers in the grid. The numbers on the grid are updated multiple times based on a user supplied value. When this grid is generated with / without automatic calculations enabled / disabled, there is a significant performance impact. *

Figure 3 – Calculation Manual vs Automatic

As the size of the grid/number of calculations required increases, the performance will continue to decline. Temporarily disabling the calculation method will significantly boost performance on sheets that make use of formulas. (e.g. just about every spreadsheet on the planet)

*IMPORTANT NOTE – Performance metrics are going to vary on a machine by machine basis depending on the system’s hardware. As a general rule, these settings will almost always improve performance. The amount of improvement will be inversely proportional to the machine’s hardware. (e.g. the slower the machine, the larger the improvement) Even if the improvement isn’t the greatest on your machine, you should still consider implementing these steps if other users will work with the excel files.

EnableEvents

In Excel, events can trigger actions and/or programming logic to be executed. For instance, when you change a value in a cell, a “changed” event triggers. Events are one of the ways in which Excel “automagically” knows to update formula based cells when the underlying data is altered. Disabling events is just as easy as adjusting the calculation mode:

Figure 4 – Disable / Enable Excel Events

Using the same 22×22 grid of numbers from the previous example, testing was performed contrasting the difference between enabling / disabling events. Disabling events resulted in a 50% improvement in processing time on a modern quad core i7 machine!

ScreenUpdating

The ScreenUpdating setting in Excel is relatively self-explanatory. When this is enabled, the worksheet display area will be refreshed after any cell values are altered. When users are directly interfacing with Excel, this absolutely needs to be enabled; however, when executing scripts, this is not needed. Updating this setting is also straight forward:

Figure 5 – Disable / Enable ScreenUpdating

Using the same 22×22 grid of numbers from the previous example, testing was performed contrasting the difference between enabling / disabling screen updating. Disabling ScreenUpdating resulted in a modest 10% improvement in processing time on a modern quad core i7 machine with a high end graphics card.

EnableAnimations / DisplayStatusBar

The final two items, EnableAnimations and DisplayStatusBar, typically do not result in significant gains; however, they can help if you need to squeeze every possible ounce of performance out of your Excel/VBA.

EnableAnimations is another user interface related performance setting. This setting only disables certain visual elements as opposed to all screen updates. (e.g. user interface animations for certain actions) Typically, this setting will not make a significant impact; however, it is better than nothing in situations where you cannot leverage the ScreenUpdating setting. Like many of the previously discussed settings, this is another On/Off (True/False) setting:

Figure 6 – Disabled / Enable Application Animations

The DisplayStatusBar is yet another user interface setting that may result in minimal gains. Unless your Excel application relies heavily on the status bar, it is not likely this is worth the effort. This is also an On / Off (True / False) setting:

Figure 7 – Disable / Enable Status Bar

 

Additional Tips / Suggestions

When leveraging these settings, keep the following in mind:

• Cumulative Effects –The performance settings can be combined for cumulative improvements! In the previously discussed grid test, enabling all performance settings results in an amazing improvement. On a modern quad core i7 computer, the test went from 4 seconds to less than 1 second!
• Global Scope – Be cognizant that these settings are global settings and will persist across workbooks, instances of Excel, and will even persist after you close and restart Excel! Be sure to consider this when designing your solutions.
• Restoring original settings – While these settings are great for performance, they are not great for end users! If your code fails to restore the original values, end users will be negatively impacted! (End users and IT teams alike may remove you from their Christmas mailing lists!)

As some of the settings, such as Calculation State, are more than an On/Off setting, it is recommended that you read the current setting and store it in a variable to ensure that you can correctly reset the application setting to its original state.

• Code Robustness – To ensure that the settings are consistently reset, it is imperative that you follow good coding practice and account for errors/exceptions in your code. If you do not implement proper error handling in your code, an end user will eventually run into an unhandled exception resulting in the code ending in an incomplete state. (e.g. your VBA code tries to delete a file and encounters an error, such as ‘permission denied’) The ‘On Error’ statement should be utilized in your code to provide a way for your code to handle errors gracefully.

The attached Excel workbook contains sample code as well as the Sample Grid test which has been referenced in this write-up:

Figure 8 – Excel Performance Demo Application

http://www.charlescbeyer.com/SampleCode/2_PerformanceOptimizations.xlsm