Excel workbook slow at calculation and also calculating for no apparent reason

Question

Initial context

I am working with a large Excel file (Excel 2010) that models some enterprise data. The unfilled workbook is about 3-4MB in size. The workbook consists of multiple sheets with some complex dependencies. And some of the formula in the sheets are also complex.

The workbook also relies on UDF, event handlers and other code. Key event handlers that are relevant to this problem are:

• Worksheet_Change
• Workbook_SheetChange
• Worksheet_SelectionChange
• Workbook_SheetSelectionChange

(please excuse me if I have mis-typed the names of any of these, but you know the ones I mean)

This workbook has been updated recently by myself and a colleague to add functionality, increase robustness (reduce bugs and hidden gotchas) and also improve performance where possible.

The workbook has been checked and all volatile functions have been removed/changed. In addition, none of the UDF are volatile.

As this is a work product, I am unable to share the workbook. In addition, due to the complexity, I doubt if I could ever create a reproducible example.

The problem

When changes are made to values in cells, the application automatically calculates, and the message in the status bar shows the progress of the calculation. Calculation can take up to 20 seconds when a single book is open and if multiple books are open can take minutes.

However, the workbook triggers the calculation on at least two unusual occasions:

• When resizing a column (not double-clicking, but single click and resize)
• When change a static value in a cell in a worksheet that has no conditional formatting and no code-behind.

These triggers are unexpected and create significant time delays.

What we have already checked and eliminated

• Unused cells with formatting
• Volatile UDF or Volatile formula
• Conditional formatting (other than that which is intended)
• The actions of Workbook_SheetChange and Workbook_SheetSelectionChange. These have conditions in them that are bypassed for the actions we tested, so they are not performing any substantial code (including bypassing any changes to calculation mode). In addition, we also tested commenting out all this code and the calculation delays still occurred.
• The impact of turning calculation mode to manual and back again
• The use of Excel 2016 in a different environment.
• Whether the calculation delays are caused by macros. This has been totally eliminated, any delays caused by macros are known and expected.

Where we make changes to the workbook through macros, the changes happen quickly without the extended calculation. A TimeSaver class is used which amends ScreenUpdating, EnableEvents and CalculationMode. However, one of our tests eliminated this as a factor.

Relevant websites currently looking at, but almost all of the suggestions have already been actioned:

• https://trumpexcel.com/suffering-from-slow-excel-spreadsheets/
• https://docs.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-improving-calcuation-performance
• https://smallbusiness.chron.com/make-excel-calculate-faster-32149.html
• https://professor-excel.com/15-ways-to-speed-up-excel/
• https://www.pcworld.com/article/3222067/microsoft-excel-why-your-spreadsheet-is-so-slow.html
• https://www.mrexcel.com/forum/excel-questions/607372-excel-calculating-way-too-long.html
• http://www.decisionmodels.com/optspeedb.htm
• Working on this Excel workbook causes Excel to freeze for several seconds. Why?

The question

What is triggering the unexpected re-calculations which are spoiling the user experience?