UDF Rules and guidelines


In a previous post, I showed an example of how to create a DNS lookup User Defined Function (UDF) for Excel Services. It is important to understand the various limitations of UDFs so that one has fewer surprises when writing them.


In this post, I will discuss the various supported UDF signatures. I will also try to talk a bit about the rational behind them.


There are three levels of checks that are made on UDFs. The first is done in the discovery phase, in which Excel Services looks for methods that are candidates. Next, it checks all the candidates for their signatures to make sure they are compatible. Lastly, when executing the functions, Excel Services will make sure the call is compatible with the signature of the UDF.


Candidate Functions


As discussed in the previous post, for Excel Services to recognize that a method is a UDF, that method needs the following things going for it:



  1. It needs to be in a .NET 2.0 Assembly*.
  2. The class needs to be inside a public non-abstract class which has the UdfClassAttribute applied to it.
  3. The class needs to have a parameterless constructor.
  4. The method itself needs to be public, non-abstract and have the UdfMethodAttribute applied to it.

If all three of these are true, the method will be considered a candidate by Excel Services to be a User Define Function.


Signature Test


Once a method has been determined to be a candidate, Excel Services will check its signature to make sure that it can actually be called by it. The following table discusses the various .NET types we support in Excel Services UDFs:




































.NET Type


Return Type / Parameter


Notes


String


Both


Empty cells will be coerced to empty strings


Boolean


Both


 


All primitive numeric types except for Int64 and UInt64


Both


 


Object


Return Type


Object parameters are not supported – but you can achieve the same thing by having an object[] parameter – Excel Services will do the translation from a single cell to a single-item array.


Object[], Object[,]


Both


 


DateTime


Both


Excel Services will use the same rules to transform doubles to Dates as Excel Client does.


Array of Strings,
Array of Booleans,


Array of supported primitives,
Array of DateTime


Return Type


 


 


Only methods that follow these rules will be eligible for getting called from Excel Services.


Note that Excel Services also supports param-arrays (params keyword in C#), these param arrays need to follow the same rules. The following tables shows a few examples of valid and invalid Excel Services UDFs:


































UDF


Supported?


int Udf(int i)


Supported


int Udf(int[] iarray)


Unsupported (array of Ints only supported on return values)


int Udf(object[] array)


Supported


int Udf(DateTime time)


Supported


string[] Udf(int i)


Supported


object[,] Udf(object[,] array)


Supported


int[] Udf(object[])


Supported


double Udf(string st, params string[] args)


Supported


double Udf(string st, params object[][,] arrays)


Supported – this UDF needs to have a string as its first parameter, and then has a variable list of parameters, each can be a range in Excel, so, the following call from an Excel cell will succeed:


=Udf(“Hello”, A1:C7, Z9:ZZ99)


 


Coercion test


When a UDF is finally called, Excel Services make sure that the parameters passed to it are compatible and can be coerced. Note that Excel Services is more constrictive than Excel Client in what type conversions it does or does not allow. If you find these constrictions problematic, simply make sure your methods take an object array and let Excel place the native value into it, and then you can go ahead and make the conversions.


The following table maps Excel Types to .NET Types:














































ExcelType/
.NET Type


Excel String


Excel Boolean


Excel Double


Excel Range


(Multicell)


String


Supported


 


 


 


Boolean


 


Supported


 


 


All Numeric Primitives (Except for 64bit ones)


 


 


Supported


 


DateTime


 


 


Supported


 


Object[]


Supported


Supported


Supported


Supported


Object[,]


Supported


Supported


Supported


Supported


 

All other Excel Types are not supported (for example, the error type in a cell). If an unsupported type or conversion exists, Excel Services will not even call your UDF, it will immediately place a #VALUE error in the cell.

Comments (6)

  1. Excel Services provides ways of doing Session-Scoped and Global-Scoped caching in your UDFs. This post explains how these work.

  2. Part 1 of 2 – How to partially simulate Real-Time Data in Excel Services

  3. I have made a few posts about UDFs over the past couple of months. One of the things I neglected to explain…

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

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

    One…

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