Sheet EnableCalculation Working Intermitently

Question

I am working on a spreadsheet for my company and I am having trouble with the EnableCalculation function. We copy large chunks of data and paste them into a workbook. To expedite this process, I 'freeze' sheets by turning the EnableCalculation to False as shown in the example.

Disabled

Without this, Excel slows to a crawl while background calculations are done as the data is being pasted.

In the macro that does the calculation for this workbook once all the data has been pasted, I re-enable the calculation by setting EnableCalculation to True.

Re-Enabled

The issue that I have found, however, is that sometimes the sheets calculate properly but other times they do not with seemingly no rhyme or reason. It is extremely frustrating for it to be so unpredictable. Is there a way to ensure that the sheets calculate when they are supposed to? Thanks,

Answer 1

Yes there is a way to ensure sheets calculate when they should do: Refactor the workbook so that you avoid the bottlenecks that forced you to put it into manual calc mode in the first place. Then switch calc mode back to Automatic and leave it there! Manual calc mode is just too dangerous, and almost always completely avoidable.

Common reasons why spreadsheets get so slow that users have to switch to manual calc mode include:

• Volatile formulas (OFFSET, INDIRECT, TODAY etc) with large calculation chains hanging off them. See my blog post here. I've slashed recalc time from minutes to seconds just by addressing this in isolation.
• Overuse of SUMPRODUCT for cases that can be handled by SUMIFS or PivotTables. See my answer here.
• Resource-intensive lookups done on unsorted data. See my post here. Using a binary match on sorted data can reduce recalc time by minutes.

Charles Williams has a great article on finding and optimizing bottle necks here.