Mine Field of Excel Development Tools – Part 3
The task is to create an Excel User Defined Function with managed .NET code. It seems so far that the way to go is with shims which proxy COM requests from Excel and separate the managed code into its own application domain for security and stability. This short article summarizes how I’ve come to that conclusion.
To get started, here is an intro page to shims by Andrew Whitechapel. And here is his blog from 2005 about shimming an add-in. There he says he removes IDTExtensibility2. Later he says that in his book he left IDTExtensibility2 in. It pays to keep reading before coding – and don’t believe everything you read on the internet. 
Andrew and others worked on the COM Shim Wizard as an unsupported project, to simplify the manual steps documented earlier for us poor folk trying to replace VBA and XLLs with managed code. Three cheers for personal initiative at Microsoft! The original v2.0 Wizard was later replaced by v2.3, with support for Office 2007. Get info here about the v2.3 COM Shim Wizard. While a little confusing, that document discusses Office 2007 exclusively, but way at the top it mentions "Applies to Office 2003". We’ll see…
Go here to get the v2.3 COM Shim Wizard. If that link doesn’t work, download via link from the info page linked from the last paragraph. The download page similarly doesn’t mention Office 2003 at all, so it’s still a gamble to see if v2.3 works with Office 2003. BTW, I tried to install the v2.0 Shim Wizard package (mentioned in my last blog entries) but it failed with an exception during the install and the templates weren’t visible in VS2005. Installing v2.3 worked with no problem after v2.0 was removed (again with an exception). Hey, this stuff is unsupported, and in particular that package is 3 years old…
As a follow-up to shims, which is still largely a manual process, Andrew introduces the new Outlook Add-in Support in VSTO. There is an official introduction on the Microsoft site. In short, this add-in support intends to replace the hassle of shims while providing the same benefits and in a more elegant way. But at this point it’s for Outlook only. This "Architecture" document on the Outlook Add-in for VSTO includes good info about shims in the context of VSTO. We find here that the Outlook AddinLoader is not only better than a shim but it’s also supported. It’s interesting that this AddinLoader is constantly referenced for "Office applications" though so far it seems like it only works for Outlook. (Gosh, I could have really used this when I was hot-n-heavy into Outlook development – I’m sure I’ll get a chance to use it soon.) Obviously they’re working toward support for all Office apps.
Sure enough, this document says the Outlook Addin features have been extended to other Office products including Excel. It seems shims were replaced by the add-ins which are 1) supported, 2) handle code isolation into separate app domains, 3) manage the registry tinkering, 4) replace the mscoree.dll COM proxy with a more robust mechanism.
All that is great, but unfortunately for some reason the new VSTO add-ins still don’t expose managed functions to Excel, even through a COM wrapper. This seems to indicate we need to stick with the complicated shim process if we want managed automation add-ins for Excel UDFs. My only question now is whether I need to do this manually or whether the v2.3 Shim Wizard will minimize the burden. We’ll see in the next posting on this topic.