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.

Just to clarify... you're saying that on Excel 2003, pressing a key didn't interrupt calc? Because I think it did. — Chris Rae, Jan 04 '14 at 06:11
How's it going to did you get a chance to try any of these solutions? — Jeremy Thompson, Jan 08 '14 at 22:38

score 2 · Answer 1 · answered Jan 03 '14 at 03:46

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.

score 0 · Answer 2 · answered Dec 26 '13 at 16:32

Why do you need to select a cell during calculation - Is there a reason you can't wait until it is done? Selecting/changing cells during calculation is dangerous as the data might not be correctly calculated yet.

If you are just worried about accidental key presses - this should prevent any accidental key presses

Sub SetUpUserInterface()

Dim Current As Worksheet

    For Each Current In Worksheets
        Current.Protect , UserInterfaceOnly:=True
        'Current.Protect Password:="", UserInterfaceOnly:=True
    Next

End Sub

The problem is, I have create an xll addin using C# and using that in Excel. And this problem is occurring with that addin — Varun Mahajan, Dec 27 '13 at 02:56

score 0 · Answer 3 · answered Jan 07 '14 at 02:13

Try turning off the UserControl, EnableEvents and setting the Calculation to Manual:

private static XlCalculation xlCalculation = XlCalculation.xlCalculationAutomatic;
static public void TurnOffApplicationSettings(Excel.Application xlApp)
{
    xlApp.ScreenUpdating = false;
    xlApp.DisplayAlerts = false;
    xlCalculation = xlApp.Calculation; //Record the current Calculation Mode
    xlApp.Calculation = XlCalculation.xlCalculationManual;
    xlApp.UserControl = false;
    xlApp.EnableEvents = false;
}

Then run your long operation and revert the UserControl and EnableEvents to true and the calculation back to Automatic (or more correctly what it was set to previously):

static public void TurnOnApplicationSettings(Excel.Application xlApp)
{
    xlApp.ScreenUpdating = true;
    xlApp.DisplayAlerts = true;
    xlApp.Calculation = xlCalculation;
    xlApp.UserControl = true;
    xlApp.EnableEvents = true;
}

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

3 Answers3