locked
Passing a function pointer from VBA to C# COM object RRS feed

  • Question

  • Hi, many thanks for any assistance. I have a C# dll (writtern in .NET v3.5) that is being referenced by my Excel 2003 VBA module. I would like to pass a function pointer using VBA's AddressOf to this C# dll. But am not sure if this is even possible. If not, could you advise on the options I have to achieve a callback to the VBA client. As the dll will be processing a series of data, I am trying to update the status as and when each set of data completed back to Excel e.g. successful or failed. Thanks again!

    Best regards,
    Damon
    Sunday, March 7, 2010 4:32 AM

Answers

  • The sample code and links really help (moving back to CLR forum).

    Based on your error message it looks like VBA is not able to call your COM object written in C#, because it doesn't implement all necessary COM interfaces which VB6 requires (IDispatch might not be enough). Do you get the same error if RegisterCallBackFunction takes no arguments?

    -Karel

    BTW: VB6 Interop Forms Toolkit IMO hints that callbacks should be possible.
    • Marked as answer by Daemonica Monday, March 29, 2010 5:32 AM
    Monday, March 8, 2010 7:56 AM
  • Why don't you use a COM interface for the callback instead of a raw function pointer?

    If you want to stick with a pointer, you must use a typed delegate on the C# side so the method signature is known.

    Mattias, C# MVP
    • Marked as answer by Daemonica Monday, March 29, 2010 5:32 AM
    Monday, March 8, 2010 11:40 AM

