Calling Application.Calculate breaks formula being edited

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.

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?

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

---

2 Years later - I'm glad some folks are getting use out of this. I actually ended up making some big enhancements to this to be able to determine "why" Excel is busy as much as possible. As such, I'll present two solutions.

First, the simple, self-contained (but limited) solution:

/// <summary>A variety of checks to see whether Excel is busy. This is required because
/// in recent versions of Excel, Recalculate can interrupt user activity.</summary>
private static bool IsExcelBusy(Application xlApp)
{
    try
    {    
        // The user is editing if Interactive is true and cannot be set to false
        // NOTE: Toggling App.Interactive can interrupt certain user activity
        // (e.g. renaming a sheet) so use this check sparingly.
        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;
    }
}

---

This is the more extensive solution, which now makes use of Native Methods (see appendix) to check on properties of the Excel UI itself. Disclaimer: These might break if Excel makes any other major UI changes in the future.

#region IsExcelBusy
/// <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>
/// <param name="xlApp">The excel application instance to test for activity.</param>
/// <param name="reason">out - the detected reason for Excel being busy,.</param>
/// <returns>True if a recalculation should be deferred, false if it's safe to recalculate.</returns>
public static bool IsExcelBusy(Application xlApp, out string reason)
{
    reason = null;
    try
    {
        if (xlApp.ActiveWorkbook == null) return false;

        // Check whether the user's cursor in some editable Excel control
        // (like the formula bar, renaming a sheet, typing in the Named Range box, etc.)
        IntPtr excelHwnd = (IntPtr)xlApp.Hwnd;
        uint excelThreadId = NativeMethods.GetWindowThreadProcessId(excelHwnd, out uint excelProcessId);
        // Get the handle of whatever window is in the foreground (system-wide)
        IntPtr foreground = NativeMethods.GetForegroundWindow();
        // TODO: Don't check the focused control if this control's parent doesn't include the main interface?
        //       There are false positives with, e.g. controls in the VBA editor.

        // If a non-excel-owned process has focus, we cannot get the focused control
        uint foregroundThreadId = NativeMethods.GetWindowThreadProcessId(foreground, out uint foregroundProcessId);
        if (foregroundProcessId == excelProcessId)
        {
            // We need to attach the thread that owns this window to get the focused control
            uint thisThreadId = NativeMethods.GetCurrentThreadId();
            try
            {
                if (thisThreadId != foregroundThreadId)
                    NativeMethods.AttachThreadInput(foregroundThreadId, thisThreadId, true);
                IntPtr focusedControlHandle = NativeMethods.GetFocus();
                if (focusedControlHandle != IntPtr.Zero)
                {
                    // Get the class name of the control that the user is currently interacting with (if any)
                    StringBuilder classNameResult = new StringBuilder(256);
                    NativeMethods.GetClassName(focusedControlHandle, classNameResult, 256);
                    string className = classNameResult.ToString();
                    // Determine if this control is at risk of being interrupted by a recalculations
                    switch (className)
                    {
                        case "EXCEL6":
                            reason = "User is editing a cell";
                            return true;
                        case "EXCEL<":
                            reason = "User is editing in the formula bar";
                            return true;
                        case "RICHEDIT60W":
                            reason = "User is editing a ribbon control";
                            return true;
                        case "Edit":
                            reason = "User is in the named range box";
                            return true;
                        case "EXCEL=":
                            reason = "User is renaming a sheet";
                            return true;
                    }
                }
            }
            finally
            {
                if (thisThreadId != foregroundThreadId)
                    NativeMethods.AttachThreadInput(foregroundThreadId, thisThreadId, false);
            }
        }
        // TODO: Ideally, we could discover if the user left the excel application while in edit mode so that
        //       we don't interrupt an edit-in-progress just because they temporarily switched to another window.
        else
        {
            // If the Excel application does not currently have focus, there's no winAPI call to figure out
            // whether they were in the middle of editing one of those controls when they left Excel.
            // We can use the following "poor-man's" test of whether the user is in the middle of
            // editing a formula, but it doesn't work for the other 4 cases mentioned above,
            // And actually leads to problems of its own (like removing focus from other excel-owned controls)
            try
            {
                if (!xlApp.Interactive) return false;
                xlApp.Interactive = false;
                xlApp.Interactive = true;
            }
            // If we a COM exception (Exception from HRESULT: 0x800A03EC)
            // This indicates that the action was blocked because a cell was in edit mode.
            catch (COMException)
            {
                reason = "A cell is in edit mode";
                return true;
            }
        }

        // Otherwise, assume Excel is not busy.
        return false;
    }
    catch (AccessViolationException ex)
    {
        reason = $"Excel is shutting down? ({ex.Message})";
        return true;
    }
    catch (COMException ex)
    {
        if (reason == null)
            reason = $"Excel is not responding to requests ({ex.Message})";
        return true;
    }
}

---

Appendix Here's the NativeMethods class used in the 'advanced' approach:

using System;
using System.Runtime.InteropServices;
using System.Text;

internal static class NativeMethods
{
    /// <summary>Gets the id of the thread (and process) that owns the specified window handle.</summary>
    [DllImport("kernel32.dll", SetLastError = true)]
    public static extern uint GetCurrentThreadId();

    /// <summary>What tread (and process) owns this window?</summary>
    [DllImport("user32.dll", SetLastError = true)]
    public static extern uint GetWindowThreadProcessId(IntPtr hWnd, out uint processId);

    /// <summary>What window is currently in the foreground (has focus)?</summary>
    [DllImport("user32.dll")]
    public static extern IntPtr GetForegroundWindow();

