Microsoft Office Tutorials and References
In Depth Information
User-Defined Functions (UDFs)
Parameter Arrays as Arguments
You can also use a parameter array argument to get values into a UDF. This approach provides the
flexibility of passing in a variable number of scalar arguments (such as an int type) or as an object-array type.
A one-dimensional parameter array can receive values or single cell references. The following
ReturnNumberOfCellsReceived UDF method has a single params array argument ( xlCells ) and
returns an integer that represents the number of cells passed in through the params array argument:
public int ReturnNumberOfCellsReceived(params int xlCells)
In Excel, you call the ReturnNumberOfCellsReceived method by entering the following code into a
cell. Excel evaluates the method as #NAME? . The example noted here provides one value ( 6 ) and two cell
references ( F2 and E5 ) as the arguments being passed in, and 3 is the return value that represents the
number of items (or cells) passed in from Excel.
A second way to use parameter arrays as arguments is to create a two-dimensional array argument that
can receive multiple ranges. The following ReturnCountOfCellsReceived UDF method has a
twodimensional object array argument ( xlArray ), and returns an integer that represents the number of cells
in the items passed in from Excel.
public int ReturnCountOfCellsReceived(params object[,] xlArray)
int elements = 0;
for (int x = 0; x < xlArray.Length; x++)
elements += xlArray[x].Length;
In Excel, you call the ReturnCountOfCellsReceived method by entering the following code into a
cell. Excel evaluates the method as #NAME? . The example noted here provides one value ( 6 ), a cell
reference ( F2 ), and a range ( G2:H3 ) as the arguments being passed in, and 6 is the return value that
represents the number of cells passed in from Excel.
Return Data Types
The supported UDF return 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 supported return types, as
well as the behavior that you can expect from combinations of UDF return types and Excel types. The
first column represents the UDF return data type. The second column represents the Excel behavior.