Returning matrices to Excel

Returning a matrix to Excel is very similar to returning a vector. Add the following stand-alone matrix function to Tutorial1.cpp.

Note: This is a very silly add-in function. Transformations of this kind are far easier to do in Excel itself, especially if you use a mixture of relative and absolute addresses. But it makes a reasonably good example function.

// Multiplication of two vectors to produce a matrix
// aadZ should be an array of cX pointers to columns
void MatrixMultiply(int cX, double* adX, int cY, double* adY, double** aadZ)
{
    int i, j;
    for (i = 0; i < cX; i++) {
        for (j = 0; j < cY; j++) {
            aadZ[i][j] = adX[i] * adY[j];
        }
    }
}

Note that argument aadZ is an array of pointers to columns.

Use the Function Wizard to create a new function MATRIXMULT:

Modify the generated code so that it looks like this:

extern "C" __declspec( dllexport )
LPXLOPER MATRIXMULT(const COper* X, const COper* Y)
{
    CXlOper xloResult;
    BOOL bOk = TRUE;
    std::vector<double> vecX;
    bOk = bOk && X->ReadVector(vecX, "X", xloResult);
    std::vector<double> vecY;
    bOk = bOk && Y->ReadVector(vecY, "Y", xloResult);
    if (!bOk)
        return xloResult.Ret();
//}}XLP_SRC

    // Allocate a matrix with initial size: rows = vecY.xsize() and cols = vecX.size()         
    ple::mtx_ptrs<double> matResult(vecY.size(), vecX.size());                                 

    // Pass the input and output arrays to the stand-alone function                            
    MatrixMultiply(vecY.size(), &vecY[0], vecX.size(), &vecX[0], &matResult[0]);               
    // If you prefer STL notation, use the following                                           
    //MatrixMultiply(vecY.size(), vecY.begin(), vecX.size(), vecX.begin(), matResult.begin()); 

    // Populate xloResult with the resulting matrix                                            
    xloResult = matResult;                                                                     

    return xloResult.Ret();
}

Here we've made use of the mtx_ptrs class to manage the output array. The matrix constructor allocates an appropriate number of rows and columns, which are initially filled with zeroes. Then we use either a pointer to the first array in the matrix (&matResult[0]) or the STL matResult.begin() function to get an array of column pointers, as required by MatrixMultiply().

Populating xloResult with the result of MatrixMultiply() is very simple: we use another CXlOper overloaded assignment operator to allocate the output data and to copy in the contents of matResult.

Next: Array results the hard way >>