XLL add in function obtain array range reference

[dr...@red-three.com]

Would like to be able to determine the rows/cols of the current array range from an XLL UDF written in c++.  That is, have UDF's that are array funcs, but they need to know the entire Ref/Range of the array from which they are called.

1) Excel4(xlfCaller, ...) cannot be used in general, since if the user places the cursor in just one cell in the array range the XLOPER returned by xlfCaller will contain ONLY the ref to that one cell.

2) using Excel4(xlfSelection, ...) cannot be used in general, since it only returns the entire (array) range if the entire range is selected prior to the call, otherwise returns only the cell(s) which have been explicitly selected, and not the entire array range.

3) Have tried to use iE4 = Excel4(xlcSelectSpecial, &ArRef, 1, &SSArg) with all vars being XLOPER's and SSArg type = xltypeInt, and SSArg.Val.w = 6, which is the correct arg value to select the current array range.

iE4 returns 0, meaning "success".

However, this does not work in a UDF, as ArRef comes back as Type 4 (bool), and the .rwFirst, .rwLast, etc components are mostly rubbish.

Put differently, one would have expected ArRef to come back as type 1024 (xltypeSRef), with the relevant components holding the array Range ref values.

I am guessing the issue may be related to this being an "xlc" ie. a "command" function.

Regardless, must have some way to obtain the entire current array range/ref when only one or few cells in the array range are selected.

Please advise

[Govert van Drimmelen]

Hi,

I do not see the behaviour for xlfCaller that you describe. When called from an array UDF formula the function correctly returns the full array range

For this it does not seem to matter whether I register the function with or without the # macro type identifier.

I've tried with this C# code in an Excel-DNA add-in:

    [ExcelFunction(IsMacroType = true)]

    public static string GetCallerAddressMacro()

    {

        var caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;

        return caller.ToString();

    }

    [ExcelFunction(IsMacroType = false)]

    public static string GetCallerAddress()

    {

        var caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;

        return caller.ToString();

    }

Maybe you can describe a bit more of how you tested the xlfCaller in your case. There are certainly issues with xlfCaller inside a wrapper UDF that calls RTD - these behave strangely when called from array formulas, and dpn't always return the correct caller.

But for regular UDFs I don't know of a problem.

-Govert

[dr...@red-three.com]

Dear Govert

Many thanks for the prompt response.  I provide some code below that demonstrates the issue, and have attached an jpg showing debug session with the xlfCaller result.

However, before I go further, allow me to emphasise that xlfCaller "works" on the very first occasion on which the array range is created in the sheet.  It is only thereafter, e.g. when the user returns to the array range, and, say, places the cursor in just ONE of the cells in the array range.  For example, the attached jpg shows the caller's return value when the cursor was in the second of a three-cell array range.

The UDF is registered with "#", since without it, the GetCell bit doesn't work.

The code below begins with the usual xlfCaller, and as noted, it reports only the Ref for the single cell, when only a single cell in an array is selected.  If the entire array is selected, even then xlfCaller only returns the Ref for the current cell, but then xlfSelection can be used to get the full Ref.  Obviously, xlfSelection does not return the entire array when only one cell is selected.

So in terms of the code below, "Line A" does not work in a general sense.

So have implemented the GetCell querry to determine if the current cell is in fact a part of an array, and if so, try to use xlfSelectSpecial(6) to get the arrary Ref.  xlfSelectSpecial(6) is supposed to perform the equivalent of Edit/GoTo/Special/CurrentArray ... but I get rubbish-like content for the row/col elements of the XLOPER, on "Line B"

BTW, the iExcel4Res's always return "0" (i.e. "success"), even the XLOPERs contain rubbish

No idea why it is not working ... everything else works, and would have been "finished" were it not for this.

.... so any suggestion for any method that gets the array Ref would be greatly appreciated.


    XLOPER xARTCallerQQ;
    XLOPER xCellRefQQ;
    XLOPER xCellRefArgQQ;


    iExcel4Res = Excel4(xlfCaller, &xARTCallerQQ, 0);                    // Line "A"


    xCellRefQQ.xltype = xltypeBool;

    xCellRefArgQQ.xltype = xltypeInt;
    xCellRefArgQQ.val.w = 49;                        // the Arg we want, in this case 49 for "is this an array func/cell?"

    iExcel4Res = Excel4(xlfGetCell, &xCellRefQQ, 1, &xCellRefArgQQ);    // Arg =49 is supposed to test for array funcs



    if( xCellRefQQ.val.xbool )
    {
        XLOPER xSelectSpecial;                        // "Num_Type"    - 6 for curreent array

        xSelectSpecial.xltype = xltypeInt;
        xSelectSpecial.val.w = 6;                    // need "6" to return the encompassing array

        iExcel4Res = Excel4(xlcSelectSpecial, &xARTCallerQQSS, 1, &xSelectSpecial);    // Line "B"

    }


