Making Excel Services UDFs work on Excel 2007 - Part 1

I have made a few posts about UDFs over the past couple of months. One of the things I neglected to explain is how to make server targeted UDFs work on the client. Among other reasons why one would want that, is the fact that it’s useful to actually see what the results of your workbook is before you publish it to the server.

This series of posts will concentrate on one of the ways that this can be achieved. While there are ways that are more efficient (perf wise), the one I will present is probably the easiest to do yourself.

 

This post will show how to modify a simple UDF that only takes strings, dates or primitives as parameters and so that it works on the client. The second post in the series will show how more elaborate UDFs (those that take ranges for example) can be modified to work on the client. The third will show one way of supporting volatile UDFs on the client.

 

For this example, lets take a UDF that returns the n’th element in a delimited string. The following call, for example, will return “fox”:

=GetNthWord(“The quick brown fox got hit by a truck”, 3, “ “)

The first parameter is the string we are parsing. The second one is the zero-based index of the word we are looking for and the third parameter is the delimiter we are using.

 

This would be a potential implementation of this UDF:

 

namespace NewUdf

{

       [UdfClass]

       public class Class1

       {

[UdfMethod()]

public string GetNthWord(string sentence, int index, string delimiter)

{

              if (delimiter.Length > 1)

              {

              throw new InvalidOperationException();

              }

             

              string[] splat = sentence.Split(delimiter[0]);

              if (splat.Length <= index)

              {

              throw new InvalidOperationException();

              }

             

              return splat[index];

}

       }

}

 

(Note that this could be made to be slightly more efficient by only asking the .Split() method to do so until the Nth string)

 

Now, as you know if you ever did Excel Services UDF programming, using this UDF inside Excel would result in a #NAME error. However, by changing the file to support COM Interop, it is possible to make it usable by Excel. There are four relatively easy steps that need to be done:

 

1. Decorate the class with attributes so that it can be used by COM:

[UdfClass]

[Guid(Class1.ClsId)]

[ProgId(Class1.ProgId)]

[ClassInterface(ClassInterfaceType.AutoDual)]

[ComVisible(true)]

public class Class1

{

       const string ClsId = "D08A3087-F858-4103-8C52-66B2D2898346";

       const string ProgId = "SampleUdf.Class1";

       //...

}

 

Note: It is important to specify the ClassInterface attribute with AutoDual or Excel will not be able to use the UDF class.

 

2. Make sure that the “Programmable” key is added to the registration of this class in the COM infrastructure. If you don’t do that, you won’t be able to make Excel recognize the UDF class. This is done by adding the ComRegisterFunction and ComUnregisterFunction attributes to the class:

[ComRegisterFunction]

public static void RegistrationMethod(Type type)

{

       // Only add stuff to the registration

       // if it's this class that's being registered.

       if (typeof(Class1) != type)

       {

              return;

       }

       // Add "Programmable" under our key.

       RegistryKey key = Registry.ClassesRoot.CreateSubKey(

              "CLSID\\{" + ClsId + "}\\Programmable");

       key.Close();

}

[ComUnregisterFunction]

public static void UnregisterationMethod(Type type)

{

       // Only add stuff to the registration

       // if it's this class that's being registered.

       if (typeof(Class1) != type)

       {

              return;

       }

       // Add "Programmable" under our key.

       Registry.ClassesRoot.DeleteSubKey("CLSID\\{" + ClsId+ "}\\Programmable");

}

These two functions will be executed by the tool that makes assemblies usable by COM upon registration.

3. We also need to register the assembly for COM usage. To do that, we will make use of the regasm.exe command line tool which is located in your C:\WINNT\Microsoft.NET\Framework\v2.0.50727 directory (the name of the directory may vary of course). To register the assembly, run the following command:

regasm /codebase NewUdf.dll

You will get a warning which you can ignore for the purposes of this post. The UDF is now registered and ready for use in Excel.

4. In Excel 2007 use the File menu to choose the “Excel Options” button. In the dialog that comes up, choose the “Add-Ins” section and at the bottom, make sure “Excel Add-ins” is selected in the combo box at the bottom of the dialog and click “Go”.

Another dialog (“Add-Ins”) will come up. Click the “Automation…” button to bring up a list of automation servers.

The “Automation Servers” dialog will contain a huge list – search for your automation add-in there. The name will be [Namespace].[ClassName] (in our case, NewUDF.Class1).

Select that automation server and click “OK”. You will get a warning message about mscoree.dll – ignore it and click “No”.

Excel should now be ready to use the newly created UDF. Typing that same formula that we did before should now result in the word “fox” in the cell.

In the next post, I will talk about how to make UDFs that expect object[] or object[,] work.