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

• Anonymous
  April 12, 2006
  Excel Services provides ways of doing Session-Scoped and Global-Scoped caching in your UDFs. This post explains how these work.
• Anonymous
  May 23, 2006
  Part 1 of 2 - How to partially simulate Real-Time Data in Excel Services
• Anonymous
  August 03, 2006
  I have made a few posts about UDFs over the past couple of months. One of the things I neglected to explain...
• Anonymous
  September 01, 2006
  Shahar Prish, one of the developers on the Excel Services team, has recently posted a few entries on...
• Anonymous
  September 28, 2006
  Instead of re-hashing information I've found elsewhere I figured a pre-reqs post would be good.
  One...
• Anonymous
  October 30, 2006
  Instead of re-hashing information I've found elsewhere I figured a pre-reqs post would be good. One of