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.

CXlOper* INTERPFILL_Impl(CXlOper& xloResult, double Start, double End)
{
    // End of generated code 
//}}XLP_SRC 
    // Get the dimensions of the cell range
    size_t nRows, nCols;
    if (!CXllApp::GetCallerDims(nRows, nCols))
    {
        // We're not being called from a cell range 
        throw CXlErrorException(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 (size_t i = 0; i < nRows; i++) 
    {
        for (size_t j = 0; j < nCols; j++, dValue += dAdd)
        {
            xloResult.Cell(i, j) = dValue;
        }
    }
    return xloResult.Ret();
}

The example makes use of the exception class CXlErrorException to abandon the function if the caller is not appropriate.

Next: Events >>