XLL+ Class Library (7.0)

Arguments that contain entire columns

The following versions of Excel have three bugs that affect add-in arguments that contain entire columns, such as A:A, A:IV, A1:D65536.

Using a CXlOper Reference type

If you attempt to use a CXlOper to 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 ability to handle entire columns is important to your function, you may want to handle this with a workaround such as the following:

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;
    // Redirect InputRange to a fixed variable, xloFixedRange, 
    // if necessary
    CXlOper xloFixedRange;
    if (CXllApp::XlVersion() >= 8.0
     && CXllApp::XlVersion() < 12.0
     && InputRange->IsRef() 
     && InputRange->GetRef().Top() == 0
     && InputRange->GetHeight() == 16384)
        CXlRef xlref = InputRange->GetRef();
        xlref.Bottom() = CXlRef::MaxRow();
        if (InputRange->IsMRef())
            xloFixedRange.FromMRef(&xlref, 1, InputRange->GetSheetId());
        InputRange = &xloFixedRange;
    // Now use InputRange as usual

Using a CXlOper by value

Alternatively, if you attempt to use a CXlOper value type to pass a column, then Excel simply passes #NUM! instead. The behaviour of CXlOper by reference 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.