References are not only one of the most common features in the XLM language — they are also the most confusing! In the Microsoft Excel XLM language, you have to remember when to use a local reference and when to use an external reference. You also have to remember when to use R1C1 notation and when to use A1 notation. Sometimes references are specified as strings; sometimes they are not. To help keep all this straight, the C API is designed to simplify specifying references. This section describes the different types of references and how to construct XLOPERs that specify them.
The first thing you must do is decide which sheet you want to refer to. There are three possibilities: the current sheet, the active sheet, or an external sheet.
The current sheet is the sheet that is currently being calculated. This may be a macro sheet (in the case of a running macro) or a worksheet (in the case of a user-defined function). The current sheet is not necessarily the one that appears in front on the screen, as shown in the following example.
The user is working on Sheet1 and clicks Button 1. This executes a macro in Book2. While that macro is running, it calls your DLL. Macro1 is now the current sheet because it is the one being calculated.
To create a reference to the current sheet, you create an xltypeSRef XLOPER, as shown in the following example.
XLOPER xRef;
xRef.xltype = xltypeSRef;
xRef.val.sref.count = 1;
xRef.val.sref.ref.rwFirst = 0;
xRef.val.sref.ref.rwLast = 2;
xRef.val.sref.ref.colFirst = 0;
xRef.val.sref.ref.colLast = 2;
This creates a reference to the cells A1:C3 in the upper-left corner of the current sheet. Notice how rows and columns are always zero-based inside Microsoft Excel.
The active sheet is the sheet that the user sees in front on the screen. For example, in the preceding illustration the active sheet would be Sheet1 in Book1. To create a reference to the active sheet, you need to find out the sheet ID of the active sheet and then construct an external reference to that sheet ID. For more information see the following section, "External Sheet."
Technically, an external sheet is any sheet except the current sheet. This means that the active sheet is an external sheet unless the current sheet and the active sheet are the same. For example, when you are executing a user-defined function during recalculation on a worksheet, the active sheet might be the same as the current sheet. External references are the most common type of reference used in C functions. That is because you need an external reference to refer to the active (front) sheet, which is probably the sheet used to call the DLL. To construct an external reference, you need to find the sheet ID of the sheet you want and build the external reference XLOPER.
To find the sheet ID of the sheet you want, use the xlSheetId function. For more information about how to call the xlSheetId function, see "xlSheetId" on page 254. The xlSheetId function has two forms. If it is called with no arguments, it returns the sheet ID of the active sheet — that is, the sheet that the user sees in front. If it is called with one argument, of type xltypeStr, it returns the sheet ID of the named sheet. The xlSheetId type returns its result by putting the sheet ID in the val.mref.idSheet field of the result XLOPER. For example:
XLOPER xRef;
if (xlretSuccess != Excel4(xlSheetId,&xRef,0))
{
error("No active sheet!");
}
/*
** Now xRef.val.mref.idSheet contains the
** sheet ID of the active sheet.
*/
If this succeeds, the sheet ID for xRef is filled in. Or, you might want an external reference to a named sheet. The following example shows how to obtain the sheet ID of SHEET1 in BOOK1.XLS.
XLOPER xRef, xFileName;
xFileName.xltype = xltypeStr;
xFileName.val.str = "\021[BOOK1.XLS]SHEET1";
if (xlretSuccess !=
Excel4(xlSheetId, &xRef, 1, (LPXLOPER)&xFileName))
{
error ("SHEET1 not found");
}
The next step is to build the external reference XLOPER. This is an XLOPER of type xltypeRef, which is the most general reference type. The following code constructs a rectangular reference to the active sheet:
XLOPER xRef;
XLMREF xlmref;
if (xlretSuccess!=Excel4(xlSheetId,&xRef,0))
{
error();}
else
{
xRef.xltype = xltypeRef;
xRef.val.mref.lpmref = (LPXLMREF) &xlmref;
xlmref.count = 1;
xlmref.reftbl[0].rwFirst = 0;
xlmref.reftbl[0].rwLast = 3;
xlmref.reftbl[0].colFirst= 0;
xlmref.reftbl[0].colLast = 3;
}
This code generates a reference to 16 cells in the upper-left corner of the active (front) sheet. This would be called !A1:D4 in the macro language. If you want to specify a nonadjacent reference, you can use a different value for count. You will also have to allocate more memory for xlmref. Then, you can fill in reftbl[n] for the (n+1)th rectangular region. The reftbl[n] reference is easily expanded using the XLMREF and XLREF constructs defined in XLCALL.H. Dynamically allocate space for the XLMREF and specify sizeof(XLMREF) + sizeof(XLREF) * (n–1), where n is the number of nonadjacent references you want to build.
Note
As a shortcut, you can use an idSheet number of 0 in your XLOPER to get the current sheet. Microsoft Excel automatically fills in the correct idSheet number. When you get external references from Microsoft Excel, you always get an actual idSheet number, not 0, even if they refer to the current sheet.