Adding an optional argument

The need for optional arguments

At the end of the last section, it was noted that changing the interface of our HISTVOL add-in function was inconvenient for existing users of the function. Existing spreadsheets which used the function would be broken.

The old release of HISTVOL only expected one argument, but the new one expects two, a series and a number. If a numeric argument is omitted, as will be the case with all cells which use the old release of HISTVOL, then Excel passes the value zero instead. We need to do something to make sure that the existing cells get the same result as they received using the old release.

We have two choices for implementing optional arguments:

1. Rely on Excel to set the value to zero. We will need to add code to change the value of DaysPerYear to 250 if (and only if) it is equal to zero. This is an acceptable solution in this case, because zero is an illegal value for this argument.
2. Use a COper argument, and test it ourselves, using the COper::ReadOpt() method, which can detect missing or empty arguments.

Using zero as a default

The first method is very easy to implement. You could just add a test to our latest implementation, as follows:

extern "C" __declspec( dllexport )
LPXLOPER HISTVOL(const COper* Prices, double DaysPerYear)
{
    CXlOper xloResult;
    BOOL bOk = TRUE;
    std::vector<double> vecPrices;
    bOk = bOk && Prices->ReadVector(vecPrices, "Prices", xloResult);
    if (!bOk)
        return xloResult.Ret();
//}}XLP_SRC

    double dHistVol;
    if (DaysPerYear == 0.0)                                                       
        DaysPerYear = 250.0;                                                      
    if (CalcHistVol(vecPrices.begin(), vecPrices.size(), DaysPerYear, &dHistVol))
        xloResult = dHistVol;
    else
        xloResult = xlerrNum;
    return xloResult.Ret();
}

However, in some cases, this is not an acceptable solution. It may be that zero is a perfectly legal value, or that zero is not the default value.

Next: Using COper for optional arguments >>