As the jpeg shows, xlfCaller returns only a single cell Ref, though the  cursor/selected cell is inside an array.

[Govert van Drimmelen]

Hi,

 

I might be confused – are you calling the UDF as an array formula (entered with Ctrl+Shift+Enter)?

Do you mean to get the array that this array formula is called from?

 

It’s the region you’d get if you selected one of the cells and pressed Ctrl+/

 

If so, the xlfCaller calls returned that array consistently in my quick test.

 

To be sure, that’s not related to the region of non-empty cells that the formula might be inside - it’s the region that was selected when the user pressed Ctrl+Shift+Enter to insert the formula as an array formula and that has the single array formula in.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[red3]

Cheers for that.

 

I answer your questions below, but I have just made a "big leap" in narrowing the problem.  When I test the UDF's I always use the "fx" button in the toolbar.  However, I re-tested using the both "Calc sheet" and also a macro I have that performs a "range/cell calc".  Using either of those WORKED ... whoo hoo :-)

 

However, using "fx" fails with the results posted and attached.   This is still a serious problem, since users should be able to select a single cell in an array formula and use the "fx" button or equivalent.  

 

... so any thoughts would be greatly appreciated.

 

As for your questions.

 

Yes, it is a proper formula array range entered by selecting a range of cells, pasting the desired UDF, pressing ctrl+shift+enter. 

 

I have several test examples with 1-D and 2-D array formula ranges.  The results are always the same, xlfCaller returns the entire array Ref (i.e. the XLOPER's .rwFirst etc are correct on the initial entry of the array formula range), but thereafter, the return is a Ref that only contains the single Cell that contains the cursor/is selected

 

... as shown in the jpg attached previously, and here again (here the selection was was the second of a three-cell array formula range), and xlfCaller returns only the row/col info for that ONE cell (when using "fx").

 

All I need is the Ref of the (full) array Range (ie. the rwFirst ... etc) since the UDF needs to know the size/shape of the array's Range before it can decide what/how to calculate).

 

Partial Eval:  The idea is that if somebody is using a sheet, and they wish to eval a single UDF that returns an array on that sheet, then the user will (often) simple select a single cell in the desired array, and (in my case) there is a button to perform an eval on just that cell/range (c.f. instead of eval the entire sheet, since some sheets are large/complex and wish to eval single UDF's only sometimes).

 

Ta

 

DrO

 

 

You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/VPrnqZ01g5o/unsubscribe.
To unsubscribe from this group and all its topics, send an email to exceldna+u...@googlegroups.com.

[red3]

oops, sorry, I may not have been clear in my previous response.

It all works, including with "fx", except in debug mode on the first cycle through the func.

Cheers

DrO

[Govert van Drimmelen]

Since this is not related to Excel-DNA, you might like to try the Excel for Developers forum: 

http://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev

But I'm afraid the answer might be: That's how Excel works.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of red3 [Dr...@red-three.com]
Sent: 15 September 2014 07:25 PM

[red3]

Dear Mr Govert

 

As noted, it is working. 

 

The problem was that it does not work on the first cycle through the UDF, when debugging.  Since my UDF must know the array dims prior to calc, I was stumped. 

 

The solution, make sure that incorrect array info does not crash the UDF while Excel is performing its first cycle through the UDF, and let Excel do its second cycle. 

 

On Excel's second cycle through the UDF, it gets the array info correct, since its "array eval plumbing" automatically selects the entire array range at the end of the first cycle, and so on the second pass, the array info is correct, and all is well.

 

Since I have never seen Excel get it wrong on the first cycle, it was all a bit surprising.

 

BTW, this issue does not arise when I am working with VBA/DLL's (c.f. XLL's/XLOPER's/DLL's) since the COM objects implicitly carry the array info, and there is no need to explicitly deduce those prior to the actual calcs inside the UDF/DLL.

 

In any case, many thanks for your efforts.