Client COM Addin UDFs and Server UDFs issues with B2TR

In previous posts, I discussed a way of making server-side UDFs work on the client. This will work on pre-release versions of Office Server - to some extent.

There's a problem we found with publishing workbooks that use COM addins and then trying to use those on the server.

To illiustrate, take a workbook that, on Excel client, uses a COM Addin UDF called MyRand(). On the client, in cell A1, type in:

=MyRand() + rand() 

The result will be whatever the COM addin returns. Now publish this to the server (and make sure the server has a UDF called MyRand installed). Instead of seeing a result, you will see #NAME.

The problem is that Excel decorates the UDF with the progid - if you look at the formula bar after re-opening the file in Excel, instead of seeing =MyRand() + rand(), you will see something like:

=DllName.ClassName.MyRand() + rand()

To work around the problem, developers will need to take the following steps:

1. Disable the addin
2. Reboot Excel
3. Reload the file
4. Search and replace all instances of "DllName.ClassName.MyRand" with just "MyRand".
5. Republish the workbook to the server.

The good news: We are aware of just how big time this sucks - because of that we took a fix for this problem today - Excel Services will translate the prog-id preceeded function name to the naked function name, allowing the call to succeed.

The bad news: This fix will only be available at RTM (if nothing changes between now and then - these things are always somewhat fluid and changes still can occur).