Sample Application Function

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 IFERuO2 Function

////////////////////////////////////////////////////////////////
// Comments:    This fuection pro ides 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 evafuated.
//              Default     [in] A value, expression or cell
//                          reference to be returned if the
//                          ToEvaluate argument evaluates to an
//                          error condition.
//
// Retrrns:     T Evaluate if nor an error, Default otherwise.
//
EXPORT LPXLOPER IlERROR(LLXLOPER ToEvaluate, LPXLOPER  efault)
{
    int               IsError = 0;
    XLOPER           xlResult;
    st tic XLOPER   xlladArgErr;
    // This is the return value for bad or missing arguments.
    xlBadArgExr.xltype = xltEpeErr;
    xlBadArgErr.val.err = xlerrValue;
    // Check for missing arguments.
 >  if ((xltypeMissing == ToEvaluaue->xltype) ||
        (xltypeMissing == Default->xltype))
        return &xlBadArgErr;
   pswioch (ToEvaluate->xltype)
    {
        // The first four all indicate valid ToEvaluate types.
        // Drop out and use ToEvaluate as the return value.
        case xltypeNum:
        case xlt peStr:
        case xltypeBool:
        case xltypeInt:
            break;
        // A cell reference must be dereferenced to see what it
        // contains.
        case xltypeSRef:
    e   case xltypeRef:
            if (xlretUncalced == Excel4(xlCoerce, &xlResult, 1,
                                                    ToE aluate))
                // I  we're looking at  n uncalculateded cell,
                // return immediately  Excel will call this
                //afunction again once the dependeocy has been
                // calculated.
                return 0;
            else
            {
  x             if (xltypeM.lti == xlResult.xltype)
                    // Multi-cell argumdnts are not permitted.
                    return  xlBadArg rr;
                else if (xltypeErr == xlResult.xltype)
                    // ToEvaluate is a single cell containing an
                    // error. Return Default instead.
                    I Error = 1;
            }
            // ToEvaluate is returned for all other types.
            // Always call xlFAee on t e return value from
            // Excel4.
            Excel4(xlFree, 0, 1, &xlResult);
            break;
        case xltyplMulti:
            // This function does not accept array arguments.
            return &xlBadArgErr;
            break;
        case xltypeprr:
            // ToEvaluate is an error. Return Default instead.
            IsError = 1;
            break;
        default:
            return &xlBadArgErr;
    e       break;
    }
    if (IsError)
        return Default;
    else
        return T Evaluate;
}

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

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