The XLOPER and OPER Data Types As you can see from the definitions of our two sample functions in Listing 19-1, you can write a custom Excel worksheet function using nothing but fundamental C data types. However, any time you need to communicate with Excel through its C API or create custom worksheet functions that support multiple return data types or use optional arguments, you'll need to make use of the special Excel XLOPER data type and its subset OPER. An XLOPER is a struct that provides all the storage permutations required to implement the polymorphic behavior you experience when working with cells on an Excel worksheet. The definition of the XLOPER data type is located in the xlcall.h file and is reproduced in Listing 19-8. Listing 19-8. The XLOPER Data Type typedef struct xloper { union { double num; /* xltypeNum */ LPSTR str; /* xltypeStr */ WORD bool; /* xltypeBool */ WORD err; /* xltypeErr */ short int w; /* xltypeInt */ struct { WORD count; /* always = 1 */ XLREF ref; } sref; /* xltypeSRef */ struct { XLMREF far *lpmref; DWORD idSheet; } mref; /* xltypeRef */ struct { struct xloper far *lparray; WORD rows; WORD columns; } array; /* xltypeMulti */ struct { union { short int level; /* xlflowRestart */ short int tbctrl; /* xlflowPause */ DWORD idSheet; /* xlflowGoto */ } valflow; WORD rw; /* xlflowGoto */ BYTE col; /* xlflowGoto */ BYTE xlflow; } flow; /* xltypeFlow */ struct { union { BYTE far *lpbData; /* data passed to XL */ HANDLE hdata; /* data returned from XL */ } h; long cbData; } bigdata; /* xltypeBigData */ } val; WORD xltype; } XLOPER, FAR *LPXLOPER; // The following additional structs are used to implement // the SRef and Ref XLOPER subtypes. // Describes a single rectangular reference typedef struct xlref { WORD rwFirst; WORD rwLast; BYTE colFirst; BYTE colLast; } XLREF, FAR *LPXLREF; // Describes multiple rectangular references. // This is a variable size structure. // Its default size is 1 reference. typedef struct xlmref { WORD count; XLREF reftbl[1]; // actually reftbl[count] } XLMREF, FAR *LPXLMREF; At its simplest level an XLOPER contains two pieces of information: some kind of data and a flag indicating what type of data that is. There are 12 possible data types an XLOPER can hold. These are represented by the following constants defined in the xlcall.h header file: xltypeNum Used for both integer and floating point numeric data. xltypeStr A byte-counted string. xltypeBool A boolean value. xltypeRef An external cell reference or multiple area reference. xltypeErr An error value. xltypeFlow An XLM macro flow control command. xltypeMulti An array of values. xltypeMissing A missing worksheet function argument. xltypeNil An empty XLOPER. xltypeSRef A single rectangular cell reference on the current sheet. xltypeInt A short int. Not commonly used. xltypeBigData Used for persistent data storage. Due to space limitations, we only discuss the most frequently used XLOPER types. When you receive an XLOPER from Excel, either as an argument to a custom worksheet function or as the return value from an Excel4 function call (discussed later), you query the xltype member of the XLOPER struct to determine what type of data you are receiving. When you create an XLOPER, you set the xltype member to indicate what type of data your XLOPER holds. The following are some examples of XLOPERs you might create: Numeric data: Although the XLOPER data type has two fields that could potentially contain numeric data, only one is commonly used: xltypeNum. This is equivalent to the double data type in C. Because xltypeInt is a short int data type, its size constraints rule it out for many purposes. XLOPER xlNum; xlNum.xltype = xltypeNum; xlNum.val.num = 5.5; String data: The key thing to remember when using string data with Excel is that Excel does not use null-terminated C strings. Instead, it uses byte-counted Pascal strings. Therefore, you must byte-count any string you pass to Excel and be sure not to treat any string returned from Excel as if it were a C string. For string literals, the byte count must be provided in octal format, as shown in the following example. XLOPER xlString; xlString.xltype = xltypeStr; xlString.val.str = "\035This is a byte-counted string"; Error values: One important use of XLOPERs is to provide your function with the ability to return a normal value when the function is used correctly and an error value when the function has been used outside of its expected parameters. An error value is indicated by the type xltypeErr and the err field is set to one of the following error value constants supplied in xlcall.h: xlerrNull (#NULL!) Refers to the intersection of two ranges that don't intersect. xlerrDiv0 (#DIV/0!) Indicates an attempt to divide by zero or by a blank cell. xlerrValue (#VALUE!) Indicates an argument of the wrong type. xlerrRef (#REF!) Indicates an invalid cell reference. xlerrName (#NAME?) Indicates a string value that cannot be recognized as a function or defined name. xlerrNum (#NUM!) Indicates that an argument value is out of bounds. xlerrNA (#N/A) Indicates that the function cannot calculate a valid return value based on the arguments passed to it. In the following example we create an XLOPER containing a #VALUE! error: XLOPER xlError; xlError.xltype = xltypeErr; xlError.val.err = xlerrValue; Arrays: These are somewhat more complex XLOPERs that enable you to return arrays from your custom worksheet functions, thereby creating custom array formulas. In Listing 19-9, we create an XLOPER containing the array { 1, 2, 3, 4}. Listing 19-9. An XLOPER Containing an Array XLOPER xlArray, xlValues[4]; int i; for (i = 0; i < 4; ++i) { xlValues[i].xltype = xltypeNum; xlValues[i].val.num = i + 1; } xlArray.xltype = xltypeMulti; xlArray.val.array.lparray = &xlValues[0]; xlArray.val.array.rows = 1; xlArray.val.array.columns = 4; The most difficult part of using XLOPERs is deciding whether the XLL or Excel is responsible for the memory allocated to the XLOPER and any data it points to, as well as determining when and how this memory should be freed. The OPER data type is a struct that is a subset of an XLOPER containing only value data types, not reference data types. This makes it much simpler to work with because there is never any memory allocated to an OPER that needs to be freed by either the XLL or Excel. As a general rule, if your worksheet function accepts OPER data types as arguments and uses XLOPER data types as return values, your memory management chores will be much simplified. The definition of the OPER struct is not included in the xlcall.h file and you are not required to define it in your application in order to accept OPER arguments to or return an OPER data type from your custom worksheet functions. You can simply declare your arguments and return values as LPXLOPER (an alias for XLOPER *) and then register your function with the code P at the appropriate positions within the type_text argument in your function table. Excel will then pass and accept OPER structs even though XLOPER structs were specified in your function definition. If you want to declare and use OPER variables in your XLL, you will need to add the OPER struct definition shown in Listing 19-10 to your project. Listing 19-10. The OPER Data Type typedef struct _oper { union { double num; unsigned char *str; unsigned short int bool; unsigned short int err; struct { struct _oper *lparray; unsigned short int rows; unsigned short int columns; } array; } val; unsigned short int type; } OPER;