COM Events ignored when VBA Message Box is open

Sep 5, 2014 at 10:10 AM
Hi,

I've added a COM API to an existing Addin written using ExcelDNA. I'm exposing a number of C# events from the COM visible class using [ComSourceInterfaces] and the appropriate delegates, this allows me to subscribe to the events from VBA using the WithEvents syntax.

To be sure users receive all events I'm triggering them within a call to ExcelAsyncUtil.QueueAsMacro. In most circumstances this works really well, ExcelAsyncUtil queues up my actions, executing them (and triggering the COM events) only after all user interaction has finished (e.g. they've finished editing a cell or dealing with an Excel option window).

Unfortunately I've noticed that opening a dialog box from VBA (using the MsgBox function) doesn't cause QueueAsMacro to queue the action. Instead my events are triggered immediately and VBA completely ignores them (presumably because the MsgBox is open). This is a problem, not only has the event been missed but neither our addin nor the VBA user has any way of knowing that it's been missed.

So far my options seem to be:
a) Add a sequence number to my event payload and provide an application-level replay capability so VBA users can notice missing events and re-request them
b) Stop using events and use some sort of callback mechanism instead

I don't like either option; I'd much prefer to detect that a MsgBox is open and defer execution until after it's been closed but I haven't been able to find any way to do that. Does anyone know of another way to work around this, either in ExcelDNA or via the Excel APIs?

Thanks in advance,

Ben
Coordinator
Sep 5, 2014 at 12:14 PM
Hi Ben,

As a test, you might try the following:
  • Make some code that updates a worksheet by just writing to some cells, maybe writing the time or something.
  • Start a timer that calls ExcelAsyncUtil.QueueAsMacro regularly, getting it to run the sheet update.
  • Check whether the sheet continues to update during the VBA MsgBox display.
I'm trying to understand if the Excel COM object model really continues to work with the MsgBox open. If not, there might be some way to detect it in the queued macro code. It think the only way that can work is if Excel installs some special message filter or message handler in the message box window, though I might be confused about the message routing.

What about showing the message box from the .NET side? Does that have the same effect?

There are ways using the UI Automation libraries to keep track of which windows are opened and closed in Excel, but that would be a fair amount of work to figure out. Could more of the VBA stuff not move to VB.NET?

-Govert
Sep 5, 2014 at 3:44 PM
Hi Govert,

Thanks for getting back to me. I tried your suggestion and I can confirm that both ExcelAsyncUtils.QueueAsMacro and QueueMacro continue to execute macros while the MsgBox is open, the event still gets fired but the event handling code never gets fired.


VBA:
' simple macros
Public Sub TestMacro()
    Debug.Print "Running test macro: " + CStr(Time) + ", Interactive: " + CStr(Application.Interactive) + ", ScreenUpdating: " + CStr(Application.ScreenUpdating)
End Sub

Public Sub ShowMsgBox()
    Debug.Print "Showing MsgBox"
    MsgBox ("MsgBox Opened")
    Debug.Print "MsgBox Closed"
End Sub

' event handler
Private Sub api_OnApiEvent(ByVal apiEvent As IApiEvent)
    Debug.Print "Event received: " + CStr(Time) + ", Interactive: " + CStr(Application.Interactive) + ", ScreenUpdating: " + CStr(Application.ScreenUpdating)
End Sub
C#
// called by a timer once a second:
private void HandleTimerTick(object timerState)
{
    ExcelAsyncUtil.QueueMacro("TestMacro");
    ExcelAsyncUtil.QueueAsMacro(FireEvent);
}

private void FireEvent()
{
    var handler = OnApiUpdate;
    if (handler != null)
    {
        handler(new ApiEvent());
    }
}
VBA Debug Output:
Event received: 16:31:00, Interactive: True, ScreenUpdating: True
Running test macro: 16:31:01, Interactive: True, ScreenUpdating: True
Event received: 16:31:01, Interactive: True, ScreenUpdating: True
Running test macro: 16:31:02, Interactive: True, ScreenUpdating: True
Event received: 16:31:02, Interactive: True, ScreenUpdating: True
Showing MsgBox
Running test macro: 16:31:03, Interactive: True, ScreenUpdating: True
Running test macro: 16:31:04, Interactive: True, ScreenUpdating: True
Running test macro: 16:31:05, Interactive: True, ScreenUpdating: True
Running test macro: 16:31:06, Interactive: True, ScreenUpdating: True
Running test macro: 16:31:07, Interactive: True, ScreenUpdating: True
Running test macro: 16:31:08, Interactive: True, ScreenUpdating: True
MsgBox Closed
Running test macro: 16:31:09, Interactive: True, ScreenUpdating: True
Event received: 16:31:09, Interactive: True, ScreenUpdating: True
Running test macro: 16:31:10, Interactive: True, ScreenUpdating: True
Event received: 16:31:10, Interactive: True, ScreenUpdating: True
Running test macro: 16:31:11, Interactive: True, ScreenUpdating: True
Event received: 16:31:11, Interactive: True, ScreenUpdating: True
I switched to using a non-modal message box by calling the Windows API directly (like this) and the problem goes away, which is good. The problem is that the API that I'm creating is going to be distributed externally and telling users not to use the standard MsgBox feels a little restrictive and error prone.

Keeping track of open windows might be an option, I'll take a look at that and let you know if I find anything that could be of more general use.

Thanks,

Ben
Sep 8, 2014 at 4:39 PM
Hi Govert,

Following up on your suggestion I've been able to detect whether Excel has any Modal windows open using the UIAutomationClient libraries (using something similar to this stack overflow answer and checking the IsModal property).

I need to maintain an additional queue so from my perspective things would be even neater if QueueAsMacro could perform the check for me. Of course there may well be other cases where you don't want to queue execution for this condition, I'm not sure.

Anyway thanks very much for your advice,

Ben