Forum Discussion
A statement of truth or a wasted opportunity. Lambda helper functions reluctant to return 2D arrays.
Microsoft1x1 and scalars are distinct in Excel. =TYPE(1) returns 1. =TYPE({1}) returns 64.
The Arrays of Arrays limitation is a long standing limitation, it doesn't serve a purpose per se - its just something that was never supported even in the CSE days. Now that arrays are more prominent, the limitation is more apparent. Changing this behavior has backcompat implications, so anything we do here in future needs careful engineering and consideration. It also needs to be weighed and prioritized against other customer asks.
Thanks for the correction, it was just a bit of sloppy reporting from
in which I remembered you saying "Arrays in Excel are 2D; Excel is the only programming language in the world that doesn't have the concept of a 1D array".
I have been trying to refactor old workbooks to help me understand the potential of the new Lambda functions for changing the nature of Excel programming. To my mind, something important that they achieve is to make recursion something more than a 'geeks corner' technique. Then I examined the idea that they may allow me to dispense with the concept of relative referencing in its entirety. Both the notation and the practice of 'copy down' could be consigned to the trashcan. MAP would allow one to dispense with one of the central concepts of traditional spreadsheet development.
It was surprising how many times I hit 'arrays of arrays' (and, to a lesser extent, arrays of ranges) issues. In some instances the gains of simplifying Excel formula were then lost by needing recursive VSTACK to consolidate the result array. What you are doing is amazing but I imagine it is also highly speculative in terms of user acceptance, so I can see why you might not wish to rush the process.
I was raising the issue early because I presumed that replacing a #CALC! error with a logical result might not cause compatibility issues whereas expanding a truncated calculation might have undesirable consequences.
