Sized result arrays

Why do we need to know the size of the caller?

There are several reasons why you may need to know the size of the range from which an array formula is being called.

Misfitting array formulae

If an add-in function returns an array result to a range of cells containing a formula array, it may not be the right size. If the result does not fill the range, then Excel will pad the result with cells containing #N/A!. If the result is too large for the range, then it will be truncated.

You may wish to indicate to the user that truncation has occurred, or you may wish to fill any unused area with blanks.

Repeated vectors

A problem arises if the range containing a formula is two-dimensional, and the returned array is one-dimensional. Excel will repeat the result vector in each row or column of the range.

Fill formulae

You may wish to precisely fill the range containing the formula, for instance with test data, or a set of random numbers.

CXllApp::GetCallerDims

The static method CXllApp::GetCallerDims() can be used to get the dimensions of the range from which the current function was called. The method returns FALSE if the current function was not called from a cell range.

The code below demonstrates how to fill a range precisely.

// Function:    INTERPFILL
// Purpose:     Fill a range using values interpolated between two points

//{{XLP_SRC(INTERPFILL)
    // NOTE - the FunctionWizard will add and remove mapping code here.
    //    DO NOT EDIT what you see in these blocks of generated code!
IMPLEMENT_XLLFN2(INTERPFILL, "RBB", "INTERPFILL", "Start,End", 
    "Statistical", "Fill a range using values interpolated"
    " between two points", "First value\0Last value", "\0\0", 1)

extern "C" __declspec( dllexport )
LPXLOPER INTERPFILL(double Start, double End)
{
    CXlOper xloResult;
//}}XLP_SRC

    // Get the dimensions of the cell range
    USHORT nRows, nCols;
    if (!CXllApp::GetCallerDims(nRows, nCols))
    {
        // We're not being called from a cell range
        return CXlOper::RetError(xlerrNA);
    }

    // Calculate the interval between points
    double dAdd = 0.0;
    if (nCols * nRows > 1) 
        dAdd = (End - Start) / (double)((nCols * nRows) - 1);

    // Allocate an output range of just the right size
    xloResult.AllocArray(nRows, nCols);

    // Fill each cell, incrementing the value at each cell
    double dValue = Start;
    for (USHORT i = 0; i < nRows; i++) 
    {
        for (USHORT j = 0; j < nCols; j++, dValue += dAdd)
        {
            xloResult.Cell(i, j) = dValue;
        }
    }

    return xloResult.Ret();
}

The example also makes use of the helper function CXlOper::RetError() to save a line of code.

Next: Macro functions >>