3

I'm working on an Excel plugin (XLL), which communicates with COM objects. So, I have to marshall between XLOPER and VARIANT. I've got most of this working, but arrays are definitely a pain. I need to support 1- and 2D arrays.

I imagine someone has already had to deal with this before. What's the best way to simplify dealing with VARIANT, SAFEARRAY, and XLOPER (and XLOPER12)?

airstrike
  • 2,270
  • 1
  • 25
  • 26
Tim
  • 8,912
  • 3
  • 39
  • 57
  • see this new post for an answer :http://stackoverflow.com/questions/27473463/how-to-return-xloper-excel-variable-to-vba-directly-from-a-c-dll – Malick Dec 17 '14 at 10:18
  • Since Malik's link above is dead, here's an archive.org version http://web.archive.org/web/20150616142740/https://stackoverflow.com/questions/27473463/how-to-return-xloper-excel-variable-to-vba-directly-from-a-c-dll – airstrike May 08 '18 at 22:13
  • 1
    Thanks @AndyTerra , I don't know why I have deleted my post ... I just undeleted it. – Malick May 10 '18 at 17:04

2 Answers2

3

I had to hand-roll my own marshalling code for this. There were no freely available libs to handle this. XLW is meant for wrapping your entire plugin - this was not an option for me.

In the end, it just took a lot of time, looking through the documentation for xloper and variant, and figuring out how to map them to each other.

For the curious:

arrays in xloper(12):

  • .xltype is xltypeMulti
  • .val.array is a pointer to an array of xlopers.

arrays in variant:

  • .vt is VT_ARRAY | VT_VARIANT
  • .parray is a SafeArray of variants

other tips with this marshalling:

  • xltypeRef and xltypeSRef: use xlCoerce to look up actual cell values.
  • integer values run the risk of overflow. boost::numeric_cast helps with that.
  • string marshalling is annoying. xloper uses char, xloper12 uses wchar, variant uses bstr.
  • Remember to free the buffers you allocate for your new xloper strings. Use xlbitDLLFree and xlAutoFree(12) accordingly.
  • Template programming is useful for the xloper/xloper12 issue. Boost helps. _bstr_t also helps.
Tim
  • 8,912
  • 3
  • 39
  • 57
  • Can I contact you offline reg. this? I have an UDF written in C++ that needs to return a 2-d variant array by stashing it into a XLOPER. I get nightmares just thinking about it. – ForeverLearning Nov 24 '11 at 14:38
  • @Dilip, post that as a question here on SO. I (or others) can answer it. I'd be happy to help you out; I just prefer to keep answers public, to help others in the future. – Tim Nov 26 '11 at 07:00
0

I use XLL Plus (costs) http://www.as-ltd.co.uk/xllplus/default.htm

There is also XLW (opensource) http://xlw.sourceforge.net/

and probably others I am not aware of

Charles Williams
  • 23,121
  • 5
  • 38
  • 38