Application.Caller and dynamic arrays

81 views
Skip to first unread message

Alexander Lillevang Bech

unread,
Nov 5, 2020, 6:58:40 AM11/5/20
to Excel-DNA
Hi Govert,

I'm having some troubles identifying ranges when using dynamic arrays.

When I'm calling a function which returns a object[,] i'd like to see the complete address of the return when recalculating this function.
For this purpose i used the below code

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

And this works well enough when using the old method; CTRL+SHIFT+ENTER. However when using dynamic arrays in excel, it only returns the calling cell.
Is there anyway to mimic the behavour of CTRL+SHIFT+ENTER, as I then had access to the address of the whole return + values in each cell of the range.

Appreciate your hard work on this project, it works fantastic.

Alexander Bech


Govert van Drimmelen

unread,
Nov 5, 2020, 5:26:05 PM11/5/20
to exce...@googlegroups.com

Hi Alexander,

 

In the Dynamic Arrays world, the “address of the return [range] when recalculating” is not really a defined concept.

The result can be a single scalar value or dynamically spill, according to what your function returns and according to the rest of the formula in the anchor cell.

This is true whether your function returns an array or not.

(And as you note it can also be an array caller in the old Ctrl+Shift+Enter style)

 

For example:

  • Suppose your function returns a 2x3 array, but is called as =SUM(MyFunc()). The result is not an array or dynamic array in the sheet. Even though your function returns an array, it results in a normal scalar result in the sheet.
  • Suppose your function return an int, but is called as =SEQUENCE(MyFunc()). The result will be a dynamic array that spills or single scalar value depending on what your function returns. On the next call it might be different. If it can’t spill you’ll have a single cell with a #SPILL! error.

 

One can come up with more examples like this.

 

So when your function is calculating, there is nothing that you can say about what the array-ness or spill range of the result will be – it is not determined yet, and won’t be determined by your function. All you know is what the anchor cell will be from the xlfCaller call, as you show.

 

The one thing you might reasonably ask is what the previous value for the spill range of the calling cell was.

I.e. if you function is called in cell A1, you might want to know about the size and contents of A1# before the calculation started.

While it is possible to get the old value of the calling cell (if the function is marked as IsMacroType=true) I have not been able to find a way to get the full old spill range.

(Just appending # to the address of the caller does not seem to work.)

 

As a last resort, you can also use sheet events to monitor the whole sheet.

But even in that case I’m not sure how you monitor the dynamic array spill ranges, but I imagine it is possible somehow.

But that would get quite ugly soon.

 

It sounds to me like you might need to embrace the ‘dynamic’ aspect of Dynamic Arrays.

Or maybe you can say a bit more about what you are trying to do with your function, and why that is problematic.

 

-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 view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/449ac70d-daf8-481d-835d-66875adb1e6en%40googlegroups.com.

Alexander Lillevang Bech

unread,
Nov 6, 2020, 4:37:57 AM11/6/20
to Excel-DNA
Hi - Thanks for the quick and thorough answer.

Yeah I think I need to look more into the dynamic arrays and how they operate.

My problem was that I had a function which I'd like to suspend for recalculation by having it just return the previous value if the user wished to. The function might be quite heavy, so it would better performance if recalculation wasn't done automatically. This was easy enough for a single scalar value, but moving into array territory was a bit tricky.
My goal was to find the previous value for the spill range of the calling cell, but as you note, it might be a bit more complicated and i'd like to stay away from sheet events.

I'll experiment a bit more on the subject and come back here if I find anything useful. Thanks for the input!

Alexander

Govert van Drimmelen

unread,
Nov 6, 2020, 5:32:00 AM11/6/20
to exce...@googlegroups.com

Hi Alexander,

 

I guess there are some alternatives for managing your updates that way:

  • Keep your own cache of last values returned, keyed by the calling ref
  • Take an IObservable / RTD approach where you only push a new result if the user refreshes using a button or something.

 

Anyway, dynamic arrays complicate things a bit, but I still think it’s a great feature.

pkm

unread,
Sep 13, 2021, 5:55:50 PMSep 13
to Excel-DNA
Hi Govert,

Thanks so much for your detailed responses on this topic.  I came across a similar requirement as Alexander where I needed to return the previous value of the calling cell (depending upon a boolean parameter to the UDF) - in this case to effectively stop the cell from recalculating.  

I was able to make this work with Dynamic Arrays as well and thought I should share the workaround.

I followed your pointer about marking the function as IsMacroType=true and using xlfCaller to return the calling cell reference.

I was able to use the static function ToRange contributed by john.a...@jtekt.com https://groups.google.com/g/exceldna/c/4FwjwuPTYO0/m/_OFRuLb0AwAJ to convert the reference to an Excel.Range.  I then used the returned Excel.Range object to retrieve the spill range, if any, for the cell.

Outline of the function is as follows:

    [ExcelFunction(Description = "Get Data ", IsMacroType = true)]
    public static dynamic[,] getdata(bool doRefresh,  [Optional] dynamic Para1,  [Optional] dynamic Para2, [Optional] dynamic Para3, [Optional] dynamic Para4)
    {
        if (!doRefresh)
        {
            Excel.Application xlapp = (Excel.Application)ExcelDnaUtil.Application;
            //Excel.Range udfrange = xlapp.Caller;
            ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;
            dynamic udfrange = ToRange(caller);
            dynamic[,] objectArray = null;
            if (udfrange.HasSpill)
            {
                dynamic rl = udfrange.SpillingToRange;
                objectArray = new dynamic[rl.Rows.Count, rl.Columns.Count];
                for (int row = 0; row < rl.Rows.Count; row++)
                {
                    for (int col = 0; col < rl.Columns.Count; col++)
                    {
                        objectArray[row, col] = rl.Cells[row + 1, col + 1].Value2;
                    }
                }
            }
            else
            {
                objectArray = new dynamic[1, 1];
                objectArray[0, 0] = udfrange.Cells[1, 1].Value2;
            }
            return objectArray;
        }
       // recalculate the cell as before
       ...
       return newarray;
    }

Thanks again for all your help and guidance in this project.

-Praveen
Reply all
Reply to author
Forward
0 new messages