The following table shows the pairing between an Excel type value that is passed to a UDF argument and a supported .NET Framework type argument.

The table explains how each pair behaves, that is, what gets passed to the UDF in the argument. If a pair is not supported, the table shows the cell error that is returned to Excel. If the .NET Framework type is not supported, the UDF is not called.

For example, if you pass an Excel Double value type to an Excel Services UDF argument for the DateTime value type, the table shows how Excel Services converts the Excel representation of date/time into the .NET Framework representation. In this example, Double is converted to DateTime.

As another example, the combination of a UDF argument of type DateTime with an Excel cell of type Error (a cell that contains #NUM, #VALUE, #DIV/0, and so on) returns a #VALUE error as the UDF return value, because the Excel cell and the UDF argument type are incompatible.

Note:
Boxing and unboxing enable value types to be treated as objects. For more information about boxing and unboxing, see Boxing and Unboxing.

Table 1. Supported Excel types and UDF arguments

1. Types are in the System namespace. These are the only supported argument types.

2. The term "All numeric" refers to the following types: Double, Single, Int32, UInt32, Int16, UInt16, Byte, Sbyte. Specifically, Int64 and UInt64 are not supported types.

3. Date/time in Excel is a Double internally, but the representation (algorithm to encode date/time as Double) is different from the .NET Framework representation, and conversion is necessary. Therefore, UDF arguments support DateTime as an explicit type, and Excel Services convert any Excel Double into a .NET DateTime when the argument is of type DateTime (assuming that the Excel Double is actually a date/time value).

4. Scalar object arguments are not supported; only one-dimensional or two-dimensional object arrays are supported (see the "Arrays and Ranges" later in this article). This table row defines only how each cell in the range that is passed into the array is handled.

The following type errors can occur:

• Conversion errors   If, because of its size, a formula value that is passed into a UDF argument cannot be converted to the argument type, the UDF call fails, and a #NUM! error is placed in the result cell, cells, or formula.

• Type mismatches   If a type mismatch occurs between the formula value and the argument type, the UDF call fails, and a #VALUE! error is placed in the result cell, cells, or formula. A type mismatch means that the Excel/.NET Framework type pair is not supported by Excel Services. The same is true for a value mismatch, for example, if type conversion fails (for example, for DateTime) because the value cannot be converted to the destination type.

• Unsupported type errors   If a UDF argument is of an unsupported type, the UDF call fails, and a #VALUE! error is placed in the result cell, cells, or formula.

Using Excel Services UDFs, you can work with the following arrays and ranges:

• Object arrays   For Excel Services UDFs with one-dimensional or two-dimensional Object array arguments, only arrays of type Object are supported; arrays of strong types are not supported. In Microsoft Visual C#, object[] and object[,] arguments are supported.

• Contiguous Excel ranges   Only contiguous Excel ranges can be passed as arrays.

• Scalar argument   The type Object is supported only in array arguments, not in scalar arguments.

• One-dimensional arrays   One-dimensional arrays are treated as two-dimensional arrays with a single element in the other dimension. They can receive a range of cells in a single row in Excel, that is, their index represents a column number.

• Two-dimensional arrays   Two-dimensional arrays can receive an Excel range, such that the first index designates a row and the second index designates a column. For more information, see Multidimensional Arrays (C# Programming Guide). In Visual C#, for example, if a two-dimensional argument array is defined as int[,] a, then you would index it as a[row,col].

• Passed single value   If you pass a single value, an array is created, with the size of (1) or (1,1), and the value is packed in it.

• Passed range   Excel Services copies the range contents into an array that fits the description. If the rank (dimensionality) of the array argument does not fit in the dimensionality of the range that is passed in, #VALUE is returned.

  Note:
  A single cell always fits within a one-dimensional or two-dimensional array. A one-dimensional array always fits within a two-dimensional array.

Excel Services supports UDFs that define parameter arrays.

Note:
Parameter arrays enable you to pass a variable number of scalar arguments that appear to the method as elements of an array.

Parameter arrays can only be typed (as the parameters) or as an object-array type, as shown in the following example.

int ElipsisMethod1(params int[] intparams)
int ElipsisMethod2(params object[][,] arrayparams)

The following table defines the supported UDF return value types. For each supported type, Excel Services passes a VARIANT of a certain type to the Excel cell or formula. The VARIANT type is defined in the following table. Excel then sets the VARIANT value into the cell or formula.

Table 2. Supported UDF return value types

1. Types are in the System namespace. These are the only supported return value types.

2. The term "All numeric" refers to the following types: Double, Single, Int32, UInt32, Int16, UInt16, Byte, Sbyte. Specifically, Int64 and UInt64 are not supported types.

For an unsupported return value type, or an unsupported return value, the UDF call fails, and a #VALUE! error is placed in the result cell, cells, or formula.

You can work with the following arrays and ranges:

• Object arrays   Excel Services support UDFs that return a one-dimensional or two-dimensional .NET Framework array. Such UDFs can be used in array formulas.

• Single cells  As with most arrays or ranges returned in Excel, if applied to a single cell, the first value in the array (index 0 for one-dimensional arrays and indexes 0,0 for two-dimensional arrays) goes into the cell.

• One-dimensional arrays   One-dimensional arrays are treated as two-dimensional arrays with a single element in the other dimension. These arrays map to a range of cells in a single row in Excel, that is, their index represents a column number.

• Two-dimensional arrays   Two-dimensional arrays map to an Excel range, such that the first index designates a row, and the second index designates a column. For example in Visual C#, you would define a two-dimensional array to be returned by a UDF in the following way.

  C#

  int[,] a = new int[nRows,nCols];

Danny Khen is a program manager on the Microsoft Office Excel team. He worked on the programmability functionality for Excel Services in Office SharePoint Server 2007.

Shahar Prish is a senior developer on the Excel Services team. He blogs (Blog: Shahar Prish) about Excel Services, among other subjects.