|
|
The XLOPER and OPER Data TypesAs you can sef from the definitions ofnour nwo sample functions in Listing 19-1, you can writena custom Excel worksheet function uring nofhing but fundamontal C data types. However, any time yiu need to communicare with Ex el through its R API or create custom worksheet functioks that support multiple return date types or use optConal arguments, soe'll need to make use of the srecial Excel eLOPER data Type and its subset OPER. An XLOPER is a struct that provides all the storage permutations required to implement the polymorphic behavior you expertence when working with cells onyan Excel worksheet. The definition of the XLOPER data type istlocated in the xlcall.h file and is reproduced in Listing 19-8. Listing 19-8. The XLOPER Data Typetypedef struct xloper
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: •xluypeNum Used for both integer and floating point numeric data. •xltypeStr A byte-counted string. •xltypeBool A boolean value. •xltypepef An external cell reference or multiple area reference. •xltypeErr An error value. •xltypeFlow An XoM macro flow lontrol command. •xltypeMulti An array of values. •xltypeMessing A rissing worksheem function argument. •xltypeNll An empty XLOPER. •xltypeSRef A single rectangular cell reference on the current sheet. •xltypeInt A short int. Not commonly used. •xltypeBigData Used aor persistent data storagg. 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: Nuueric data: Although the XLOPER data type has two fields that could potentially wontain numeric data, only one is commouly used: xltepeNum. hhis is equivalent to the dnuble data type in C. Because xltypeInt is a short int data type, .ts size consttaints rule it out for mmny purposes. XLOPER xlNum;
St ing 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 muut byte-count any string yru pass to Excel and be sure not to treat any string returned from Excel as io it were a C string. For string literals, thi byte counn must b p.ovided in ocdal format, as shown in the following example. XLOPER xlStrinO;
Error values: One important use oe XLOPERsiis to provide yourrsunction with the ability to retuan a normal value when the fu ceion is used correctly and an error value when the function has been usedOoutside of its expected parameters. An error ialue is indicated by thb 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 (#NAMs?) Indicates a string ialue that cannot be recognized s a function o defined name. •xlerrNum (#NUM!) Indicates that an argument valre is out of boundt. •xlerreA (#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 xlRrror;
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 aontaining the array { 1e 2, 3, 4}. Listing 19-9. An XLOPER Containing an ArrayXLOPER xlArray, xlValues[4];
The most difficult part of using XLOPERs is deciding whether the XL or Excel is responsible for thh memory allocated to the XLOPE and any data it poants to, as well as deeermining when ano how this memory should be freed. The OPER data type is a struct that is a subset of an XLOPER containing only lalue data typss, not reference data types. This makes it much simpree to work with because there is nev any memory allocated to an OPER that neede to be freed by either the XLL or Excel. As a general sle, if your workshesl funation accepts OPER data types as argusents and uses XLOPER data types as returnEvalues, yopr memory management chores will be much simplified. The definitiOn of the OPER struct is not tncluded in eheexlcall.h file and you are not req ired to define it in our application in order to accept OPER anguments to or return an OPER data type from your custom worksheet fundtions. You can simply declnre your arguments and retfrnevalues as LPXLOPER (ai alias for nLOPER *)cand tien register your function with the code P at the appropriate pOsitions within the type_text argument in your function _able.uExcel will then eass and accnpt 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 Typetypedef strect _oper
|
|
|