Excel Services User-Defined Functions (UDFs)

by Craig Thomas

Excel Services UDFs represent another tier in the Excel Services capability to support custom solutions. UDFs provide a means to call custom-managed code functions from within a workbook. Without UDFs, a workbook is restricted to using only the intrinsic Excel functions. With UDFs, custom functions can be called from the workbook as well.

UDFs are very instrumental to custom server solutions because Excel Services does not support loading workbooks that contain code behind (VBA). Nor does Excel Services support the Excel client add-ins used by workbooks to extend functionality. With the right UDF-managed wrapper solution, you could leverage existing custom client solutions on the server, but that topic isn't discussed here.

This article describes how to author manage UDFs and deploy them to the server to make them available to workbooks that are loaded from the trusted file locations. An example solution is provided to demonstrate the authoring and building of a UDF assembly. Additional material is provided to explain how the workbook interacts with UDF methods, and how to pass and return arguments to the workbook.

UdfMethodAttribute Class

Each public method in the public UDF class must have the [UdfMethod] attribute if the UDF is to be treated as a public UDF. The UdfMethodAttribute has two Boolean properties: IsVolatile and ReturnsPersonalInformation.

The IsVolatile property has a default value of false. When set to true, the UDF method is treated like an Excel volatile function. A volatile function always calculates when any part of a workbook needs to be calculated. UDF volatile methods are called when the Volatile Function Cache Lifetime setting has passed. This setting is defined on the trusted file location where the workbook was loaded.

The ReturnsPersonalInformation property also has a default value of false. When set to false, the thread's Windows identity is hidden, so all callers of the UDF method share the same results cache. When set to true, the UDF method returns results based on the identity, which ensures that callers of the method are not sharing cached values. If a UDF method is expected to return results based on the caller's identity, then the ReturnsPersonalInformation should be true so that each caller gets only their identity-specific results.

Argument Data Types

The supported UDF argument data types are in the .NET System namespace. Excel supports a smaller set of data types that can be applied to data in cells. The following table describes the behavior that you can expect from combinations of UDF argument types and Excel types. The first column represents the UDF argument data type. The remaining columns represent the Excel types that are passed into the UDF through the argument. The contents of the table indicate the error that is returned if the pair is unsupported, or what to expect if an error is not going to be returned.

Here is a further explanation of the data types in the table:

• Numeric — Refers to the following System namespace types: Byte, Double, Int16, Int32, Sbyte, Single, UInt16, and UInt32. The Int64 and UInt64 types are not supported.
• DateTime* — Internally, Excel treats dates as a double. The ECS converts a DateTime double from Excel into a .NET DateTime.
• Object — Defines the behavior for each cell in the range that is passed into the array.
• #VALUE — Can be returned for different reasons, including the following:
• The Excel type is an error, such as division by zero (#DIV/0!).
• The UDF argument is an unsupported type, such as Int64.
• The Excel and .NET type pair is not supported by ECS.
• The type conversion fails, which can occur for a DateTime type.