Normally, Excel calculates your workbooks so fast you don’t notice. Sometimes, however, your workbook is so big that it takes Excel some time to calculate it. I’m guessing readers of this blog run into that situation more than the average Joe. While Excel is calculating, the status bar displays its progress. If you continue to work during the calculation, Excel will pause its calculation so you can work at a normal speed. Nice feature, I think.
Excel in mid-calculation
Make a keystroke and it goes back to ready, but shows that more calc’ing is needed
I guess Microsoft is assuming that if you doing other things, you’re not really looking at the results. If you’re not looking at the results, it can wait until you’re done to finish calculating. For humans, I think that’s a fine assumption. For VBA code, not so much. Your code may be continuing on happily without knowing that the workbook isn’t fully calculated.
The Excel object model provides the Application.CalculationInterruptKey property to control how/if calculation can be interrupted. xlAnyKey is the default and it pauses whenever the user starts working. xlEscKey only pauses when the escape key is pressed. It’s used when you want to allow the user to interrupt calculation, but you want to make sure they do it explicitly and not by accident. xlNoKey prevents interruption. It seems that any time you calculate in code, you should set this property to xlNoKey, then set it back. Are there times it wouldn’t matter?
Thanks to Bill Manville for “discovering” the CalculationInterruptKey property.