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
UDF
Argument
Data Type
Notation
Double
String
Boolean
Empty
Tries to cast;
Byte
and
Sbyte
return
#
NUM
0
Numeric
#VALUE
#VALUE
String
#VALUE
String
#VALUE
String.Empty
Boolean
#VALUE
#VALUE
Boolean
False
DateTime
Double*
#VALUE
#VALUE
#VALUE
Object
Boxed double
Reference to a string
Boxed Boolean
Null
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
.
