Application.Caller and dynamic arrays
Alexander Lillevang Bech
Govert van Drimmelen
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
Govert van Drimmelen
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/aacf3e23-d990-4205-9230-4317e4f25aa9n%40googlegroups.com.
