Passing COM object back to Excel-DNA via VBA

Jan 6, 2014 at 11:34 AM
Edited Jan 6, 2014 at 2:33 PM
In my current project I am trying to write an Excel .NET add-in that hooks into an existing third-party library (via COM/.NET), however, I am a bit of a greenhorn when it comes to COM despite reading quite a bit about it I've never really found a satisfactory solution.

There is an third-party add-in, let's call it ExcelAddin.xla that is loaded into Excel which, on startup, creates a new object. It also contains a function to return a property of the AddinController object (another class), (below is VBA):
Private AddinController as Object

Sub Auto_Open()
    Set AddinController = CreateObject("ExcelAddInController")  
End Sub

Function GetAddinApplication as Object
    Set GetAddinApplication = AddinController.Application
End Function
The ExcelAddInController is the ProgID of a COM-exposed class in a .NET library (let's call it ExcelAddInController.dll) that was also created by the same third party. It appears that the Application property is not COM-exposed, however I know the structure of the class.

I am trying to write a .NET-based add-in for Excel that hooks into ExcelAddin.xla and attempts to call GetAddinApplication to obtain the Application property so that I can do useful things with it.

I started to use Excel-DNA and wrote a .NET library in VB.NET and referenced the third-party ExcelAddInController.dll library so I could get at the classes (below is in VB.NET):
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Addin

    Implements ExcelDna.Integration.IExcelAddIn
    
    Private _excelApplication As Excel.Application

    Public Sub AutoClose() Implements ExcelDna.Integration.IExcelAddIn.AutoClose
    End Sub

    Public Sub AutoOpen() Implements ExcelDna.Integration.IExcelAddIn.AutoOpen
        _excelApplication = TryCast(application, Excel.Application)
        Dim AddinControllerApp as ExcelAddInControllerApplication = TryCast(_excelApplication.Run("ExcelAddin.xla!GetAddinApplication"),ExcelAddInControllerApplication)
    End Sub
 
End Class
However, when starting up the add-in via Excel-DNA (i.e. XLL) I received the message:
Unable to cast COM object of type 'System.__ComObject' to class type 'ExcelAddInControllerApplication'. Instances of types that represent COM components cannot be cast to types that do not represent COM components however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.
Does this message occur due to the process isolation of Excel-DNA?

In this case, is this solvable so that I can use early-binding in my add-in, or do I need to use late-binding to interact with the third-party library? It's not a huge issue either way, I just wanted to be sure there wasn't some clever trick I could use to use early-binding (for code auto-complete etc.)

Thank you for any help on this!
Coordinator
Jan 14, 2014 at 11:51 AM
Hi,

It looks like you are getting some COM object back from the GetAddinApplication call, but it is not matched to the ExcelAddInController.Application interface. Are you able to talk to the returned object late-bound (if you keep it 'As Object')? If so, at least you know you're getting the right object back. Then the problem might be the COM interop wrapper created when you reference the ExcelAddInController.dll or something.

Is your VB.NET library targeting .NET 4? If not, I suggest you change it, since the COM interop in .NET 4 is a bit smarter.

The AppDomain isolation of Excel-DNA does not affect COM interop.

I'm a bit surprised that the VBA call works anyway. One approach would be to try to get rid of the VBA part completely - you might be able to drive the ExcelAddInController completely from .NET.

-Govert