Excel, Multithreading and callbacks
Excel’s muilti-threading is a strange beast.
As we saw in Architecture, Excel calls happily but won’t receive unsolicited calls. This is perfectly reasonable once you understand the Windows message pump, if the UI thread is waiting on a Dialog box, it can’t accept other work.
The first, and most important thing to understand is that you will have to create a true real-time server running as an asynchronous task. Workarounds using timers will not work. Excel can and will call your code at any random moment and you must be prepared to handle the request and return control immediately. Never use System.Threading.Thread.Sleep on Excel’s thread, it’ll hang the UI.
The consequence is that you cannot call Excel from an asynchronous thread. This might not sound like a major handicap, until you try and figure out how to call Excel on the UI thread when the UI thread hasn’t called you. You could potentially wait for a RefreshData, but it might be a long wait and doing so is hardly conducive to a snappy UI experience.
The solution lies in the SynchronizationContext, a device which allows us to queue calls on another thread’s message queue. It works like this:
- We ask the host application for the current SynchronizationContext.
We can only do this once the host application has displayed its window, so asking in New() is too early, we have to ask later #18#, in our case from the WorkbookActivate event #20#.
- Armed with this SynchronizationContext, when our asynchronous thread needs to call Excel, it calls the context switcher #8#, which queues a call on the UI thread #9#.
- Back on the UI thread, we are free to do whatever we wish, and we call Excel to update topics with new data #10#.