Microsoft Office Tutorials and References
In Depth Information
User-Defined Functions (UDFs)
UDF Return Type
Excel Behavior
Cast to double
Numeric
String
String
Boolean
Boolean
DateTime
Recognizes the Double as a DateTime
Object[] Type[]
Array (first value goes into the first cell, and so on)
Array (first value goes into the first cell, and so on)
Object[,] Type[,]
Object
Excel tries to map to one of the types noted above and handles it
accordingly
Object(Null)
Empty/ Null String
The Numeric data type refers to the following System namespace types: Byte, Double, Int16, Int32,
Sbyte, Single, UInt16, and UInt32. The Int64 and UInt64 types are not supported.
Returning a Range
In addition to returning single-valued data types (also referred to as scalar data types ), a UDF can return
one- or two-dimensional arrays. Only object arrays are supported; strong typed arrays are not.
A one-dimensional array can hold a range consisting of a single row. The following UDF method has a
single object array argument (xlRow) and returns an object array that represents the object that was
passed in. A row is passed in and the same row is returned.
[UdfMethod]
public object[] Return1dObjectArray(object[] xlRow)
{
return (xlRow);
}
In Excel, you call the Return1dObjectArray method by entering the following code into cell C7. To
create the array formula, select cells C7:F7, press F2, and then press Ctrl+Shift+Enter. Excel automatically
inserts the formula between {} (curly braces). The C7:F7 range represents the cells where the object[]
returned from Return1dObjectArray is applied. Excel evaluates the method as #NAME?. The example
noted here uses E5:H5 as the argument to pass in. After Return1dObjectArray returns, C7:F7 contains
the same values as E5:H5.
=Return1dObjectArray(E5:H5)
A two-dimensional array can receive a range that spans one or more rows. The following UDF method
has a single two-dimensional object array argument (xlRange) and returns a two-dimensional object
Search JabSto ::




Custom Search