Office 2013 and later

Last modified: July 01, 2011

Applies to: Excel 2013 | Office 2013 | Visual Studio

Converts one type of XLOPER/XLOPER12 to another, or looks up cell values on a sheet.

Excel12(xlCoerce, LPXLOPER12 pxRes, 2, LPXLOPER12 pxSource, LPXLOPER12 pxDestType);


The source XLOPER/XLOPER12 that needs to be converted.

pxDestType (xltypeInt)

(Optional). A bit-mask of the resulting types you are willing to accept. You should use the bitwise OR operator ( | ) to specify multiple possible types. If this argument is omitted, references to single cells are converted to one of the value types xltypeStr, xltypeNum, xltypeBool, xltypeErr, xltypeNil (if the referred-to cell is empty), and references to blocks of cells are converted to xltypeMulti. This makes xlCoerce the most convenient way to look up cell values.

Returns the coerced value (xltypeStr, xltypeNum, xltypeBool, xltypeErr, xltypeNil, or xltypeMulti).

xlCoerce cannot convert to or from xltypeBigData or xltypeFlow. Passing an xltypeMissing or xltypeNil type as pxDestType is equivalent to omitting the argument. Conversion can fail in some cases. For example, some strings cannot be converted to numbers, whereas others can.

If an array or a multi-cell reference is converted to a single value type, the result is the value of the top left cell or array element.

The following code can be found in \SAMPLES\EXAMPLE\EXAMPLE.C.

Note Note

The xlcAlert function implicitly tries to convert its argument to a string so that the coercion step shown here could in fact be removed, and xInt could be passed directly to xlcAlert. As xlcAlert is a command macro, this code only works correctly when called from a macro sheet.

short WINAPI xlCoerceExample(short iVal)
   XLOPER12 xStr, xInt, xDestType;

   xInt.xltype = xltypeInt;
   xInt.val.w = iVal;

   xDestType.xltype = xltypeInt;
   xDestType.val.w = xltypeStr;

   Excel12f(xlCoerce, &xStr, 2, (LPXLOPER12)&xInt, (LPXLOPER12)&xDestType);

   Excel12f(xlcAlert, 0, 1, (LPXLOPER12)&xStr);
   Excel12f(xlFree, 0, 1, (LPXLOPER12)&xStr);

   return 1;