The proposed method to interface Excel and Solace Systems is an Excel RTD Server. There are a number of reasons for this, worthwhile to set out the thinking behind this decision.
The choices considered were:
- RTD Server
- DDE Server
- VSTO add-in
- COM Addin
- Excel vba addin
Taking these in reverse order, why were they rejected?
Excel vba addin: Rejected
We've covered Excel VBA a lot here, here and in Excel Industrialisation. The primary reason to reject VBA is encapsulated in a quote from Excel VBA – does it have a future?
"there are no real changes to the VBA code base in functional terms between VBA6 and VBA7 as the changes are all made to enable underlying operating system changes. So we have to look at VBA6 as the last time there were functional changes in Excel VBA. VBA6 was introduced in Excel 2000 which was released in 1999"
Since the language has not been functionally changed for 15 years, I think it's safe to say that it's not a language to use for new projects.
COM Addin: Rejected
I have written a few COM Addins and I see merit. In essence, the COM Addin was an Office addin, generic over the office platform. There is a good MSDN article that details some of the inner workings. In summary, useful, but not right for what we are going to achieve here.
VSTO add-in: Rejected
VSTO is a set of templates to assist in development of COM callable components within the dot net world. I've never warmed to this approach, perhaps if I had more time to investigate I could find things to like that would make the learning journey worthwhile.
DDE Server: Rejected
Pushing data into Excel using DDE is problematic for this use case since the formula syntax that Excel requires for DDE is similar to this:
This requires every cell that has a DDE link to contain all of the data needed - no links to other cells are possible.
There are ways to work around this - one I have implemented has been to write VBA code to create formula links to DDE servers based on ranges of data and column headings and then insert them into the spreadsheet. While that works, it's inflexible and it can be a pain to modify and support these sheets.
RTD Server: Selected
RTD is the best option based on assessment and subsequent rejection of other choices. It's not perfect, I have advocated changes since 2005.
If your code will receive events from a middleware layer then it "fits" to use an event driven model to push that data into Excel. For the most supportable manner that would be C# code with COM interop. Of course, any language that supports CLR and COM interop would work but since I have already built RTD servers in C# it makes sense to use a language that is familiar. Once this is completed, I may try using the COM Type Provider for F# and implement in F#.
So, we have logic to the decision to use C#.
One of the problems with this pattern is that the it's necessary to get COM and C# to play nicely together. This is not as easy as it looks when you look at the edge cases and the behaviour of COM under load - the error code RPC_E_SERVERCALL_RETRYLATER is one that soon crops up and needs to be handled.
In essence, we see that the call made within the implementation of IRtdServer to CallbackObject.UpdateNotify() can be ignored when Excel is busy on the user interface thread due to user input. If this issue is not handled then you will see that the code fails on a frequent but irregular basis.
Fortunately there is an implementation of the code required available on MSDN https://msdn.microsoft.com/en-us/library/ms228772.aspx
In initial testing this appears to be a viable solution, but this will take some time to confirm. Shortly I hope to push an updated set of code to github along with some narrative on this blog.