Raising COM server events

[ateene]

I'm trying to implement a COM server that raises COM events in Excel based on this example: http://msdn.microsoft.com/en-us/library/dd8bf0x3(v=vs.90).aspx

My problem is that the NewMessage event doesn't get invoked in the FireNewMessageEvent method.

Thanks, Andres

namespace XLServer

{

    // Step 1: Defines an event sink interface (MessageEvents) to be implemented by the COM sink.

    [ComVisible(true)]

    [InterfaceTypeAttribute(ComInterfaceType.InterfaceIsIDispatch)]

    public interface MessageEvents

    {

        void NewMessage(string s);

    }

    // Step 2: Connects the event sink interface to a class by passing the namespace and event sink interface

    [ComVisible(true), ClassInterface(ClassInterfaceType.None)]

    [ComSourceInterfaces(typeof(MessageEvents))]

    public class MessageHandler

    {

        public delegate void NewMessageDelegate(string s);

        public event NewMessageDelegate NewMessage;

        public MessageHandler() { }

        public void FireNewMessageEvent(string s)

        {

            Debug.Print($"New Message {s}");

            if (NewMessage != null)

            {

                Debug.Print($"Invoke {s}");

                NewMessage.Invoke(s);

            }

        }

    }

}

VBA code

Public WithEvents mh As MessageHandler

Public status As String

Private Sub Class_Initialize()

    Set mh = New MessageHandler

    status = "Initialized"

End Sub

' Events and methods are matched by name and signature.

Private Sub mh_NewMessage(ByVal s As String)

    MsgBox "Message " + s

End Sub

[Govert van Drimmelen]

Hi Andres,

The issue might have to do with how you are firing the event, but it looks fine and worked for me in a simple test.

I suggest add some way to call your class from the VBA side, e.g. just change the ClassInterface attribute to: ClassInterface(ClassInterfaceType.AutoDual)

This will allow you to call the FireNewMessageEvent from VBA.

Support your VBA code with the WithEvents is in a class called MessageListener, then you might try

Sub Test()

    Dim listener as New MessageListener

    listener.mh.FireNewMessageEvent "Hello there!"

End Sub

This should work, and show that the event is working fine.

Whether it would be safe to fire the event from the C# side from another thread is another matter...

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of ateene [atee...@gmail.com]
Sent: 05 February 2017 09:28 PM
To: Excel-DNA
Subject: [ExcelDna] Raising COM server events

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[ateene]

Thanks for the quick reply.

I get the following error when I made the suggested changes and fire the event from VBA

Exception thrown: 'System.Runtime.InteropServices.COMException' in mscorlib.dll

An exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll but was not handled in user code

Additional information: Unknown name. (Exception from HRESULT: 0x80020006 (DISP_E_UNKNOWNNAME))

My objective is to be able to fire the event from the C# side. btw, this is similar to the approach being used by the IB ActiveX Api for passing events from their server to excel. So far I've implemented an RTD server and various asyn com functions using the IB C# api and excel-dna, I also want to  pass some of the events from the IB api to excel.

Thanks,

[Govert van Drimmelen]

Did you re-run "tlbexp" after making the changes?

Did you remove and add again the reference to the .tlb (or .xll if packed) on the VBA side?

For firing from the C# side you'll just have to understand the threading - I'm not sure whether the .NET COM Interop will automatically sort out the marshaling onto the main thread for VBA.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of ateene [atee...@gmail.com]

Sent: 05 February 2017 11:25 PM
To: Excel-DNA
Subject: Re: [ExcelDna] Raising COM server events

[ateene]

Yes, I've re-run and tlbexp and reloaded the .tlb on the VBA side.

When firing the FireNewMessageEvent on the C# side the NewMessage event is null so it doesn't get invoked. From the VBA side the NewMessage event get assigned but causes an exception with NewMessage.Invoke(s).

