XLL command (not UDF): xlSet fails to set multiple values

224 Views Asked by At

I'm pretty new to XLL programming but I believe I've done my homework on this little problem. I'm trying to set multiple cell values using xlSet but xlSet just duplicates the first value of my array, as if I had passed it a single reference. If instead I consecutively call xlSet for each cell individually, it works. But it's ugly and, no doubt, slow.

I know xlSet can't be called from a UDF. This isn't a UDF. Elsewhere I saw that somebody had a struct alignment problem in the xlMulti, but I'm sending the same xlMulti back to Excel, so that's not a problem. (I tried the /Zp8 compiler switch, anyway.)

I started with SDK Framework stuff, eg., TempActiveRef(1,1,0,2) and then replaced those calls with the more direct XLCALL.H stuff, mainly to increase my chances of getting responses here.

Office 2010, VSTO 2010, Win7 64bit SP1.

Here's what works and what doesn't:

__declspec(dllexport) int WINAPI testCmd(void)
{
    XLOPER12 ref, xValsInMulti, xResSet;
    XLMREF12 mref;
    int i, res;

// Build an xltypeRef XLOPER12 that points to three cells, A2:C2
    res = Excel12(xlSheetId, &ref, 0);
    if (res != xlretSuccess) return 0;
    ref.xltype = xltypeRef;
    ref.val.mref.lpmref = &mref;
    mref.count = 1;
    mref.reftbl[0].rwFirst = mref.reftbl[0].rwLast = 1;
    mref.reftbl[0].colFirst = 0;
    mref.reftbl[0].colLast = 2;

// Fetch the cell values into an xltypeMulti.
// This works. Returns 0. And xValsInMulti.type becomes xlTypeMulti
    res = Excel12(xlCoerce, &xValsInMulti, 1, &ref );

// Change cell reference to the next row (A3:C3)
    mref.reftbl[0].rwFirst = mref.reftbl[0].rwLast = 2;

// Attempt to set the values. Doesn't work. All cells become value of A2.
    Excel12(xlSet, &xResSet, 2, &ref, xValsInMulti);
    Excel12(xlcAlert, 0, 1, &xResSet); // Displays "TRUE"

// Try again (in the next row) setting each cell individually. This works.
    mref.reftbl[0].rwFirst = mref.reftbl[0].rwLast = 3;
    for (i=0; i<3; i++)
    {
        mref.reftbl[0].colFirst = mref.reftbl[0].colLast = i;
        Excel12(
            xlSet, &xResSet, 2, &ref, xValsInMulti.val.array.lparray+i
        );
        Excel12(xlcAlert, 0, 1, &xResSet); // Displays "TRUE"
    }

    Excel12(xlFree, 0, 1, &xValsInMulti);

    return 1;
}
0

There are 0 best solutions below