Calling Application.Calculate breaks formula being

Question

I'm working on an add-in that periodically requests a recalculation using the following:

((Excel.Application) xlApp).Calculate()

If the user happens to be editing a formula at the time, Excel breaks the users formula when I do this.

I expect the action to fail due to user activity, which it does, but not before oddly interrupting whatever formula the user is typing.

---

For example, if the user is in the middle of typing into a cell =SUM(1+2+, as soon as I run the above line of code, their input is interrupted and Excel complains that their formula is incomplete or similar.

Note that this is not even the same behaviour as if the user were to hit "Enter", which would result in a dialog like this:

Excel is doing something weirder, and trying to kick the user out of their formula input entirely without the niceties.

Even weirder - if the user's formula is syntactically valid, Excel not only kicks them out of editing the formula, but replaces the contents of the formula with the result!

---

I've confirmed that the issue is happening to a variety of my users, all using modern versions of Excel.

I've tried both invoking Calculate() on the main excel-owned application, and in a background process, but both behave identically. I've tried different calculation methods (like CalculateFull()) but it's the same. I've also tried other interop actions like xlApp.StatusBar = "Test" and they don't interrupt the user's actions the way Calculate does.

Is there anything I can do to prevent interrupting the user like this? I could have sworn this wasn't the behavior in older versions of Excel.

If it makes a difference, I am using the Excel-Dna library as the basis for my add-in, but I'm purely using Microsoft.Office.Interop.Excel.Application for this piece.

---

Update with some more weirdness:

I had mild success using the method described here to check if I could set Application.Interactive to false then back to true - if not, the user is editing a cell. That allows me to skip Calculate in cases where they are in the formula editor, but oddly, doesn't prevent Excel from kicking the user out of other inputs.

For example, if the user is:

• Editing a sheet name
• Assigning a named range name to a cell
• Typing a new font name into the "Font" ribbon box
• etc...

--The user gets kicked out of all these actions when app.Calculate() is called, and usual methods for detecting if they are editing a formula don't detect when the user is doing any of those things.--

Update re: Application.Interactive

It turns out settingApplication.Interactive = True causes even more user-interruption issues, such as stealing focus out of dialogs, and interrupting mouse-drag operations (like resizing or moving windows). Not recommended as a solution if your goal is to not annoy users.

Answer 1

EDIT

Using Application.Interactive caused more problems than it solved. This solution is not recommended.

---

This is still a work in progress, but so far I've had to resort to a variety of hacks to detect if the user is actively doing something in Excel, and simply stop myself from calling "Calculate" if so.

The main checks are:

• Is an Excel-owned window currently in the foreground?
  • If not, some the user is using some other program, calling Calculate won't interrupt them.
  • If so, is it the main workbook window (Excel.Application.Hwnd)?
    • If not, the user is in some Excel dialog, VBA editor, etc. Don't interrupt.
    • If so, we need to dig deeper.
• Is the user's mouse currently down?
  • If so, they're definitely busy (dragging, clicking, resizing, etc.) Don't interrupt.
• Is the user's cursor in some editable control? (Renaming the sheet, picking a font from a dropdown, typing in the named range box, etc).
  • If so, don't interrupt. (Haven't figured out how to test for this yet).
• Is the user editing a cell? (Can test for this specifically with Excel.Application.Interactive)
  • If so, don't interrupt.

There's still things to work around, but this already helps a lot.

---

Here's the code - hopefully the comments make the logical progression clear:

/// <summary>A variety of checks to see whether Excel is busy. This is required because
/// in recent versions of Excel, invoking Recalculate can interrupt user activity.</summary>
private static bool IsExcelBusy(Application xlApp)
{
    try
    {
        // Check whether the main Excel application window is currently in the foreground
        // We do this by getting Excel's native window handle, and that of the foreground window
        IntPtr xlHwnd = (IntPtr)xlApp.Hwnd;
        IntPtr foreground = NativeMethods.GetForegroundWindow();
        if (xlHwnd != foreground)
        {
            // If the main Excel window is not in the foreground, see who owns the
            // foreground window by getting the id of each window's owning process.
            NativeMethods.GetWindowThreadProcessId(xlHwnd, out uint xlProc);
            NativeMethods.GetWindowThreadProcessId(foreground, out uint foregroundProc);
            // If the foreground window is owned by the Excel application, return busy
            // If the foreground window is some other process, Excel itself is not busy.
            return xlProc == foregroundProc;
        }
        // If the main excel window is active, ensure the user isn't doing anything.

        // Check if the user currently has a downed mouse button (to avoid interrupting
        // drag operations within the Excel application)
        if (Control.MouseButtons != MouseButtons.None)
            return true;

        // TODO: Check whether the user's cursor in some other Excel control (like
        //       renaming a sheet, typing in the Named Range box, typing in the Font box, etc.

        // The user is editing a formula if Interactive is true and cannot be set to false.
        // https://www.add-in-express.com/creating-addins-blog/2011/03/23/excel-check-user-edit-cell/
        // NOTE: Even setting App.Interactive can interrupt user activity, so do this test last.
        if (xlApp.Interactive)
        {
            xlApp.Interactive = false;
            xlApp.Interactive = true;
        }

        // Otherwise, assume Excel is not busy.
        return false;
    }
    catch (AccessViolationException)
    {
        return true;
    }
    catch (COMException)
    {
        return true;
    }
}

---

Based on how difficult this has been, I feel as though I'm trying to do things with Excel that it isn't meant for. Excel was definitely meant to be automated, and definitely meant to be used by end users. Maybe not just both at the same time?