Serving the Quantitative Finance Community

DavidJN
Topic Author
Posts: 234
Joined: July 14th, 2002, 3:00 am

### XLOPERs as XLL function arguments

Any hardcore Excel C API XLL developers on the forum? I have a question or two about using the internal Excel data types, the XLOPERs, as XLL function arguments. The 2013 MS XLL SDK sample code (latest version) uses the U data registration type (XLOPER12) for XLL function arguments, and “coerces” them using the xlCoerce function call (embedded within the omnibus Excel12 callback function) to test whether function arguments are of the expected data type before any calculations are effected.

Is xltypeMulti strictly reserved for coerced variables? That is, can they only be created using xlCoerce or can one somehow read an xltypeMulti as a XLL function argument? What does one have to pass as an argument to the Excel Function Wizard for an XLL argument to evaluate as xltypeMulti? Does Steve Dalton's textbook on add-in development address this?

Oh, being pretty old school when it comes to software, I am not interested in 3rd party tools to manage this stuff. I prefer to go straight to the source, which in this case is a bit thinly documented.

bearish
Posts: 5049
Joined: February 3rd, 2011, 2:19 pm

### Re: XLOPERs as XLL function arguments

Govert. But it has been almost 7 years…

Cuchulainn
Posts: 18512
Joined: July 16th, 2004, 7:38 am
Location: Llanfairpwllgwyngyllgogerychwyrndrobwllllantysiliogogogoch

### Re: XLOPERs as XLL function arguments

Dalton's book is rather unfathomable. I seem to lost mine in one of my bookshelves. Anyway, Bovey chap27 is a decent one
https://www.amazon.nl/Professional-Exce ... 0321508793
They sought it with thimbles, they sought it with care; They pursued it with forks and hope; They threatened its life with a railway-share; They charmed it with smiles and soap.

Cuchulainn
Posts: 18512
Joined: July 16th, 2004, 7:38 am
Location: Llanfairpwllgwyngyllgogerychwyrndrobwllllantysiliogogogoch

### Re: XLOPERs as XLL function arguments

Dalton's book is rather unfathomable. I seem to have lost mine in one of my bookshelves. Anyway, Bovey chap27 is a decent one
https://www.amazon.nl/Professional-Exce ... 0321508793

/* I did some test cases long ago before C++ ATL, VBA/dll and Excel DNA. It had its own particular charm, like JCL on IBM mainframes or using DTD before XML became a standard.*/

1. xltypeMulti represents an array of values in an XLOPER struct (it has others) for polymorphic function calls. You can query the xltype member to determine the type of data. BTW I don't know what you mean by a "coerced variable".
2. I don't know how  function arguments can be tested beforehand. I'm saying it's a bit altmodisch.However, XLOPER provides xltypeError and err fields (defined in xlcall.h). This might help ("it is easier to ask for forgiveness than ask for permission").
3. For reference data types, you may have to take memory management into account.
4. You don't need xlCoerce AFAIR to create arrays as arguments.
5. I suppose all this is input to EXCEL4? It can also return error values, not just xlretSuccess.

This is my underdstanding based on incomplete recall.
They sought it with thimbles, they sought it with care; They pursued it with forks and hope; They threatened its life with a railway-share; They charmed it with smiles and soap.

Cuchulainn
Posts: 18512
Joined: July 16th, 2004, 7:38 am
Location: Llanfairpwllgwyngyllgogerychwyrndrobwllllantysiliogogogoch

### Re: XLOPERs as XLL function arguments

#define xlretSuccess        0    /* success */
#define xlretAbort          1    /* macro halted */
#define xlretInvXlfn        2    /* invalid function number */
#define xlretInvCount       4    /* invalid number of arguments */
#define xlretInvXloper      8    /* invalid OPER structure */
#define xlretStackOvfl      16   /* stack overflow */
#define xlretFailed         32   /* command failed */
#define xlretUncalced       64   /* uncalced cell */
#define xlretInvAsynchronousContext  256  /* invalid asynchronous function handle */
#define xlretNotClusterSafe  512  /* not supported on cluster */
They sought it with thimbles, they sought it with care; They pursued it with forks and hope; They threatened its life with a railway-share; They charmed it with smiles and soap.

Cuchulainn
Posts: 18512
Joined: July 16th, 2004, 7:38 am
Location: Llanfairpwllgwyngyllgogerychwyrndrobwllllantysiliogogogoch

### Re: XLOPERs as XLL function arguments

They sought it with thimbles, they sought it with care; They pursued it with forks and hope; They threatened its life with a railway-share; They charmed it with smiles and soap.

DavidJN
Topic Author
Posts: 234
Joined: July 14th, 2002, 3:00 am

### Re: XLOPERs as XLL function arguments

1. I am switching on argument XLOPER xltype for error checking purposes. I have yet to find anything I can supply to the Excel Function Wizard that switches on an xltype into xltypeMulti. I can supply arguments that switch on xltype as expected into the other sub types - xltypeNum, xltypeStr, xltypeRef, xltypeMissing, etc.

The API function xlCoerce does 2 things: 1) provides a structured way of reading data from Excel into an XLL, and 2) provides a structured way to change data types of said data. Perhaps coercion is the MS SDK term for somewhat more structured casting.

2. I've returned XLOPERs in XLLs for decades, returning very specific error messages for dummies who can't enter Excel function arguments correctly. It helps the clients help themselves and that's always helped me. The XLL technology is so altmodish and clumsy it is nearly cultish. But boy it is fast and stable if one gets the memory management right. Fast like rattlesnakes on a hotplate.

3. Yes indeed. Call Excel12 and you may indeed have to free memory. MS SDK documentation is good on the memory management of XLOPERs.

4. Agreed. I generally don't use XLOPERs for arrays, sometimes finding types like the much simpler FP structure (now FP12) more useful. My array stuff ported to 64-bit cleanly just by adding '12' to the end of the 32-bit datatype names.

5. Yes again. Excel4 is now named Excel12 (accommodates bigger data sizes). Excel12 can indeed return informative error values. I am amazed at the MS commitment to Excel backwards compatibility, the SDK still supports macro sheets!

An example of the unexpected behavior I am finding with XLOPER function arguments is that supplying literally nothing instead of an expected double argument in the Function Wizard traps as xltypeMissing (so far so good), but supplying the argument as a reference to an empty Excel cell evaluates to zero. I think one may just need to add suitable logic to check for when switching on xlytype into xltypeRef.

The MS XLL SDK documentation is spotty in places, Excel12 (not all the arguments are explained) is one of them.

Cuchulainn
Posts: 18512
Joined: July 16th, 2004, 7:38 am
Location: Llanfairpwllgwyngyllgogerychwyrndrobwllllantysiliogogogoch

### Re: XLOPERs as XLL function arguments

in-between remark ..

Just had a look in my MC book (Wiley, 2009) with Dr. Joerg Kienitz who uses xll in combination with data structures. He uses the user-friendly wrapper XLW (developed by the late Dr. Mark Joshi) which might help with your use cases.

https://xlw.sourceforge.net/

So, it is just xll, satisfying your original requirement.

Maybe Quantlib is useful but I have no experience (I only know VBA/DLL, C++ ATL and C# Excel DNA).

https://www.quantlib.org/quantlibxl/functional.html
They sought it with thimbles, they sought it with care; They pursued it with forks and hope; They threatened its life with a railway-share; They charmed it with smiles and soap.