The xlCoerce function works even when the source XLOPER is a reference. Therefore, if you coerce a reference to Sheet1!A1 to a string, xlCoerce looks up the current value of A1 and converts it to a string. In fact, you can coerce a reference to any nonreference type, which has the effect of looking up the value of that cell. This is the fastest way to get the value from a cell. Because this operation is so common, it is the default behavior of xlCoerce. If the second argument (destination type) is omitted, it is assumed that you want to coerce a reference to any nonreference type or, in other words, look up the value of a cell.
The following example shows code for the function LookupCell, which finds the value of any single cell on the active (front) sheet by constructing an external reference and then coercing it.
/*
** LookupCell
** Looks up the value of a cell on the active sheet
**
** Arguments:
**
** LPXLOPER pxResult Room for an XLOPER to store result
** int iRow 0-based row
** int iColumn 0-based column
**
** Returns:
**
** pxResult
**
** Important! You must remember to call xlFree on pxResult when
** you are done!
*/
LPXLOPER WINAPI LookupCell(LPXLOPER pxResult, int iRow, int iColumn)
{
XLOPER xRef;
XLMREF xlmref;
/* Get Sheet ID of active sheet */
Excel4(xlSheetId, &xRef, 0);
xRef.xltype = xltypeRef;
xRef.val.mref.lpmref = (LPXLMREF)&xlmref;
xlmref.count = 1;
xlmref.reftbl[0].rwFirst=xlmref.reftbl[0].rwLast=iRow;
xlmref.reftbl[0].colFirst=xlmref.reftbl[0].colLast=iColumn;
/*
** Since there is only one argument to xlCoerce, Microsoft Excel
** will coerce to ANY nonreference type
*/
Excel4(xlCoerce, pxResult, 1, (LPXLOPER)&xRef);
return pxResult;
}
Similarly, you can coerce a rectangular reference. This allows you to look up a rectangular range of cells, all at once, and returns an xltypeMulti (Microsoft Excel array). An example of this is FuncSum in Framewrk\Generic.c. The code from this function follows:
__declspec(dllexport) LPXLOPER WINAPI FuncSum(
LPXLOPER px1,LPXLOPER px2,LPXLOPER px3,LPXLOPER px4,
LPXLOPER px5,LPXLOPER px6,LPXLOPER px7,LPXLOPER px8,
LPXLOPER px9,LPXLOPER px10,LPXLOPER px11,LPXLOPER px12,
LPXLOPER px13,LPXLOPER px14,LPXLOPER px15,LPXLOPER px16,
LPXLOPER px17,LPXLOPER px18,LPXLOPER px19,LPXLOPER px20,
LPXLOPER px21,LPXLOPER px22,LPXLOPER px23,LPXLOPER px24,
LPXLOPER px25,LPXLOPER px26,LPXLOPER px27,LPXLOPER px28,
LPXLOPER px29)
{
static XLOPER xResult; // Return value
double d=0; // Accumulate result
int iArg; // The argument being processed
LPXLOPER *ppxArg; // Pointer to the argument being processed
XLOPER xMulti; // Argument coerced to xltypeMulti
WORD i; // Row and column counters for arrays
LPXLOPER px; // Pointer into array
int error = -1; // -1 if no error; error code otherwise
//
// This block accumulates the arguments passed in. Because FuncSum is
// a Pascal function, the arguments will be evaluated right to left.
// For each argument, this code checks the type of the argument and
// then does things necessary to accumulate that argument type. Unless
// the caller actually specified all 29 arguments, there will be some
// missing arguments. The first case handles this. The second case
// handles arguments that are numbers. This case just adds the number
// to the accumulator. The third case handles references or arrays.
// It coerces references to an array. It then loops through the
// array, switching on XLOPER types and adding each number to the
// accumulator. The fourth case handles being passed an error. If this
// happens, the error is stored in error. The fifth and default case
// handles being passed something odd, in which case an error is set.
// Finally, a check is made to see if error was ever set. If so, an
// error of the same type is returned; if not, the accumulator value
// is returned.
//
for (iArg = 0; iArg < 29; iArg++)
{
ppxArg = &px1 + iArg;
switch ((*ppxArg)->xltype)
{
case xltypeMissing:
break;
case xltypeNum:
d += (*ppxArg)->val.num;
break;
case xltypeRef:
case xltypeSRef:
case xltypeMulti:
if (xlretUncalced == Excel(xlCoerce, &xMulti, 2,
(LPXLOPER) *ppxArg, TempInt(xltypeMulti)))
{
//
// That coerce might have failed due to an
// uncalced cell, in which case, we need to
// return immediately. Microsoft Excel will
// call us again in a moment after that cell
// has been calced.
// return 0;
}
for (i = 0;
i < (xMulti.val.array.rows * xMulti.val.array.columns);
i++)
{
// obtain a pointer to the current item //
px = xMulti.val.array.lparray + i;
// switch on XLOPER type //
switch (px->xltype)
{
// if a num accumulate it //
case xltypeNum:
d += px->val.num;
break;
// if an error store in error //
case xltypeErr:
error = px->val.err;
break;
// if missing do nothing //
case xltypeNil:
break;
// if anything else set error //
default:
error = xlerrValue;
break;
}
}
// free the returned array //
Excel(xlFree, 0, 1, (LPXLOPER) &xMulti);
break;
case xltypeErr:
error = (*ppxArg)->val.err;
break;
default:
error = xlerrValue;
break;
}
} if (error != -1)
{
xResult.xltype = xltypeErr;
xResult.val.err = error;
}
else
{
xResult.xltype = xltypeNum;
xResult.val.num = d;
}
return (LPXLOPER) &xResult;
}