    /// <summary>Activates the specified window.</summary>
    [DllImport("user32.dll")]
    public static extern bool SetForegroundWindow(IntPtr hWnd);

    /// <summary>Gets the handle of the control that has keyboard focus (if you are on
    /// the same thread as that control).</summary>
    [DllImport("user32.dll", CharSet = CharSet.Auto, CallingConvention = CallingConvention.Winapi)]
    public static extern IntPtr GetFocus();

    /// <summary>Associate a thread's message queue with another thread.</summary>
    [DllImport("user32.dll")]
    public static extern uint AttachThreadInput(uint idAttach, uint idAttachTo, bool fAttach);

    /// <summary>Get the class name of the control with the specified handle.</summary>
    [DllImport("user32.dll", SetLastError = true, CharSet = CharSet.Unicode)]
    public static extern int GetClassName(IntPtr hWnd, StringBuilder lpClassName, int nMaxCount);

    #region GetWindow
    /// <summary>Used with GetWindow to get the owner of the window.</summary>
    public const uint GW_OWNER = 4;

    [DllImport("user32.dll")]
    public static extern IntPtr GetWindow(IntPtr hWnd, uint uCmd);
    #endregion GetWindow

    #region SetWindowLong
    /// <summary> Called GWLP_HWNDPARENT but this is a misnomer. It changes the OWNER,
    /// not the parent, of a window when used with SetWindowLong.</summary>
    public const int GWLP_HWNDPARENT = -8;

    /// <summary>Change a property of a window.</summary>
    public static IntPtr SetWindowLong(HandleRef hWnd, int nIndex, IntPtr dwNewLong)
    {
        return IntPtr.Size == 4 ?
            (IntPtr)SetWindowLongPtr32(hWnd, nIndex, (uint)dwNewLong) :
            SetWindowLongPtr64(hWnd, nIndex, dwNewLong);
    }

    [DllImport("user32.dll", EntryPoint = "SetWindowLong", CharSet = CharSet.Auto)]
    private static extern uint SetWindowLongPtr32(HandleRef hWnd, int nIndex, uint dwNewLong);

    // See https://www.medo64.com/2013/07/setwindowlongptr/ - code analysis complains if
    // the code-analysis tool itself is 32 bit, because it doesn't 'see' the entry point.
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Interoperability", "CA1400:PInvokeEntryPointsShouldExist")]
    [DllImport("user32.dll", EntryPoint = "SetWindowLongPtr", CharSet = CharSet.Auto)]
    private static extern IntPtr SetWindowLongPtr64(HandleRef hWnd, int nIndex, IntPtr dwNewLong);
    #endregion SetWindowLong

    #region SetWindowPos
    [Flags]
    public enum SWP : uint
    {
        /// <summary>If the calling thread and the thread that owns the window
        /// are attached to different input queues, the system posts the request to
        /// the thread that owns the window. This prevents the calling thread from
        /// blocking its execution while other threads process the request.</summary>
        ASYNCWINDOWPOS = 0x4000,
        /// <summary>Prevents generation of the WM_SYNCPAINT message.</summary>
        DEFERERASE = 0x2000,
        /// <summary>Draws a frame (defined in the window's class description) around the window.</summary>
        DRAWFRAME = 0x0020,
        /// <summary>Applies new frame styles set using the SetWindowLong function.
        /// Sends a WM_NCCALCSIZE message to the window, even if the window's size is
        /// not being changed. If this flag is not specified, WM_NCCALCSIZE is sent
        /// only when the window's size is being changed.</summary>
        FRAMECHANGED = 0x0020,
        /// <summary>Hides the window.</summary>
        HIDEWINDOW = 0x0080,
        /// <summary>Does not activate the window. If this flag is not set, the window is
        /// activated and moved to the top of either the topmost or non-topmost group
        /// (depending on the setting of the hWndInsertAfter parameter).</summary>
        NOACTIVATE = 0x0010,
        /// <summary>Discards the entire contents of the client area. If this flag is
        /// not specified, the valid contents of the client area are saved and copied
        /// back into the client area after the window is sized or repositioned.</summary>
        NOCOPYBITS = 0x0100,
        /// <summary>Retains the current position (ignores X and Y parameters).</summary>
        NOMOVE = 0x0002,
        /// <summary>Does not change the owner window's position in the Z order.</summary>
        NOOWNERZORDER = 0x0200,
        /// <summary>Does not redraw changes. If this flag is set, no repainting
        /// of any kind occurs. This applies to the client area, the non-client area
        /// (including the title bar and scroll bars), and any part of the parent window
        /// uncovered as a result of the window being moved. When this flag is set,
        /// the application must explicitly invalidate or redraw any parts of the window
        /// and parent window that need redrawing.</summary>
        NOREDRAW = 0x0008,
        /// <summary>Same as the NOOWNERZORDER flag.</summary>
        NOREPOSITION = 0x0200,
        /// <summary>Prevents the window from receiving the WM_WINDOWPOSCHANGING message.</summary>
        NOSENDCHANGING = 0x0400,
        /// <summary>Retains the current size (ignores the cx and cy parameters).</summary>
        NOSIZE = 0x0001,
        /// <summary>Retains the current Z order (ignores the hWndInsertAfter parameter).</summary>
        NOZORDER = 0x0004,
        /// <summary>Displays the window.</summary>
        SHOWWINDOW = 0x0040
    }

    /// <summary>Can be used to move a window around, or change its z-order</summary>
    [DllImport("user32.dll", EntryPoint = "SetWindowPos")]
    public static extern IntPtr SetWindowPos(IntPtr hWnd, IntPtr hWndInsertAfter,
        int x, int y, int cx, int cy, SWP wFlags);
    #endregion SetWindowPos
}