Interrupting Calculations

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.

Posted in Uncategorized

2 thoughts on “Interrupting Calculations

  1. You’ve indirectly touched on the number one item on my Excel wishlist – the location of the “Calculate” message. This is one of the most significant messages that Excel has to give you, yet the information that any formula results displayed are potentially incorrect is frequently hidden under the trivial and long-winded “Select destination and press ENTER or chose Paste”.

    Given that, IIRC, this goes back to at least least XL97, I’ve never been able to understand how it survived so long.

    Regards,
    Brian.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *