XLL+ Class Library

Walkthrough: Adding a matrix argument

You can use the XLL+ Function Wizard to specify that an argument should contain a two-dimensional array - or matrix - of a particular type. This walkthrough demonstrates how to add and use a matrix argument in an add-in function.

Creating the project

To create the project using Visual Studio 6

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

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

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

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

Creating the function

Use the XLL+ Function Wizard to create a new add-in function and to add a vector argument.

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.

  1. Open the source file MatrixFns.cpp and click on the New XLL+ Function menu item or tool-button, to show the New Function dialog, and fill in the name, category and description as shown below.

  2. Add a new argument named m of type double, by typing into the arguments grid, as shown below.

  3. Click on the Matrix tool, to convert the argument to a matrix.

  4. The argument is now a matrix of type double, as shown below.

  5. Add a second argument, axis, of type Short, as shown below.

    Click on the OK button to close the Function Wizard and save the function.

Inspecting the code

  1. The following code has been added to VectorFns.cpp.

    // Function:    MatrixAxisMean
    // Purpose:     Returns the mean of the values in one axis of a matrix
    
    //{{XLP_SRC(MatrixAxisMean)
        // NOTE - the FunctionWizard will add and remove mapping code here.
        //    DO NOT EDIT what you see in these blocks of generated code!
    IMPLEMENT_XLLFN2(MatrixAxisMean, "RPI", "MatrixAxisMean", 
        "m,axis", "Math & Trig", "Returns the mean of the values in"
        " one axis of a matrix", "Square input matrix\000Axis - 0 or"
        " 1\000", "B0()0()m Square input matrix\0\0", 1)
    
    extern "C" __declspec( dllexport )
    LPXLOPER MatrixAxisMean(const COper* m, short axis)
    {
        CXlOper xloResult;
        BOOL bOk = TRUE;
        MTX_PTRS<double> matm;
        bOk = bOk && m->ReadMatrix(matm, "m", xloResult, 0, 0, XLA_ARRAY_FLAGS_STD, 0, 0);
        if (!bOk)
            return xloResult.Ret();
    //}}XLP_SRC
    
        // TODO - Set the value of xloResult
        return xloResult.Ret();
    }

    Let us examine the interesting code.

  2. Note that the matrix argument has been declared as type COper*. The COper type is usually used to pass vector and matrix values from Excel to add-in functions.

    LPXLOPER MatrixAxisMean(const COper* m, short axis)
    
  3. A buffer variable matm is declared. The contents of the COper argument passed by Excel will be extracted and put into matm.

        MTX_PTRS<double> matm;
    

    The matrix is declared as type MTX_PTRS. This is a macro, normally defined as ple::mtx_ptrs.

    (On some occasions, for instance if you are using your own matrix class with a matrix adapter, it may be defined differently - see Adapter classes for imtx<T>.)

  4. Code has been generated to read the contents of m into matm.

        bOk = bOk && m->ReadMatrix(matm, "m", xloResult, 0, 0, XLA_ARRAY_FLAGS_STD, 0, 0);
        if (!bOk)
            return xloResult.Ret();
    

    Note that if the cell range passed to the function cannot be read for any reason (for example, because a cell contains a string instead of a number) then an error message will be generated, placed in xloResult, and immediately returned to Excel.

Completing the function

  1. Add code to implement the function, as shown below:

    extern "C" __declspec( dllexport )
    LPXLOPER MatrixAxisMean(const COper* m, short axis)
    {
        CXlOper xloResult;
        BOOL bOk = TRUE;
        MTX_PTRS<double> matm;
        bOk = bOk && m->ReadMatrix(matm, "m", xloResult, 0, 0, XLA_ARRAY_FLAGS_STD, 0, 0);
        if (!bOk)
            return xloResult.Ret();
    //}}XLP_SRC
    
        // Check that the matrix is square & not empty
        if (matm.size(0) != matm.size(1))
            return CXlOper::RetString("#Error: Expected a square matrix for m");
        if (matm.size(0) == 0)
            return CXlOper::RetString("#Error: Expected a non-empty square matrix for m");
    
        // Traverse the matrix
        int i, n = matm.size(0);
        double dSum = 0.0;
        for (i = 0; i < n; i++)
            dSum += (axis == 0) ? matm[i][i] : matm[i][n - (1 + i)];
        dSum /= (double)n;
    
        // Return the result
        xloResult = dSum;
        return xloResult.Ret();
    }
  2. Note the use of the size() method to get the size of the matrix in each dimension.
  3. Notice how the items in the matrix can be accessed exactly as if the matrix was an array.

            dSum += (axis == 0) ? matm[i][i] : matm[i][n - (1 + i)];
    

    This behaviour is made possible by the overloaded operator:

    mtx_ptrs<T>::operator const T**() const
  4. Build and test the project.

See Also

Walkthroughs | mtx_ptrs<T> class | COper::ReadMatrix method | Reference: Array arguments