Walkthrough: Grouping multiple arguments into a single Excel range

Excel only accepts a limited number of arguments - 30. Therefore, you may sometimes find it convenient to group multiple arguments into a single input range. For instance, if your function accepts 35 arguments, you can group 6 related arguments together and require that they be passed in a single range of size 1 x 6 or 6 x 1.

The Function Wizard lets you specify grouped arguments, and generates code to read them from Excel ranges into local variables.

This walkthrough demonstrates how to create an add-in function that accepts grouped arguments.

Our add-in function will combine Excel's NORMDIST and NORMSDIST built-in functions, by accepting a value, and optionally a range containing a mean and standard deviation. If the range is omitted, then defaults of 0 and 1 will be used for the mean and the standard deviation respectively.

In a real-world example, this function would have dozens of other arguments. Grouping arguments when there is no need to is confusing and irritating for the user. We only do it here for the sake of a simpler example.

Creating the project

To create the project using Visual Studio 6

From the File menu, select New to show the New dialog.
Select the XLL+ AppWizard 4 project template from the list in the Projects tab, and enter GroupedArgs in the Project name box. Under Location, enter an appropriate directory in which to create the project.
Accept all the default settings in both pages of the XLL+ AppWizard.

To create the project using Visual Studio .NET or Visual Studio 2005

From the File menu, select New and then Project to open the New Project dialog.
Select the XLL+ Excel Add-in project template from the list of Visual C++ Projects, and enter GroupedArgs in the Name box. Under Location, enter an appropriate directory in which to create the project.
Accept all the default settings in the XLL+ .NET/2005 AppWizard.

For more details about creating projects, see Creating an add-in project in the XLL+ User Guide.

Creating the function

Note: If you do not know how to start the Function Wizard, or you cannot find the tool-bar, look at Installing the Function Wizard under Developer Studio 6 or Installing the Function Wizard under Visual Studio .NET or Visual Studio 2005.

Use the XLL+ Function Wizard to add a new function, GroupedFn, filling in the function's details as shown below:
Add a numeric argument X, as shown below:
Add a new argument of type Group, by selecting the type in the drop-down combo, as shown below:

Then set the name to be Distribution.
Next, edit the grouped argument, by clicking on the Edit Argument tool, as shown below:

Alternatively, you can use the Argument - Edit Argument menu item, or type Ctrl+E.
In the Edit Arguments dialog, select the General tab, and set the argument to be Optional.
Select the Members tab, and enter the members of the group, as shown below.

Click OK to accept the new grouped argument.
The complete function should look something like that shown below:

Click OK to accept the new function and close the Wizard.

Examining the code

The Wizard has added code to GroupedArgs.cpp, as shown below.

// Function:    GroupedFn
// Purpose:     Function with grouped arguments

//{{XLP_SRC(GroupedFn)
    // NOTE - the FunctionWizard will add and remove mapping code here.
    //    DO NOT EDIT what you see in these blocks of generated code!
IMPLEMENT_XLLFN2(GroupedFn, "RBP", "GroupedFn", "X,Distribution",
    "Statistical", "Function with grouped arguments", "First requ"
    "ired argument\0002 items: Mean (Mean of distribution);"
    " StdDev (Standard deviation of distribution)\000", 
    "\0B[0.0]Mean Mean of distribution\0+B[1.0]StdDev Standard"
    " deviation of distribution\0", 1)

extern "C" __declspec( dllexport )
LPXLOPER GroupedFn(double X, const COper* Distribution)
{
    CXlOper xloResult;
    BOOL bOk = TRUE;
    double Mean = 0.0;
    bOk = bOk && (Distribution->IsMissing() || Distribution->ReadGroupItem(Mean, "Distribution", 0, "Mean", xloResult));
    double StdDev = 1.0;
    bOk = bOk && (Distribution->IsMissing() || Distribution->ReadGroupItem(StdDev, "Distribution", 1, "StdDev", xloResult));
    if (!bOk)
        return xloResult.Ret();
//}}XLP_SRC

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

Examine the 2 lines of code that extract the value Mean from the grouped argument Distribution:
```
    double Mean = 0.0;
    bOk = bOk && (Distribution->IsMissing() || Distribution->ReadGroupItem(Mean, "Distribution", 0, "Mean", xloResult));
```
First, the local variable, Mean, is declared and is assigned its default value, 0.0.

Then if no errors have already occurred, the Excel Distribution argument is tested to see if it is missing. If it is not missing, then an attempt is made to extract the value of Mean from the first cell in the input range.

If it is impossible for the ReadGroupItem() method to read the argument into Mean (for instance if the input value is a string or is empty), then an appropriate error string will be placed in xloResult, and bOk will be set to FALSE.

Adding the code

Add some code to the add-in function to delegate all the work to the Excel built-in function NORMDIST, as shown below:

extern "C" __declspec( dllexport )
LPXLOPER GroupedFn(double X, const COper* Distribution)
{
    CXlOper xloResult;
    BOOL bOk = TRUE;
    double Mean = 0.0;
    bOk = bOk && (Distribution->IsMissing() || Distribution->ReadGroupItem(Mean, "Distribution", 0, "Mean", xloResult));
    double StdDev = 1.0;
    bOk = bOk && (Distribution->IsMissing() || Distribution->ReadGroupItem(StdDev, "Distribution", 1, "StdDev", xloResult));
    if (!bOk)
        return xloResult.Ret();
//}}XLP_SRC

    // xlfNormdist was looked up in the following XLL+ help topic:
    // Section: Technical Notes
    // Topic  : Excel built-in function numbers (Alphabetical order) 
    static int xlfNormdist = 293;

    // Call Excel's built-in function NORMDIST
    xloResult.Excel(xlfNormdist, 4, &CXlOper(X), &CXlOper(Mean), &CXlOper(StdDev), &CXlOper(FALSE));

    // Return results of call to NORMDIST
    return xloResult.Ret();
}

Note that for clarity, the function number of the built-in Excel functions is declared as a named static value:
```
    static int xlfNormdist = 293;
```
This number was found by looking up the function in the table Excel built-in function numbers.

Testing the add-in

To test the add-in