Mine Field of Excel Development Tools – Part 2

I’m continuing my notes here for anyone else who needs to tread through this minefield. At the moment my code is working great on systems that have Visual Studio installed, but not on another test system without VS. Sigh. That means I can’t deploy this code yet. See the Summary in this article for the current status, and see the first article for more links and details.

The big thing I’m trying to avoid is this from inside an existing addin:
Microsoft.Office.Interop.Excel.Application xlApp
xlApp = new Microsoft.Office.Interop.Excel.Application();

The reason is that my code is already working within an instance of Excel, so I don’t need another one. I need to get a handle to xlApp passed from Excel itself when the addin is registered. The way I accomplished this is to have an initialization routine. All XLS files need to have one cell (like A1) with an =NOW() function, and another cell that says =Initialize(A1). Then a ctrl-shift-alt-F9 will force a call to Initialize(). In that function if I already have an instance of the xlApp I immediately exit. If I don’t, I use the cell reference (A1 or whatever) to drill back through the OM and get a reference to the Excel Application. This works perfectly.

A walk down the wrong path?

Before this, I figured the way to get access to the Excel OM and get a UDF via automation was to implement IDTExtensibility2. Surprisingly if you look for this interface at MSDN you will find mostly references to creating add-ins for Visual Studio, not for MS Office apps. Andrew Whitechapel notes here that (Neville really suggested) you can create an addin for VS and then use it for Office apps.

Another blog describes in detail exactly how to go about creating an automation add-in using the VS addin template. I decided to give that a shot.

I created as strong name for my assembly, stripped out the code as described, but I started getting confused with the approach described. I’ve tried implementing an interface but didn’t have any special luck when registering this with Excel. Andrew says we need to turn off the Register for COM Interop flag – I don’t think anything works if I do this. The final straw on this was in step 7 on creating a shim using the COM Shim Wizard. Somehow the idea of creating a C++ ATL shim seems to be going a bit too far. This also led me to start researching shims.

Shims

It seems shimming is very important: Here is the original document on shims – it’s required reading for anyone who doesn’t know what a shim is:

This article refers to a shim wizard v2.3. I thought this was an updated wizard for Office 2003 but it turns out it’s only usable with Office 12 (2007). I need an interface for v12 too, but I need to get the v11 component done first.

This article called "Isolating Microsoft Office Extensions with the COM Shim Wizard Version 2.0" is also Very significant. It clarifies why we should shim: If you are not using Visual Studio Tools for Office to build managed Microsoft Office extensions, you should use a custom COM shim. There are two reasons why you should use a COM shim: security and isolation. You can digitally sign your COM shim and, by doing so, take fullest advantage of Microsoft Office security features for your managed extension. If you do not use a COM shim, Office loads your extension DLL into the default application domain along with all other un-shimmed extensions. All DLLs in the same application domain are vulnerable to potential damage caused by any other DLL in the same application domain.

The article includes "If you build Excel user-defined functions using managed automation add-ins, these should also be shimmed."

Yeah, I’ve been sort of getting that – glad it’s spelled out here. It continues: "The COM Shim Wizard does not support managed automation add-ins. However, it is possible to extend the shim code that the wizard generates for a regular COM add-in so that it can be used for an automation add-in. For details on how you can extend the shim code for this purpose, refer to these blog entries: OfficeZealot: Example Shimmed Automation Add-In, and OfficeZealot: Implementing IDTExtensibility2 in an Automation Add-In.

Aw shoot – that’s a serious pain and now I’m back to square one. (Part 3 of this series provides more details about shims.)

Miscellaneous notes

Here is some helpful info from Paul Kimmel. This goes beyond IDTExtensibility2 and on to other interfaces for handling application events.

Phil Wilson provided more details than most people would want – but it’s helpful for deployment.

This article on "Registration-Free COM" has the following enticing notes: "Visual Studio 2005 introduces a new feature that allows you to isolate any given COM component with the simple flip of a switch. It works by automatically generating a manifest from the component’s type library and component registration. Therefore, it is important to note that isolating a COM component requires that it be registered on the developer’s machine. The requirement to register the component on end-user machines is removed. Every COM reference in Visual Studio 2005 has a new property called Isolated. By default, this property is False, indicating that it should be treated like a normal, registered COM reference. If this property is True, it causes a manifest to be generated for the component at build time. It also causes the corresponding component files to be copied to the application folder…"

I’m going to start checking that out to see if it solves the problem I’m having registering components on end-user workstations.

I mentioned getting two addins to talk to one another in my previous blog on this topic. This article discusses this topic without the use of remoting.

This Microsoft article is funny – it’s quoted in the MSDN forums once in a while but only provides a hint at the complexity of Office add-ins with managed code. One thing I did get out of that article and other sources is that to use COM interoperability, you must have administrator or power-user security permissions. That’s pretty scary as I have no control over whether any workstation running my code is going to be in the power-user group.

Summary

At this point I’ve tried numerous ways to get an Excel function to execute managed code via an automation add-in. My development system works fine on every build. I create a setup/MSI deployment package and everything works fine on another system I use for development/testing. On a non-development workstation the installer makes the component available to the automation add-in list and I can add the component to Excel, but Excel does not see the functions exposed by the C# code. I believe I had it working once but not after that. Sigh – I’ve wasted an entire week on this and I need to move on…