Column groups

If you have a group of vector arguments which are closely related, and are always the same size as each other, then you may want to use a column group. The user will then see only one argument, and the wizard will generate code to split it into a number of vectors, checking that each column is present and that all columns are the same length.

Advantages of column groups

Column groups can be useful for both the add-in developer and the user.

• The total number of arguments is reduced. Since Excel has a limited number of arguments per function, combining multiple column arguments into one grouped argument may leave room for more arguments.
• The user has less arguments to worry about. Furthermore, if the columns are returned from some other function or from a database as a single result, it may be very convenient for the user to pass them on to another function as a single group.

Disadvantages of column groups

Column groups are not always a useful feature.

• The columns must be in a particular order, left to right. It may be inconvenient for the user to arrange them in this way.

Wizard-generated code for column groups

For example, take a grouped argument, PaymentSchedule, that contains two columns, as follows:

The XLL+ Function Wizard will generate code that declares a vector of the appropriate type for each column and populates it with the contents of the corresponding column of input. If the input cannot be read, then an appropriate error message will be returned.

// Function:    RevalSchedule
// Purpose:     Revaluate a stream of payments

//{{XLP_SRC(RevalSchedule)
    // NOTE - the FunctionWizard will add and remove mapping code here.
    //    DO NOT EDIT what you see in these blocks of generated code!
IMPLEMENT_XLLFN2(RevalSchedule, "RBP", "RevalSchedule", 
    "ValueDate,PaymentSchedule", "Financial", "Revaluate a"
    " stream of payments", "Date of evaluation\02 columns:"
    " PaymentDates; PaymentAmounts", "\0B()PaymentDates \0+B("
    ")PaymentAmounts \0", 1)

extern "C" __declspec( dllexport )
LPXLOPER RevalSchedule(double ValueDate, const COper*
    PaymentSchedule)
{
    CXlOper xloResult;
    BOOL bOk = TRUE;
    std::vector<double> vecPaymentDates;
    bOk = bOk && PaymentSchedule->ReadVector(vecPaymentDates, "PaymentSchedule", xloResult, 0, 0);
    std::vector<double> vecPaymentAmounts;
    bOk = bOk && PaymentSchedule->ReadVector(vecPaymentAmounts, "PaymentSchedule", xloResult, 0, 1);
    if (!bOk)
        return xloResult.Ret();
//}}XLP_SRC

    // TODO - Set the value of xloResult
    return xloResult.Ret();
}

If the input-checking code fails, then a useful error message will be returned, such as:

#Error in cell 3 of PaymentSchedule column 1 (PaymentDates): expected a number

or:

#Error column 2 of PaymentSchedule: expected 4 rows

If all the input-checking code is successful, then the variables vecPaymentDates and vecPaymentAmounts will be populated and ready for use. In addition, both vectors will be the same length.

Creating a column group

There are two ways to create a column group in the Function Wizard.

1. You can set the argument type to Columns in the arguments grid.
2. You can use the Edit Groups command to display the Groups dialog, which lets you add existing arguments to a group, and rearrange the members of column groups and compressed groups.

Next: International support >>