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.
To create the project using Visual Studio 6
To create the project using Visual Studio .NET or Visual Studio 2005
For more details about creating projects, see Creating an add-in project in the XLL+ User Guide.
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.
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.
Add a new argument named m of type double, by typing into the arguments grid, as shown below.
Click on the Matrix tool, to convert the argument to a matrix.
The argument is now a matrix of type double, as shown below.
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.
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.
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)
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>.)
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.
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(); }
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
Walkthroughs | mtx_ptrs<T> class | COper::ReadMatrix method | Reference: Array arguments