How to disable Excel 2010 feature/bug which stops calculation on selection of any cell?

Question

I am working on an excel sheet in which, with the help of an xll addin, I am trying to update the data. I press Ctrl+Alt+F9 to start the calculations, but the calculation stops at any keypress or cell selection. Is there a way to override this feature, or bug?

This does not happen on Excel 2003.

Answer 1

Very good question!

Not sure will this work in your C# addin, but with Excel 2010 Application object model, you can use Application.CalculationInterruptKey to stop interruption on data calculation when a key is pressed. Not tested but this can be it.

Apply this at beginning of CtrlAltF9:

Dim lKey As Long
lKey = Application.CalculationInterruptKey
Application.CalculationInterruptKey = xlNoKey

Then at end of calculation, reset it to what it was or change it to default xlAnyKey.

Application.CalculationInterruptKey = lKey ' Or xlAnyKey

If you have other event triggered Subs, you may want to add lines about Application.CalculationState such that it won't make changes until Application.CalculationState = xlDone.

Hope this helps.