COM Events

May 3, 2013 at 3:05 AM
Hi there,

I have been struggling to create a stable COM object in C# to trigger events within Excel w/o having Excel crashing.

So far, I have written a COM object that triggers events and Excel catches them fine. However when Excel is busy (e.g mouse click down, window is being resized,...) and the event triggers Excel simply crashes.
I am using the COM object to interface with a pricing platform that prices Index Options which need to be then manipulated in Excel. This needs to be done via events (No UDFs/RTD here) for the purpose of my workbook.

One of my colleague mentioned that Excel DNA was great at interfacing .Net and Excel, so I thought I could have a crack at it. However, I have had a look at the samples and couldn't really figure out a way to do this.

Can someone lend me a hand please?

Thanks heaps.

Regards
Jeremie
Coordinator
May 3, 2013 at 10:30 AM
Hi Jeremie,

Excel-DNA would be able to sort out your issue, but is quite a big step to take with a steep learning curve. I'm happy to help though.

You'd be turning your code (or the part of your code that interacts with Excel) into an Excel-DNA add-in.
You don't explain exactly how the object is created and the how the new information arises - let's assume you are notified from some other thread.

I suggest:
  • Make a new C# class library.
  • Add the "Excel-DNA" Nuget package.
  • Take a small UDF sample from the readme.txt, compile and check that everything works.
  • Add a macro:
[ExcelCommand(MenuName="My AddIn", MenuText="Test Update")]
public static void MyUpdate() 
{
    dynamic xlApp = ExcelDnaUtil.Application;
    xlApp.Range["A1"].Value = "Hello!";
}
  • Check that the macro runs and works from a menu (on the Add-Ins tab).
  • Make a new class "MyEventSource" with a Timer that will simulate your incoming data.
  • Add a class that implements IExcelAddIn AutoOpen handler, instantiate and keep a reference to "MyEventSource" here.
  • In the AutoOpen add a call to ExcelDna.Integration.ExcelAsyncUtil.Initialize().
  • In the Timer event call your Excel update routine via
    ExcelAsyncUtil.QueueMacro("MyUpdate");
  • There is also an ExcelAsyncUtil.QueueAsMacro(...) that takes a delegate.
The parts you really need are hidden behind the ExcelAscynUtil.Initialize and ExcelAsyncUtil.QueueAsMacro calls. Excel-DNA sets up a behind-the scenes message queue, waits for Excel to be 'available' and then runs your code. So when your code is running Excel is in a state where the COM calls won't fail due to user interaction or Excel being 'busy'.

OK - that's the basic idea. I've probably left out some crucial details, and every step might lead you to some more questions. Feel free to ask.

Finally, it really sounds like something well suited to an RTD mechanism using the Reactive Extensions library. Some data is pushed in, you do some calculations and update the workbook, which further uses the results. Excel-DNA makes all of that work very well. But you certainly don't have to start there.

Write back if you try it.

Cheers,
Govert
May 6, 2013 at 8:49 AM
Edited May 6, 2013 at 9:00 AM
Hi Govert,

Thanks a lot for your answer.

I have tested it and it works like a charm, thanks a lot for that. Indeed I understand from your perspective why you are mentioning that RTD + Reactive Extensions would be more suited (and I am really interested in seeing how to do this by the way. If you have a link to a tutorial of some sort to provide me that would great please).

However, I have to apologize because I have poorly described what I am trying to achieve here.

The overall data-flow of my process is quite different (I think), and makes it a bit trickier (unless I code it in a messy way based on what I have understood so far).

Here is a quick overview of how the flow goes (so far w/o Excel-DNA):

1- The user clicks on a button/GUI located on an Excel sheet
2- This triggers some VBA code which processes some info and instantiates the COM object
3- The VBA asks the COM object to request a pricing or a risk parameter (let's just say it is asking for the price of a banana for simplicity here) based on certain inputs VBA holds (Banana size, provenance, sweetness, etc...)
4- The COM object generates a banana price query and sends it to the pricing platform. (Actually here I am using Reactive Extensions for the query, sending and receiving the message)
5- At this stage the VBA sub/function exits. But in the meanwhile the platform is still crunching the pricing request.
6- Once the pricing is done the COM object receives the price of the banana from the platform
7- The COM object throws an event "PriceReceived" passing as parameters all the details of the banana's pricing (weight, price/kg, etc...)
8- VBA receives the event and starts some more processing (which includes displaying the result on a sheet but not necessarily)
9- the end

This currently errors at point 8 if Excel is busy.

I understand now that ExcelAsyncUtil.QueueMacro/ExcelAsyncUtil.QueueAsMacro allows this to be avoided which is great.
What I would like to go around in your example is:
1- I would like VBA to pilot/control what is happening with the .Net component instead of being passive (VBA calling for the calculations to be done in .Net).
2- I would like the .Net component to throw events (for VBA to catch) when the information is available, like that it will follow some conventions we are following in my company.

Do you think this is doable with Excel-DNA? or maybe you have a better idea on how to structure this data-flow?

Thanks

Regards
Jeremie
Coordinator
May 6, 2013 at 11:38 AM
Edited May 6, 2013 at 11:38 AM
Hi Jeremie,

Easiest might be to port the VBA code to VB.NET - it would simplify things a bit.
If that's not possible, you'll have to explain what can and can't change in your architecture.

Maybe you'd like to contact me directly - at govert@icon.co.za ?

Cheers,
Govert
May 7, 2013 at 1:10 AM
Hi Govert,

I will try to play around a bit more, see if I can come up with something. If I can't I will try to contact you directly.

Thank you so much for your help, it is deeply appreciated.

Cheers
Jeremie
Mar 5, 2015 at 6:48 AM
I had the same excel crash problem on invoking VBA macros from C# module. Using QueueAsMacro, it just worked. Thank you very much.