Arguments that contain entire columns

Excel 97 has three bugs that affect add-in arguments that contain entire columns, such as A:A, A:IV, A1:D65536.

Using a CXlOper

If you attempt to use a CXlOper pass such an argument from Excel 97 by reference, Excel converts it to a range containing only 16384 rows (i.e. the same number of rows as the maximum under Excel 95). If the argument is passed by value, then the bug is avoided, and the range is not truncated. If the ability to handle entire columns is important to your function, you may want to handle this with a workaround such as the following:

//{{XLP_SRC(XlOperFix)
IMPLEMENT_XLLFN2(XlOperFix, "RR", "XlOperFix", "InputRange", 
    "User Defined", "Work around the 16384 row bug in Excel 97",
    "Input range (CXlOper)", "\0", 1)

extern "C" __declspec( dllexport )
LPXLOPER XlOperFix(const CXlOper* InputRange)
{
    CXlOper xloResult;
//}}XLP_SRC

    // Redirect InputRange to a fixed variable, xloFixedRange,
    // if necessary
    CXlOper xloFixedRange;
    if (CXllApp::XlVersion() >= 8.0 
     && InputRange->IsRef() 
     && InputRange->GetRef().Top() == 0
     && InputRange->GetHeight() == 16384)
    {
        CXlRef xlref = InputRange->GetRef();
        xlref.Bottom() = (USHORT)65535;
        if (InputRange->IsMRef())
            xloFixedRange.FromMRef(&xlref, 1, InputRange->GetSheetId());
        else
            xloFixedRange.FromSRef(xlref);
        InputRange = &xloFixedRange;
    }
    // Now use InputRange as usual
    ...
}

Using a COper

Alternatively, if you attempt to use a COper to pass a column, then Excel simply passes #NUM! instead. The behaviour of CXlOper is buggy, but can at least be made to work, as shown above.

Using a CXlArray

Finally, if you attempt to use a CXlArray to pass a column, then Excel fails to call the function, and displays #VALUE! instead.