Accessing a VSTO application-addin types from VBA (Excel)

Question

We have a VSTO application-addin (not a document-addin) for Excel, and we want to expose an event to VBA code so that the VBA macro can do some action when this event fires in the addin. How can I get the VBA code to be able to subscribe to an event defined in the VSTO application-addin?

I'd think that since the addin is loaded in the Excel process, this shouldn't be too tricky, but haven't found a way yet.

BTW, using VS 2008 and Excel 2007.

Thanks!

Answer 1

VSTO is not a DLL that can generally be called from other DLLs. VSTO is basically COM-exposed .NET code operating from within a wrapper operating from within a separate AppDomain. Although your VSTO add-in is technically a DLL that is being loaded into Excel, it operates more like a top-level EXE rather than as a DLL library exposed to other callers.

Personally, I would create a standard .NET assembly -- that is, avoid using VSTO for this -- and expose it to COM using the correct attributes. The process is well explained here: COM Interop Exposed - Part 2, under the section titled "Exposing .NET Events to COM".

If you really insist on enabling VBA to be able to call VSTO, then you'll have to operate via the Office.COMAddIn.Object property which is enabled by overriding the RequestComAddInAutomationService method. The process is discussed in detail in the article VSTO Add-ins, COMAddIns and RequestComAddInAutomationService by Andrew Whitechapel.

I hope this helps!

Mike