I've started looking at the IB ActiveX code, here is a example on how they fire events from C# side

      private void ErrorEvent()

        {

            int msgVersion = ReadInt();

            if (msgVersion < 2)

            {

                string msg = ReadString();

                eWrapper.error(msg);

            }

            else

            {

                int id = ReadInt();

                int errorCode = ReadInt();

                string errorMsg = ReadString();

                eWrapper.error(id, errorCode, errorMsg);

            }

        }

      .....

    [ProgId("Tws.TwsCtrl")]

    [Guid("0A77CCF8-052C-11D6-B0EC-00B0D074179C")]

    [ComVisible(true), ClassInterface(ClassInterfaceType.None)]

    [ComSourceInterfaces(typeof(ITwsEvents))]

    public class Tws: UserControl, EWrapper, ITws, IDisposable

    {    

       ....

       public delegate void errMsgDelegate(int id, int errorCode, string errorMsg);

       public event errMsgDelegate errMsg;

       void EWrapper.error(int id, int errorCode, string errorMsg)

        {

            var t_errMsg = this.errMsg;

            if (t_errMsg != null)

                InvokeIfRequired(t_errMsg, id, errorCode, errorMsg);

       }

       ....

        void InvokeIfRequired(Delegate method, params object[] args)

        {

            if (InvokeRequired)

                Invoke(method, args);

            else

                method.DynamicInvoke(args);

        }

        .....

.

[Govert van Drimmelen]

Not sure why you're getting an error - it worked fine for me when calling from VBA. Your description doesn't make clear whether you're calling FireXXX from VBA side on a WithEvents instance... Maybe you can post a complete sample project.

In the IB code the threading is taken care of by deriving from UserControl and the Invoke calls - that implicitly uses a Window message loop to marshal to the main thread, and should work OK. Excel can still be a bit funny about being 'busy' even if you run on the main thread, so remember to test event firing while the user is busy editing a cell formula.

-Govert

[ateene]

Thanks, I'll try that and see how that works.

[ateene]

It's still not working. Here is a simplified complete version that fires the event from the C# side

using System;

using ExcelDna.Integration;

using ExcelDna.ComInterop;

using System.Runtime.InteropServices;

using System.Diagnostics;

using System.Windows.Forms;

namespace XLServer

{

    [ComVisible(true)]

    [InterfaceTypeAttribute(ComInterfaceType.InterfaceIsIDispatch)]

    public interface Events

    {

        void NewEvent();

    }

    [ComVisible(true)]

    //[ComVisible(true), ClassInterface(ClassInterfaceType.None)]

    //[ComVisible(true), ClassInterface(ClassInterfaceType.AutoDual)]

    [ComSourceInterfaces(typeof(Events))]

    public class MessageHandler

    {

        public delegate void NewEventDelegate();

        public event NewEventDelegate NewEvent;

        public System.Threading.Timer EventTimer;

        public MessageHandler() { StartEvents(); }

       public void FireNewEvent()

        {

            Debug.Print("Fire New Event");

            if (NewEvent != null) NewEvent();

            // if (NewEvent != null) NewEvent.DynamicInvoke();  // use with System.Windows.Forms UserControl

        }

        public void StartEvents()

        {

            if (EventTimer == null) EventTimer = new System.Threading.Timer(MessageEvent, null, 1000, 2000);

        }

        private void MessageEvent(object o)

        {

            FireNewEvent();

        }

    }

}

Here is the VB code

Public WithEvents mh As MessageHandler

Public status As String

Public Sub Class_Initialize()

    Set mh = New MessageHandler

    status = "Initialized"

End Sub

' Events and methods are matched by name and signature.

Public Sub mh_NewEvent()

    On Error Resume Next

    MsgBox ("NewEvent Fired")

End Sub

I get the following error in the FireNewEvents method when I start the MessageHandler in Excel

Fire New Event

Exception thrown: 'System.Reflection.TargetException' in mscorlib.dll

An unhandled exception of type 'System.Reflection.TargetException' occurred in mscorlib.dll

Additional information: Object does not match target type.

I get this error if I use the UserControl and Invoke :

Fire New Event

Exception thrown: 'System.Reflection.TargetInvocationException' in mscorlib.dll

An unhandled exception of type 'System.Reflection.TargetInvocationException' occurred in mscorlib.dll

Additional information: Exception has been thrown by the target of an invocation.

[Govert van Drimmelen]

Hi Andrew,

 

1.       The class that worked for me looks like this:

 

using System.Diagnostics;

using System.Runtime.InteropServices;

 

namespace XLServer

{

    // Step 1: Defines an event sink interface (MessageEvents) to be implemented by the COM sink.

    [ComVisible(true)]

    [InterfaceTypeAttribute(ComInterfaceType.InterfaceIsIDispatch)]

    public interface MessageEvents

    {

        void NewMessage(string s);

    }

 

    // Step 2: Connects the event sink interface to a class by passing the namespace and event sink interface

    [ComVisible(true)]

    [ClassInterface(ClassInterfaceType.AutoDual)]

    [ComSourceInterfaces(typeof(MessageEvents))]

    public class MessageHandler

    {

        public delegate void NewMessageDelegate(string s);

        public event NewMessageDelegate NewMessage;

        public MessageHandler() { }

 

        public void FireNewMessageEvent(string s)

        {

            Debug.Print($"New Message {s}");

            if (NewMessage != null)

            {

                Debug.Print($"Invoke {s}");

                NewMessage.Invoke(s);

            }

        }

    }

}

 

2.       This is the AutoOpen:

 

using System.Runtime.InteropServices;

using ExcelDna.Integration;

 

namespace ComServer

{

    [ComVisible(false)]

    public class ExcelAddin : IExcelAddIn

    {

        public void AutoOpen()

        {

            ExcelDna.ComInterop.ComServer.DllRegisterServer();

        }

        public void AutoClose()

        {

            ExcelDna.ComInterop.ComServer.DllUnregisterServer();

        }

    }

}

 

3.       You need to run: "TlbExp ComServer.dll" (or whatever your .dll is).

 

4.       On the VBA side you need to make a new workbook, Alt+F11 and then Tools->Reference and browse to the ComServer.tlb.

 

5.       Then add a Class Module, called MessageListener:

 

Public WithEvents mh As MessageHandler

 

Public status As String

 

Private Sub Class_Initialize()

    Set mh = New MessageHandler

    status = "Initialized"

End Sub

 

' Events and methods are matched by name and signature.

Private Sub mh_NewMessage(ByVal s As String)

    MsgBox "Message " + s

End Sub

 

Public Sub Fire()

    mh.FireNewMessageEvent "Hello there!"

End Sub

 

6.       And this in a Module:

 

Sub Test()

    Dim ml As New MessageListener

    ml.Fire

End Sub

 

7.       Run the Test() macro. It works fine on my machine.

 

-Govert

 

[ateene]

Govert,

Thanks that worked for firing events from VBA. 

I also figured out the problem on firing events from the C# side. I had to change the timer I was using to fire the events from the System.Threading.Timer to the System.Windows.Forms.Timer. 

Thanks!