Microsoft Office Tutorials and References
In Depth Information
User-Defined Functions (UDFs)
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.
Excel Data Type
Tries to cast; Byte
and Sbyte return
Reference to a string
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 .