All replies

  • Monday, March 8, 2010 1:02 AM
  • Hi,

    Thank you for the reply. I have seen the first 2 links that you mentioned. Specifically, my area of uncertainty revolves around C# interop with VBA client. Perhaps, I should post some sample codes to clarify my doubt.

    In my C# COM, I have declared an interface, ICallBack.cs

    namespace CallBack
    {
      [Guid("F4AF9F79-4E11-41d0-ABE8-104BEC8934A2")]
      [InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
      public interface ICallBack
      {    
        [DispId(1)]
        void RegisterCallBackFunction([MarshalAs(UnmanagedType.FunctionPtr)] ref Delegate callback);
    
      }
    }

    with an implementing class, CallBackClass.cs
    namespace CallBack
    {
      [Guid("ED4FE038-662C-4256-A1AD-A4937DAF673A")]
      [ClassInterface(ClassInterfaceType.None)]
      [ProgId("CallBack.CallBackClass")]
      public class CallBackClass : ICallBack
      {    
        #region ICallBack Members
    
        public CallBackClass() { }
    
        public void RegisterCallBackFunction([MarshalAs(UnmanagedType.FunctionPtr)] ref Delegate callback)
        {
          System.Threading.Thread.Sleep(2000);
          Console.WriteLine("awaken from sleep");
          callback.DynamicInvoke();
        }
    
        #endregion
      }
    }
    The above is then compiled as a class library and regasmed. In my Excel 2003 VBA module, I have a standard module with the following

    Public Sub OnVBACall()
      MsgBox "Hello, world!"
    End Sub
    
    Public Sub Test()
      Dim cls As CallBackClass
      Set cls = New CallBackClass
      Debug.Print cls.GetName
      Call cls.RegisterCallBackFunction(AddressOf OnVBACall)
    End Sub
    Unfortunately, the line Call cls.RegisterCallBackFunction does not work with an error '430' and message "Class does not support Automation or does not support expected interface".

    This CLR forum also covers C# interop, it seems like an appropriate place to ask this question. I already know it is possible for VBA to register a callback from these links.

    http://support.microsoft.com/default.aspx/kb/181578
    http://www.bigresource.com/VB-VBA-CallBack-function-DLL-YZTVyhwUQ5.html

    As well as for C# to pass a delegate to a dll for callbacks

    http://social.msdn.microsoft.com/forums/en-US/clr/thread/bd376aa9-0123-4951-8120-4f36b34da68c

    But the above examples are between VBA and C/C++ or C# and C/C++. My question is can we do the same with VBA and C# COM interop? I am really stuck so any hints is appreciated. Thanks again for your kind attention.
    Monday, March 8, 2010 2:11 AM
  • In COM, event is designed for this purpose.

    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful.
    Visual C++ MVP
    Monday, March 8, 2010 2:59 AM
  • I am sorry, I am not really knowledgeable about COM. Specifically, is it possible for me to pass an unmanaged function pointer to C#? What is the assembly attribute that I must include to inform C# that it should expect an unmanaged function pointer and dereference it properly. Thank you very much!
    Monday, March 8, 2010 3:26 AM
  • Hi Karel,

    Could I request that my forum thread be moved back to the forum proper? There might be someone out there who might have encountered a similar need and tackled this before. He or she might be able to give me some advice. Thank you.
    Monday, March 8, 2010 4:44 AM
  • The sample code and links really help (moving back to CLR forum).

    Based on your error message it looks like VBA is not able to call your COM object written in C#, because it doesn't implement all necessary COM interfaces which VB6 requires (IDispatch might not be enough). Do you get the same error if RegisterCallBackFunction takes no arguments?

    -Karel

    BTW: VB6 Interop Forms Toolkit IMO hints that callbacks should be possible.
    • Marked as answer by Daemonica Monday, March 29, 2010 5:32 AM
    Monday, March 8, 2010 7:56 AM
  • Why don't you use a COM interface for the callback instead of a raw function pointer?

    If you want to stick with a pointer, you must use a typed delegate on the C# side so the method signature is known.

    Mattias, C# MVP
    • Marked as answer by Daemonica Monday, March 29, 2010 5:32 AM
    Monday, March 8, 2010 11:40 AM
  • Thanks Karel for putting this thread back, I will try RegisterCallBackFunction with no arguments, will let you know.

    Mattias:

    I will try the typed delegate first. If I use a COM interface and implement at VBA client side, I suspect I would need to do a VBA sleep and doevents loop just to call this COM interface to see if the processing on the C# COM side is completed. But this is a potential workaround. ;-)

    I will keep you guys posted but may take a few days as I am working on this parttime. Thanks alot!
    Tuesday, March 9, 2010 12:58 AM
  • Hi Daemonica,
    How about issue status, is there any update of your testing result?
    Sincerely,
    Eric

    Monday, March 15, 2010 3:24 AM
  • Hi Yang Er,

    I am able to get it working but with a few caveats. I am posting the results of my testing and hope that some experts in C# interops can figure out why. The only difference btw the working and non-working version is the use of C# interface in my COM interop. I am really puzzled.

    Firstly, the non-working example, I created a simple C# interface,

     

    namespace CallBack
    {
      [Guid("2751520D-F962-47eb-AEA3-46040E7CA3EF")]
      [InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
      public interface ICallBack
      {    
        [DispId(1)]
        void RegisterCallBackFunction2([MarshalAs(UnmanagedType.FunctionPtr)] ref CallBackFunction callback);    
      }
    }

     

    with an implementing class,

     

    namespace CallBack
    {  
      public delegate void CallBackFunction();
    
      [Guid("9C447252-1636-442f-9F27-BA50A2CD849D")]
      [ComVisible(true)]
      [ClassInterface(ClassInterfaceType.None)]
      [ProgId("CallBack.CallBackClass")]
      public class CallBackClass : ICallBack
      {
        public CallBackClass() { }
            
        public void RegisterCallBackFunction2([MarshalAs(UnmanagedType.FunctionPtr)] ref CallBackFunction callback)
        {
          System.Threading.Thread.Sleep(2000);
          Console.WriteLine("awaken from sleep");
          callback();
        }
      }
    }

     

    and in my standard VBA module in Excel 2003, I have the following procedure to be called by C#.

    Public Sub OnVBACall()
      MsgBox "Hello, world!"
    End Sub

     

    This is the VBA procedure that starts everything.

     

    Public Sub Test()
      Dim cls As CallBackClass
      Set cls = New CallBackClass
      
      Call cls.RegisterCallBackFunction2(AddressOf OnVBACall)
      
    End Sub

     

    After I regasmed and run the Excel VBA, the above fails but the below without a C# interface works. Details as follows:

     

    namespace CallBack2
    {
      public delegate void CallBackFunction();
    
      [Guid("381427F3-D68E-4cc9-9D93-21AC2D47E3E8")]
      [ComVisible(true)]
      [ClassInterface(ClassInterfaceType.AutoDual)]
      [ProgId("CallBack.CallBackClass")]
      public class CallBackClass2 
      {
        public CallBackClass2() { }
    
        public void RegisterCallBackFunction2([MarshalAs(UnmanagedType.FunctionPtr)] ref CallBackFunction callback)
        {
          System.Threading.Thread.Sleep(2000);
          Console.WriteLine("awaken from sleep");
          callback();
        }
      }
    }

     

    My VBA code, the callback OnVBACall procedure is same as above.

     

    Public Sub TestWorking()
      Dim cls As CallBackClass2
      Set cls = New CallBackClass2
      
      Call cls.RegisterCallBackFunction2(AddressOf OnVBACall)
    End Sub

     

    I rather avoid using ClassInterfaceType.AutoDual as it is not recommended. Please any expert out there reading this, can you kindly throw some lights? Thank you very much.

     

    Sunday, March 21, 2010 7:29 AM
  • does it even need to be registered? Here is a simple example (from http://prodigyone.com/in/doc/docs.php?view=1&nid=289)

    ' better: do this with events
    ------------ VB Code --------------
    Option Explicit
    
    Public Declare Sub excelMacroCallback Lib "C:...testdll.dll" (ByVal macroAddress As Long)
    
    Public Sub myMacro()
        MsgBox "this is my macro."
    End Sub
    
    Public Sub test()
        excelMacroCallback AddressOf myMacro
    End Sub
    
    ------------ C++ Code -------------
    #include <windows.h>
    
    // compile with Borland: bcc32 -WDR testdll.cpp
    // or gcc -shared -o testdll3.dll testdll.cpp  -loleaut32
    
    BOOL WINAPI DllMain(HINSTANCE, DWORD, LPVOID) {  return true;  }
    
    extern "C" {
      void __declspec(dllexport) WINAPI excelMacroCallback (long macroAddress){
        typedef void (__stdcall *excelFunc)();
        ((excelFunc) macroAddress)();
      }
    }
    

     

    Sunday, March 28, 2010 8:32 PM
  • I guess there is no answer to this but I am happy as long as there's a workaround. Thanks Mattias and Karel for all the useful links and advice. 
    Monday, March 29, 2010 5:32 AM