Browse Month

July 2018

Maximizing Excel / VBA Automation Performance


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


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.


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!


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

Resolving Frozen HFM Applications


While Hyperion Financial Management (HFM) applications do not regularly suffer from technical issues, there are certain situations that can cause your application to become unresponsive. This scenario usually is caused by poorly designed or corrupted HFM member list or calculation rules which run in a loop. (e.g. self-referencing member lists) When this happens, the application becomes frozen and even a restart of the servers/application will fail to solve the issue since the rules are executed again on startup.

One way to resolve this issue is to revert to a backup; however, this could result in the loss of artifacts and/or data. Fortunately, there is a better way to solve this problem that will minimize downtime and prevent the loss of information!

The information provided below will cover key details and complete recovery steps using the HFM COMMA application as an example.

HFM Database Concepts (Rule File Storage)

While the HFM relational database contains a plethora of tables, one table (<appname>_BINARYFILES) contains the key to this solution. HFM Rules, Metadata, Member Lists, and other Application Settings are all stored in this table. (see my other post about altering HFM Starting years via this table for more information!) When you use the HFM user interface to import Rules / Member lists / Metadata to the application, the information is placed in the appropriate instance of the table. (e.g. COMMA_BINARYFILES) The LABEL column indicates what type of information is being stored (AppSettings, CalcRules, MemberListRules, SharedAppData, etc.) while the BINARYFILE column contains the data.

Since the BINARYFILE column can only contain 2,000 bytes of data, you will find multiple rows of for a given type. (e.g. Rules files will typically have numerous rows since rule files are typically much larger than 2,000 bytes) When the data needs to be used in the application, it is read in ascending order based on the ENTRYIDX column.


While the BINARYFILE data does not appear to be readable, it is simply the ASCII values stored as a string as opposed to the actual human readable characters. The beginning of the 2nd row has been decoded for reference:


Resolving HFM App Hang Up

While the HFM database table ‘history lesson’ was fun, how exactly does this solve the problem? Since the HFM application is looking for specific LABEL values for Rules / Member Lists, the easiest way to resolve the issue is to update the LABEL for the relevant rows so that HFM will no longer “see” them as rules / member lists. This will effectively give HFM a clear slate and it will startup as expected. (Before doing anything with the application you should re-load a good set of rules / members though!)

IMPORTANT – You should not make these changes while the application is running! Be sure to stop the application first!

The following steps should be followed to fully recover the application:

  1. ​Stop HFM Services
  2. Connect to the database and execute a query to change the Label value to a dummy value
    • Member Lists – UPDATE <APPNAME>_BINARYFILES SET LABEL = ‘BadMemberListRules’ WHERE LABEL = ‘MemberListRules’
  3. Restart HFM Services
  4. Connect to Application
  5. Load new Member List or HFM Rules
    • NOTE – If this is an EPMA application or you are using Calculation Manager, be sure to correct the rule issue and redeploy the application