Building a Real Time Data: Excel, multithreading and callbacks, part 5
This is part 5 of the tutorial that describes the techniques necessary to build an Excel RTD server. If you have just come to this page, I suggest you read all parts in order, here is Building a Real Time Data for Excel, part 1.
Excel’s muilti-threading is a strange beast. As we saw in part 4: Building a Real Time Data for Excel: 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#.
Building a Real Time Data for Excel tutorial:
- Building a Real Time Data for Excel, part 1
- Building a Real Time Data for Excel: Avoiding VSTO, part 2
- Building a Real Time Data for Excel: How RTD servers work, part 3
- Building a Real Time Data for Excel: Architecture, part 4
- Building a Real Time Data: Excel, multithreading and callbacks, part 5
- Building a Real Time Data for Excel: Providing easy-to-read function names, part 6
3 Comments
Cool. Do you have RTD examples in C++? ;-). I am new to Excel development and from this article i understand there are many pitfalls,-would be nice to see a simple example in C/C++.
Hi Olga,
I’m afraid I don’t have C/C++ skills. However, you can take any of my source code and use https://www.developerfusion.com/tools/convert/csharp-to-vb/ or https://www.developerfusion.com/tools/convert/csharp-to-vb/ to convert VB to C#.
Tnx. It was a very useful and generous article. Spared a lot of suffering ;-). Thank you.