Forum Discussion
A statement of truth or a wasted opportunity. Lambda helper functions reluctant to return 2D arrays.
PeterBartholomew1I went down a rabbit hole trying to play with MAKEARRAY since you mentioned it can't have a 0 value. I learned that you can omit both of the first two parameters, and that led me even further trying to see what possibly could go in for those parameters, even providing SEQUENCE formulae, etc. I was hoping to reverse-engineer MAKEARRAY based off the similarity to MAP of 2 parameters, but what I developed with MAP had sensical behavior, but I couldn't quite figure out what was going on with MAKEARRAY; exhibited on the MAKEARRAY tab of attached. I'm not sure I follow how to make it work with the 0 parameter. If the thunks aren't your cup of tea, check out the array_storage tab where I've put together a storage scheme to get dimensions of arrays and flatten them, and can revive the array from the storage array.
lori_m I was able to use the union (,) operator, but could only get it working with thunks, see the union tab. I've not messed with union/intersection much, so not fully following your breadcrumbs, and my google searches didn't help much. Any references you can point me to, or suggest a specific INDEX construction? I wasn't sure what to do with OFFSET either.
My thought was that one might be able to select specific sub areas with
=INDEX((B6:D7,C7:E8,D7:F8),,,i)and then apply MAP with i={1,2,3} maybe within a name? Analogously one can define a name 'subarrays' as
=EVALUATE({"B6:D7","C7:E8","D7:F8"})and then use SUBTOTAL(9,subarrays) as in the previous OFFSET formula.
In Python instead of applying map to 2D arrays one can use array broadcasting in 3D, e.g.
arange(3)+arange(2)[:,None]+arange(3)[:,None,None]returns:
[[[0, 1, 2],
[1, 2, 3]],
[[1, 2, 3],
[2, 3, 4]],
[[2, 3, 4],
[3, 4, 5]]]
A substantial amount of trial and error was required as we're relying on undocumented functionality to achieve this objective. Things like adding names via validation and calling a Sub from a name I only learned through this exercise, however there were many other commands that didn't work as expected when called this way.
As the basis of a 'DEFINE' function, I did have some success adding the validation automatically using =IF(COUNTA(AddValidation()),Result) where 'Result' would refer to a LAMBDA expression similar to your earlier suggestion. I hope to pick this up again later when lambda reaches the current channel if someone else doesn't pick the baton up before then.
Since we've veered a little off topic here, it might be worth raising a new thread for name management in general to continue discussion of this and other ideas to improve user interaction with names. I'll look at putting some notes together when time permits.
I have been somewhat quiet of late! I was struggling a bit sorting out a 'recommended approach' for using thunks from the tboulden experimentation. I need an idiot's guide to follow!
As for your coding, it is so full of unfamiliar techniques that it looks like magic! I think it was Arthur C Clark who had something to say about any sufficiently advanced technology being indistinguishable from magic. It took me a while to recognise the UDFs, tucked away in LET since, without any arguments, they didn't look like functions; finding the subroutine call was even more of a challenge. I had no idea that was possible to call subs like that and it seems to bypass all the constraints that would be placed upon a UDF. I have a vague recollection of seeing hyperlinks being used to achieve a similar objective.
My original thought was to use the Worksheet Change event to run the VBA code but your approach seems to offer advantages. I also like the way you have parsed the cell formula in the 'args' function, I had mentally overcomplicated the task by thinking in terms of Regular Expressions.
Right now, I have some conference papers to review, and hopefully approve, but I will try to put the pieces together, either following my idea of GLOBAL/LOCAL or perhaps using your 'scope' parameter to combine them as DEFINE (say). I am hopeful that this will serve to make some of our very long formulas more compact by separating out the definition of key elements of the formula to defined names yet, at the same time, provide documentation and traceability on the worksheet rather than tucked away in Name Manager which appears to be widely despised.
- String length shouldn't be a factor here. The trick is to call another VBA function indirectly from within a UDF e.g. Evaluate("AddValidation()") - also see the earlier code example. This appears to work with validation and leaves undo intact in basic testing.
- Very interesting! I don't know about VBA Evaluate limitations, but I know I ran into the 256 char limit when using EVALUATE via Name Manager; is that char limit a concern?
It took a bit of time to figure out the formula parsing, it's not 100% foolproof yet, but as a proof-of-concept it works better than expected.
I think the GLOBAL/LOCAL functions that were suggested earlier have potential to further improve the UI by adding the validation automatically - which looks possible using Evaluate - I'lll have to wait for LAMBDA for that though. Anyway full credit to PeterBartholomew1 for speccing it out!I agree unions of ranges have limited application; arrays of ranges on the other hand can be more useful as more functions allow them, eg attachment uses formula below for referencing 4D data:
=INDEX(INDEX(SubArrays,i,j),k,l)I guess the equivalent for thunks could be:
=INDEX(INDEX(Thunks,i,j)(),k,l)Also included is a sample implementation of Peter's idea from earlier in the thread of adding names based on a LET function. I think this is quite an intuitive way of managing names in general not just LAMBDAs, I might see about distributing the code as an addin so any workbook code can be removed.
lori_m Ahh, thanks for the clarification, I had never noticed the areas param in INDEX; I've confirmed both constructions, MAP across an area index and MAP across the EVALUATEd array of ranges.
I couldn't think of a real-world specific use-case for the REDUCE/union construction, but set up a table and unioned all rows and all columns separately, then intersected them. Was surprised to see that its non-commutative in terms of order, but also makes sense in retrospect.
