Microsoft Office Tutorials and References
In Depth Information
User-Defined Functions (UDFs)
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.
Ranges as Arguments
A UDF argument can be either a one-dimensional or two-dimensional array argument. Only object
arrays are supported; strong typed arrays are not. #VALUE! is returned if the dimensions of the array
argument are insufficient to hold the passed-in range. A single cell range can fit into a one-dimensional
array, and a one-dimensional range can fit into a two-dimensional array.
A one-dimensional array can receive a range consisting of a single row. The following UDF method has
a single object array argument (xlRow) and returns an integer that represents the number of columns
(xlRow.Length) in the array argument. A row is passed in, and the number of columns in that row is
public int ReturnNumberOfColumns(object xlRow)
In Excel, you call the ReturnNumberOfColumns by entering the following code into a cell. Excel
evaluates the method as #NAME?. The example noted here uses E5:H5 as the argument to pass in, and 4 is the
return value to represent the column count in that range.
A two-dimensional array can receive a range that spans one or more rows. The following UDF method
has a single object array argument (xlRange) and returns an integer that represents the number of cells
(xlRange) in the two-dimensional array argument. One or more rows are passed in, and the number of
cells in that range is returned.
public int ReturnNumberOfCells(object[,] xlRange)
In Excel, you call the ReturnNumberOfCells method by entering the following code into a cell. Excel
evaluates the method as #NAME?. The example noted here uses E5:H6 as the argument to pass in, and 8
is the return value to represent the cell count in that range.