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



       public class Class1



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:






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:



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)





       // Add “Programmable” under our key.

       RegistryKey key = Registry.ClassesRoot.CreateSubKey(

              “CLSID\\{“ + ClsId + “}\\Programmable”);





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)





       // 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.

Comments (24)

  1. Shahar Prish, one of the developers on the Excel Services team, has recently posted a few entries on…

  2. Instead of re-hashing information I’ve found elsewhere I figured a pre-reqs post would be good.


  3. mgr says:

    Does this work in excel cleints installed outside sharepoint environment? for example on excel 2007 running on xp

  4. Shahar Prish says:

    It should, yeah.

    If you have any specific problems, let me know.

  5. stswordman says:

    hi salis.

    Could you post the screen shot  of "File menu "t and “Excel Options” ? I can’t find them in excel 2007

  6. stswordman says:

    hi salis.

    Could you post the screen shot  of "File menu "t and “Excel Options” ? I can’t find them in excel 2007

  7. Shahar Prish says:

    In Excel 2007, you will see a circle at the top right corner. It may even blink at you – it has the Office Logo on it. Click it. At the bottom of the menu that comes up there’s an "Excel Options" button. Click that.

  8. TS says:

    How did you come up with the Class ID as below, did you make it up?

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

  9. Shahar Prish says:

    You will need to generate your own using the Guidgen tool (or the UI GUID generator you can easily find in the Tools menu option in Visual Studio)

  10. TS says:

    thanks, while i still can’t get the function to work properly, I need to be able to replace the dll. How do i get the system from locking the dll, even after unregistering it?

  11. SP says:


    I have followed all th steps mentioned but while opening the excel sheet in Excel am getting following error.

    "values does not fall on the esxpected range". Is there any restrictions like we need to select the range in excel workbook exactly same as the columns and rows of sharepoint list and call the function.

    will it be a problem if i select less or more than the list dimensions.Please let me know about this as i got stuck here and not to display the list in excel workbook.


  12. Shahar Prish says:

    Can you post your code? (Just the code that builds the result you send back from the UDF)

  13. SP says:

    Thanks shahar,

    I am very happy that my problem got resolved as i was not passing parameters in calling t he UDF correctly..

  14. Hin und wieder werde ich gefragt, ob man mit VSTO auch Tabellenfunktionen für Excel schreiben kann. Nun,

  15. Jason says:

    I followed the steps (and received all of the responses that were listed, such as warnings) in this article but I still receive the "#NAME?" value in Excel?

    Any ideas on what I may have missed?

  16. Shahar Prish says:

    Not really Jason – as long as you turned on UDF for your trusted location, this should just work.

    You can look at the logs to make sure UDFs were loaded and you can also use a deugger to debug into w3wp.exe to see if your UDF is actually loaded.

  17. Jason says:

    After reading your response, I can see how my original post may not have been the most clear.

    I have the UDF working when viewing the spreadsheet on the server.  It is not working when viewing the spreadsheet on the client.

  18. Sandeep says:

    Like Jason, I get the "#NAME?" value in Excel Client.  I have followed all the steps listed in the article. Any ideas what I am missing?

  19. Shahar Prish says:

    Sandeep – what’s the signature of your UDF?

  20. Sandeep says:

    I found the problems I was having.

    1.  The AssemblyInfo.cs ComVisible property for my assembly was set to false, I had to change it to true.

    2.  Some of my methods return type was void, I had to change these to return either bool or string.

    These two fixes solved the problem.

    Thanks for your help.


Skip to main content