Mine Field of Excel Development Tools – Part 1

Eric Carter documented an often referenced technique to create managed (C#) UDFs via automation add-ins. It works great. Samir blogged the same technique in VB.NET. Note that this technique does not require the IDTExtensibility2 interface.

I would like to makes use of the Excel Object Model to update cells other than the formula (calling) cell with the results of the function. This prompted a renewed interest in VSTO because this isn’t ordinarily supported in a UDF, but in combination with VSTO it should be simple. The feature set documented for VSTO 2005 Second Edition includes support for application-level add-ins, rather than just document-level customizations. This led me to thinking that maybe somehow VSTO 2005 SE supports UDFs. Nopers.

This was an interesting discussion on this topic but unfortunately the question was pretty much unresolved.

It seems Eric Carter’s technique only goes one way – it makes Excel aware of the custom assembly but the assembly doesn’t have access to the Excel OM. So people suggest using IDTExtensibility2 to access the rest of the OM including workbook, document properties, formulas and data in other cells, etc. So why do I have VSTO 2005 SE? Grrrrr…

Another way to get a UDF to call managed VSTO code is through VBA. Paul Stubbs documented this frequently referred-to process. Here the managed code gets registered as an Excel addin. When Excel fires the workbook Open event the managed code gets the event and registers a ComVisible class for callback. VBA gets that callback registration and stores a reference to the managed class. When a request is made for a function in the class using =func(), a VBA func() is execute, and that proxies the request to the managed object.

The problem is that VBA code is required to interact with the managed assemblies. Peter Jausovec provided some managed code to generate the VBA code proposed by Stubbs. So far I think this is the best approach for doing this sort of thing – but I’m still not sure. The problem with using VBA at all is that the developer has no idea what security end-users will have, and you can’t digitally sign VBA code that’s generated at run-time. Denis Pitcher published great info about generating the VBA via reflection and tweaking the registry to avoid security at runtime. All of this is very clever but really undesirable in a commercial offering. In my mind this VBA interface is a cludge, and a registry tweak just doubles the pain. My sentiments on this topic are well reflected in this discussion between someone with a forum ID of "Karlma", and Denis Pitcher.

Note that Eric Carter’s technique doesn’t involve VBA, but it doesn’t provide access to the Excel OM either – ya win some, ya lose some.

Andrew Whitechapel has a good article in his old blog on shimming managed add-ins. I haven’t tried implementing IDTExtensibility2 with Eric Carter’s method yet but Andrew says in that article that it "would rarely make sense" to implement IDTExtensibility2 in an automation add-in (like Eric’s). But I see inquiries about this all the time – people want a managed UDF with full access to the Excel OM. So until I can understand that statement I’m concerned about implementing IDTExtensibility2 to get the functionality I want. Will I have one in-proc COM server and one out-of-process COM server running at the same time?

The distinction that Andrew makes is that a COM addin ( implementing IDTExtensibility2 ) is used mainly for code that manipulates the UI, while automation add-ins are generally used just for new formula functions – and it seems he doesn’t see the two of these coming in the same package. Maybe I should be creating two separate assemblies, one for each purpose? Seems very MVC to me but I’m not sure what they have in mind at MSFT. A problem I still have is when a function needs to do other things with the workbook. Do I create a bridge between the automation/function-only assembly and the VSTO/OM-enabled assembly?

That last article mentions adding stuff to the registry and creating COM shims to wrap managed methods, etc. Not only is this confusing but it’s sounding cludgy again. Andrew mentions a shim wizard which I’ll come back to later.

Andrew concludes that addins with VSTO are so much easier that COM addins – but he doesn’t mention that VSTO still doesn’t support UDFs, which still leaves many of us to use COM addins and/or automation add-ins. (Granted, that last link is almost 3 years old but it still applies.) The thing that confuses me here is that VSTO 2005 SE is supposed to support application-level addins, but that doesn’t mean automation addins, so they still don’t support UDFs. Sigh. The terminology can get real confusing here and I’m sure I don’t have a full grasp on it.

To simplify the process of creating shims and registry entries and whatever else is required, Microsoft published a COM Shim Wizard. I dunno, all of this looks more complicated to me all the time. In a recent blog from Andrew, there are updated shim wizards. But the MSDN download still has the old version. Anyone from MSFT wanna update that?

2 thoughts on “Mine Field of Excel Development Tools – Part 1

    • The article covers a lot of things and includes a lot of useful links..Thanks for all those informations..

      Simply my question is there any way to provide function description , argument description etc to the function wizard from excel automation addin using c#.(MSDN answers NO..but all those are before 2005. ). Or is it possible to add a drop down list or check box control in function wizard(instead of text box)?

      Pls share if you have any ideas

    • Sorry for getting to this so late, I accidentally thought this comment was spam.

      I highly recommend you check with the people at Add-In Express to see if you can do what you ask to extend Excel. Please post here when you get an answer. Good luck!

      http://www.add-in-express.com/

Leave a Reply