Tutorial: COM server support for VBA integration

Functions and macros created in an Excel-DNA add-in can be called directly from Excel VBA by using Application.Run(…). However, .NET also supports creating rich object models that are exported as COM libraries, which can be Tools->Referenced in VBA. Excel-DNA has some advanced support to host COM-exported objects from Excel-DNA add-ins, giving some advantages over the regular .NET ‘Register for COM interop’ hosting approach:

  • COM objects that are created via the Excel-DNA COM server support will be active in the same AppDomain as the rest of the add-in, allowing direct shared access to static variables, internal caches etc.
  • COM registration for classes hosted by Excel-DNA does not require administrative access (even when registered via RegSvr32.exe).
  • Everything needed for the COM server can be packed in a single-file .xll add-in, including the type library used for IntelliSense support in VBA.

Mikael Katajamäki has written some detailed tutorial posts on his Excel in Finance blog that explore this Excel-DNA feature, with detailed explanation, step-by-step instructions, screen shots and further links. See:

Note that these techniques would works equally well with code written in VB.NET, allowing you to port VB/VBA libraries to VB.NET with Excel-DNA and then use these from VBA.

Thank you Mikael for the great write-up!

Advertisements
This entry was posted in Uncategorized and tagged , , , , , , , . Bookmark the permalink.