Use dynamic arrays for array functions on versions of Excel that support dynamic arrays
Currently, if you use the GUI to return an array of values, the array formula will always use a static array, even on versions of Excel that support dynamic arrays. Also, if you manually enter a PI DataLink array formula as a dynamic array (i.e. you are using Enter and not Ctrl + Shift + Enter) in a version of Excel that supports them, the top cell will contain the message "Resize to show all values", even when there is enough room to show all values.
Please make PI DataLink adapt its behaviour according to the version of Excel. If the Excel version supports dynamic arrays, then array formulas generated by the GUI should use dynamic arrays, and the message "Resize to show all values" should never be used, since Excel will just generate a #SPILL! error. If the Excel version does not support dynamic arrays, then PI DataLink should continue with its current behaviour.
In roughly 2030, all supported versions of Excel will use only dynamic arrays, and all support for static arrays can then be dropped from PI DataLink.
That makes more sense. Thanks for clarifying!
Kenneth Barber commented
Sorry, I made a few mistakes with that last sentence. It should read:
"After 2025, all supported versions of Excel will support dynamic arrays, and all support for static arrays can then be dropped from PI DataLink."
I didn't mean to say that support for CSE arrays would be dropped. I also looked up the end of life of Office 2016 and Office 2019 (rather than estimate), and they are both essentially the end of 2025.
Hey Kenneth, I agree with the suggestion to rework PI DataLink to use the newly-released dynamic arrays instead of legacy CSE arrays. You mention that legacy CSE array support will be dropped by roughly 2030. That's the first I've heard of that timeframe being targeted; would you mind sharing where you found that information?