XLL+ Class Library (6.3)

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 the XLL+ Function Wizard to write code that will test the input and set it to the default value if it is missing.

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:

CopyC++
CXlOper* HISTVOL_Impl(CXlOper& xloResult, const CXlOper* Prices_op, double 
    DaysPerYear)
{
    // Input buffers 
    std::vector<double> Prices;
    // Validate and translate inputs
    XlReadVector(*Prices_op, Prices, L"Prices", XLA_TRUNC_ONEMPTY|
        XLA_TRUNC_ONBLANK);
    // End of generated code 
//}}XLP_SRC 
    if (DaysPerYear == 0.0) 
        DaysPerYear = 250.0;
    try
    {
        xloResult = CalcHistVol(Prices, DaysPerYear);
    }
    catch(const char*)
    {
        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: Creating optional arguments >>