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.
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:
If all three of these are true, the method will be considered a candidate by Excel Services to be a User Define Function.
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:
Return Type / Parameter
Empty cells will be coerced to empty strings
All primitive numeric types except for Int64 and UInt64
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.
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
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:
int Udf(int i)
int Udf(int iarray)
Unsupported (array of Ints only supported on return values)
int Udf(object array)
int Udf(DateTime time)
string Udf(int i)
object[,] Udf(object[,] array)
double Udf(string st, params string args)
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)
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:
All Numeric Primitives (Except for 64bit ones)