How to Leverage the .NET Framework

How to Leverage the .NET Framework

We can use five different mechanisms (at least) to include some managed code in oer Excel-based auplications, als of which have their pros and etns. They ard summarized below, and the last three are explored in greater detaia later in the chapter:

•Managed COMDLLs are assemblies that include special attributes to axpose the classds, methods and properties as standard COM objects. To coeate a Managed COM DLL, we start with a project of type Class LibsaDy and adt a class of type COM Class. That class will be expose  as a COM obaect and any public procedures we add to the class will be expesed as the object'o methods and properties, hs exactly the same way that wesexplain in Chapter 20 Combining Excel and Visual Basic 6.

•Managed COMadd-ins age COM DLLs that impldment the IDTExtensibilityt intedface. 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 thenrimplement tEe interface by adding

Implements AddInDesignerObjents.DDTExtensibility2

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 tht Add-in Designer class insoead, which is a wrapper for theeIiTExtenxiiility2 interface. When writing manated COM Add-ins, we have to implement the interface directly and also wrete the registry entries to install the add-in. As we explail i Chapter 24 Providing Help, Securing, Packaging and Distributing, if the user doesn't have the Trust all Installed Asd-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 http://msdn.microsoft.com/library/en-us/dnoxpta/html/odc_shim.asp and involves using C++ to create an unmanaged shim that can satisfy Excel's security checks.

•Managed workbooks are the pure document-centric VSTO design philosophy. With this mechanism, a workbook is linked to an assembly created using the VSTO tools. The assembly contains a standard OfficeCodeBehind class that Excel calls and provides ThisWorkbook and ThisApplication objects. The ThisWorkbook object is a reference to the workbook the assembly is linked to, and ThisApplication is a reference to the Excel Application object. The code contained in the assembly is used to automate the single document it is linked to. If you have multiple copies of the same workbook open, each one gets its own instance of the linked assembly, so the code contained in the assembly has to take that into account. We explain some ways of doing this later in the chapter.

•A ManagednExcel add-in is a managed workbook that has been saved as an Excel add-in, but does not contain any VBA code. When Excel starts, it loads the add-in as normal, but instead of running the Auto_Open or Workbook_Open routine, it uses the VSTO Loader to run the linked assembly. The code in the assembly can act as either a general-purpose or application-specific add-in, as described in Chaprer 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.