Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Sample Application Function

For our real-world example function, we'll create a new worksheet function called IFERROR. As noted in Chapter 5 Function, General and Application-Specific Add-ins, we often encounter the following worksheet function construct:

=IF(ISERROR(<some_long_function>), 0, <some_long_function>)

This is tedious and unwieldy, so we created a user-defined function in VBA that enabled us to accomplish exactly the same thing with the following:

=IFERROR(<some_long_function>, 0)

In this section we rewrite our IFERROR function in C. This will dramatically improve its performance because it will be compiled to native code and be able to communicate directly with Excel through the Excel C API. Our C IFERROR function has the definition shown in Listing 19-12.

Listing 19-12. The IFERROR Function

////////////////////////////////////////////////////////////////
// Comments:    This function provides a short-cut replacement
//              for the common worksheet function construct:
//              =IF(ISERROR(<some_function>),0,<some_function>)
//
// Arguments:   ToEvaluate  [in] A value, expression or cell
//                          reference to be evaluated.
//              Default     [in] A value, expression or cell
//                          reference to be returned if the
//                          ToEvaluate argument evaluates to an
//                          error condition.
//
// Returns:     ToEvaluate if not an error, Default otherwise.
//
EXPORT LPXLOPER IFERROR(LPXLOPER ToEvaluate, LPXLOPER Default)
{
int               IsError = 0;
XLOPER           xlResult;
static XLOPER   xlBadArgErr;
// This is the return value for bad or missing arguments.
xlBadArgErr.xltype = xltypeErr;
xlBadArgErr.val.err = xlerrValue;
// Check for missing arguments.
if ((xltypeMissing == ToEvaluate->xltype) ||
(xltypeMissing == Default->xltype))
return &xlBadArgErr;
switch (ToEvaluate->xltype)
{
// The first four all indicate valid ToEvaluate types.
// Drop out and use ToEvaluate as the return value.
case xltypeNum:
case xltypeStr:
case xltypeBool:
case xltypeInt:
break;
// A cell reference must be dereferenced to see what it
// contains.
case xltypeSRef:
case xltypeRef:
if (xlretUncalced == Excel4(xlCoerce, &xlResult, 1,
ToEvaluate))
// If we're looking at an uncalculateded cell,
// return immediately. Excel will call this
// function again once the dependency has been
// calculated.
return 0;
else
{
if (xltypeMulti == xlResult.xltype)
// Multi-cell arguments are not permitted.
return &xlBadArgErr;
else if (xltypeErr == xlResult.xltype)
// ToEvaluate is a single cell containing an
// error. Return Default instead.
IsError = 1;
}
// ToEvaluate is returned for all other types.
// Always call xlFree on the return value from
// Excel4.
Excel4(xlFree, 0, 1, &xlResult);
break;
case xltypeMulti:
// This function does not accept array arguments.
return &xlBadArgErr;
break;
case xltypeErr:
// ToEvaluate is an error. Return Default instead.
IsError = 1;
break;
default:
return &xlBadArgErr;
break;
}
if (IsError)
return Default;
else
return ToEvaluate;
}

The additional function table entry required to register this function with Excel is shown in Listing 19-13.

Listing 19-13. Function Table Entry for the IFERROR Function

{" IFERROR",
" RRR",
" IFERROR",
" ToEvaluate, Default",
" 1",
" Sample Add-in",
" ",
" ",
" If the first argument is an error value, the second "
"argument is returned. Otherwise the first argument "
"is returned.",
" The argument to be checked for an error condition.",
" The value to return if the first argument is an error."
}