Mine Field of Excel Development Tools – Part 1

User-Friendly

Once we generate a UDF a user will expect to it to show up in the function browser – that’s the dialog that pops up when you click the "fx" button in the toolbar. The ExecuteExcel4Macro method to make this happen is documented here. Another technique by "keepITcool" improves on that as documented here. Again, while all very clever, these techniques are unsupported cludges that rely on what could be bugs. Microsoft may do something at any time to invalidate code that relies on the current (mis)behaviors.

It doesn’t seem like there is a good way to use VSTO to integrate custom features into the UI along with UDF code in the same managed module. Other people in the forums are obviously expressing similar needs, and for a couple years Microsoft people have acknowledged the demand from the field.

I get the idea that we may need to register two automation add-ins, one for VSTO and one for UDFs, and then maybe use remoting to communicate between them. Add-ins rely on COM and the Primary Interop Assemblies to marshal transactions between managed code and unmanaged software like the MS Office applications. This is very slow – using remoting to communicate between two add-ins isn’t going to help – and don’t forget that my application uses web services. If it takes more than 500ms on average to process any given transaction, this isn’t going to be very user-friendly. Sure I can make the comms asynchronous and multithreaded, etc, but that’s not going to help all that much.

Here is a good summary by "Jocular Joe" about using addins and VSTO together. Unfortunately this is too complex for deployment outside of controlled environments.

Related stuff

As I was researching this topic I came across some articles that I know I will be using.

For example, since I’m calling web services and I don’t want to trigger a remote call on every calculation, I need to be careful about volatility. Here is some great info by Charles Williams about using Application.Volatile to avoid recalculation – this is really important with time-intensive calls to web services.
 
Related, Andrew Whitechapel has good info on Volatile Managed UDFs in his old blog. In his new blog, Andrew discusses passing Excel data types in automation add-ins. That’s great info if you want to pass ranges to/from a UDF.

It’s also helpful to identify the worksheet/cell that fired a change event. (See also) Note that the Active cell is the one that triggers worksheet recalculation, but the Current cell is the one that has the formula which is currently being recalculated – this Current cell could be in a different worksheet than the Active cell. Related, here is an excellent tip on how to get the workbook that called a UDF – again, the Calling wb, not Active wb.

This forum posting discusses async retrieval and storage of data from a UDF – to avoid repeated calls for the same remote data. Related, I was thinking about storing data and/or formulas in other workbooks so that individual workbooks didn’t need to store all info. I backed away from that idea quickly but found some good info on getting data from closed workbooks.

Third-Party Solutions

To get around some of the issues documented here, there are third-party solutions, and I need to consider these since VSTO still seems like it won’t provide the functionality that I need. One of these solutions is called ManagedXLL. As I understand it, all you need to do in your managed code is to use an Attribute to decorate a function which is to be exposed as a UDF.

And an open source project called ExcelDNA is trying to provide similar functionality to ManagedXLL – but of course this is free and open source (FLOSS). I dunno, that one sort of scares me because it doesn’t look like it’s been updated since last March.

OK, that’s my story and I’m sticking to it. Please feel free to edumacate me here I this blog, or take me back to the Microsoft forum and flog me. I’m really hoping that someone at Microsoft will recognize what a mine field this is for people out here in the wild, and that maybe someone will summarize the real state of the art to correct my feeble lack of understanding. If you don’t have the tools available now, please publish something current that tells us what we need to do until something better comes along. I’ve had to search forums and google through web sites and (oh my God) read through some books to get some idea of what’s going on here. Most of the information on this topic (and so many others), whether erroneous or incomplete, is a couple years old. It’s difficult to know what’s old and invalid from what still applies, and then we have to put the puzzle together ourselves. I’ve spent days to weeks on this and I know other people have spent weeks to months navigating this mess. We’d much rather spend our time in productive code than guessing about how to wire up software components.