Share via


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