Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

How to Leverage the .NET Framework

We can use five different mechanisms (at least) to include some managed code in our Excel-based applications, all of which have their pros and cons. They are summarized below, and the last three are explored in greater detail later in the chapter:

Managed COMDLLs are assemblies that include special attributes to expose the classes, methods and properties as standard COM objects. To create a Managed COM DLL, we start with a project of type Class Library and add a class of type COM Class. That class will be exposed as a COM object and any public procedures we add to the class will be exposed as the object's methods and properties, in exactly the same way that we explain in Chapter 20 Combining Excel and Visual Basic 6.

Managed COMadd-ins are COM DLLs that implement the IDTExtensibility2 interface. To create a Managed COM Add-in, we start with a Managed COM DLL and add a reference to the Microsoft Add-in Designer library. We then implement the interface by adding

Implements AddInDesignerObjects.IDTExtensibility2

at the top of the class module and adding our code for all the interface procedures. In Chapter 21 Writing Add-ins with Visual Basic 6, we used the Add-in Designer class instead, which is a wrapper for the IDTExtenxibility2 interface. When writing managed COM Add-ins, we have to implement the interface directly and also write the registry entries to install the add-in. As we explain in Chapter 24 Providing Help, Securing, Packaging and Distributing, if the user doesn't have the Trust all Installed Add-ins and Templates setting ticked in the Tools > Macro > Security dialog, Excel will only automatically load a COM Add-in if it is digitally signed with a trusted digital signature. Unfortunately, Excel ignores any digital signatures we apply to managed assemblies, so will not automatically load managed COM Add-ins. The workaround for this issue is explained at Chapter 5 Function, General and Application-Specific Add-ins.

Hybrid VBA/VSTOSolutions are workbooks that both contain VBA and have linked VSTO assemblies interoperating with each other. This usage is officially untested and unsupported by Microsoft but in our opinion provides by far the best mechanism for a gradual migration from VBA to a managed (VSTO) solution, should we want to do so.