Using references

The CXlOper class can hold data of many different types. One of the most important types it can hold is a reference. This is a reference to a cell, a rectangular range of cells, or to multiple ranges of cells.

SRefs and MRefs

A reference can be held in two forms - single and multiple. A single reference (known as "SRef") holds a reference to a single rectangular range of cells, in the current worksheet. A multiple reference (known as "MRef") holds a reference to one or more rectangular ranges of cells, all of which must be in the same worksheet, which does not have to be the current worksheet.

You should use MRef if the range you are specifying is not guaranteed to be in the current worksheet, or (much more rarely) if you are referring to multiple ranges of cells.

You can convert an SRef to an MRef using CXlOper::ChangeType. This is useful if you need to know which worksheet contains the referenced cells.

Current worksheet

It is worth noting that the current worksheet is not the same as the active worksheet. If an add-in function is called from a worksheet cell, the current worksheet is the sheet that contains the cell currently being calculated. If an add-in function is called as a macro, the the current sheet is the same as the active sheet.

Determining whether a CXlOper is a reference

Use the CXlOper::IsRef method to determine if a CXlOper holds a reference. You can use CXlOper::IsMRef and CXlOper::IsSRef to distinguish between types of reference.

CXlOper xloValue, xloRef;
...
if (xloRef.IsRef())
{
    // If xloRef was not a reference, the next line would fail.
    xloRef.SetValue(xloValue);
}

CXlRef object

If a CXlOper is a reference, then it will contain a valid CXlRef object, which can be accessed using CXlOper::GetRef. Each CXlRef object contains only the coordinates of a rectangular range of cells. It does not contain the name of the sheet containing the range. You can inspect and change the coordinates using CXlRef::Top, CXlRef::Left, CXlRef::Bottom an CXlRef::Right. Note that all coordinates are zero-based.

The code below constructs a reference to the single cell B3 of a sheet in several different ways.

CXlRef xlrA(2, 1);               // Constructor with row (=2) & column (=1) arguments
CXlRef xlrB, xlrC, xlrD;
xlrB.SetRef(2, 1);               // Set top and left. Bottom and right are implied.
xlrC.SetRef(2, 1, 2, 1);         // Set top, left, bottom & right explicitly
xlrD.Left() = xlrD.Right() = 1;
xlrD.Top() = xlrD.Bottom() = 2;

You can convert a CXlRef object to and from a text address using CXlRef::ToString and CXlRef::FromString.

CXlRef xlr(2, 1);
CString strAddressA1 = xlr.ToString(TRUE);    // Contains "B3"
CString strAddressRC = xlr.ToString(FALSE);   // Contains "R3C2"
if (!xlr.FromString("A2"))
    CXllApp::XlMessageBox("Failed to read address", 3);
CString strLocal = xlr.ToString(FALSE, TRUE); // Contains "R3C2" in English,
                                              // "Z3S2" in German, etc

Sheet identifiers

Excel uses a numeric sheet identifier to uniquely identify a worksheet. This sheet ID remains constant as long as the worksheet is open. It does not change even if the worksheet's name is changed, or if the containing workbook's name is changed. However, if a workbook is closed and later reopened, there is no guarantee that the sheet IDs of its worksheets will remain constant.

You can get the sheet ID from a reference using CXlOper::GetSheetId.

CXlOper xloRef;
DWORD sheetID = 0;
...
if (xloRef.IsRef())
    sheetID = xloRef.GetSheetId();

You can convert sheet IDs to names and vice versa using CXllApp::GetSheetName and CXllApp::GetSheetId.

DWORD sheetID = CXllApp::GetSheetId("Book1", "Sheet1");
sheetID = CXllApp::GetSheetId("[Book1]Sheet1");
CString sheetName = CXllApp::GetSheetName(sheetID); // Contains "[Book1]Sheet1"

Creating a reference using CXlRef

There are several ways to create a reference. You can create a reference explicitly from a CXlRef, using CXlOper::FromSRef and CXlOper::FromMRef, or various short-hand equivalents. You can initialize a CXlRef object and create a single reference (SRef) from it by copying.

CXlRef xlr(2, 1);
CXlOper xloRef;
xloRef = xlr;         // xloRef now contains a reference to cell B3 in the current sheet
xloRef.FromSRef(xlr); // Has exactly the same effect as the line above

To create an MRef, you must also supply a sheet ID or a sheet name.

CXlRef xlr(2, 1, 3, 2);
DWORD sheetID = CXllApp::GetSheetId("[Book1]Sheet1");
CXlOper xloRef;
xloRef.FromMRef(&xlr, 1, sheetID);         // xloRef now contains a reference to cells B3:C4
                                           // in sheet [Book1]Sheet1
xloRef.FromMRef(&xlr, 1, "[Book1]Sheet1"); // Has exactly the same effect as the line above

Creating a reference using strings

You can also use strings to create references, via CXlOper::MakeRef and CXlOper::MakeRefIntl.

CXlOper xloRef;
if (!xloRef.MakeRef("A1", TRUE))
    CXllApp::XlMessageBox("Failed to read address", 3);
if (!xloRef.MakeRefIntl("R1C1", FALSE))
    CXllApp::XlMessageBox("Failed to read address", 3);
if (!xloRef.MakeRef("[Book1]Sheet1!A1:C5", TRUE))
    CXllApp::XlMessageBox("Failed to read address", 3);

Note that if no sheet name is provided, then the reference will be to the current worksheet.

Functions that return references

Some Excel API functions also create references.

References as arguments to add-in functions

You can also pass references as arguments to add-in functions. Specify an argument's type to be CXlOper, and it will be capable of accepting a reference. A CXlOper argument can also contain anything else, so you should check in your code whether the argument actually is a reference, using CXlOper::IsRef.

//{{XLP_SRC(FillRange)
    // NOTE - the FunctionWizard will add and remove mapping code here.
    //    DO NOT EDIT what you see in these blocks of generated code!
IMPLEMENT_XLLFN2(FillRange, "RR", "FillRange", "Range", 
    "User Defined", "Fill a range with values", "Range to be fill"
    "ed\000", "\0appscope=1\0comerroronxlerror"
    "=1\0comerrorstringprefix=#Error:\0", 2)

extern "C" __declspec( dllexport )
LPXLOPER FillRange(const CXlOper* Range)
{
    XLL_FIX_STATE;
    CXlOper xloResult;
//}}XLP_SRC

    if (Range->IsRef())
    {
        if (Range->GetRef().Width() > 1)
        {
            CXllApp::XlMessageBox("Expected only one column", 3);
        }
        else
        {
            Range->SetValue("Fred");
        }
    }
    return xloResult.Ret();
}

Using references

Several methods of CXlOper are useful only if the CXlOper contains a reference. In addition, many functions of XLL+ classes take references as arguments. Among these are the following:

Next: Setting formulae >>