Back in my first blog posting on WSS 3.0 and MOSS 2007, I discussed Excel Services and User Defined Functions. Since then I have had a number of requests for a follow up post. A common misconception from students in my SharePoint developer courses is this:
Popular Misconception #1: “I can’t test my UDFs in Excel! When I write a UDF class and method, my spreadsheet works on the server, but I get #NAME? showing up when I try to build my spreadsheet in Excel on my desktop.”
This is kind of true. A UDF is just a .NET assembly that has bee appropriately attributed to mark the Class and Methods as discoverable and usable by Excel Services. You deploy this to Excel Services by installing it in the GAC on the server and registering it in the Shared Service Provider (SSP) for Excel Services.
Excel Client 2007, on the other hand, does not know what to do with these .NET assemblies. You can try adding these .DLLs as Excel Add-Ins or COM Add-Ins and Excel will complain that your assembly is not a valid add-in.
So how do we work around this? Some say you can wrap your .NET assembly in a .XLL library written in C++. Some say there are third party wrappers that will do this for you at a premium.
You don’t need to go that far.
Let’s have a look at a simple (trivially so) UDF for Excel Services.
Very, very simply, this marks the class as a UdfClass, with a single UdfMethod that takes a double and returns the value incremented by one. This can be signed and deployed into Excel Services using the method described in my previous post on Excel Services and UDFs.
If I now want to deploy this same assembly into Excel Client 2007 as an add-in, I could add a few lines of COM registration code. Once again, don’t forget to sign your assembly!
I’d then need to register the assembly with RegAsm.exe (from the %SystemRoot%\Microsoft.NET\Framework\v2.xxxx folder).
RegAsm.exe /codebase UDFExample.dll
Finally I’d add it to Excel as a Excel Automation add-in. Under Excel Client 2007, this is available in File –> Excel Options –> Add-Ins, then select Manage: Excel Add-ins.
Next, select Automation and find your registered Add-in class.
This *can* cause Excel to complain with the following error:
It’s important that you say “no” to this.
Then you can start using your UDFs in Excel as if they were built in functions.
If you’d like the Visual Studio 2008 solution for this example, you can download it here: