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:
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.
Hi Alexander,
I guess there are some alternatives for managing your updates that way:
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.