A statement of truth or a wasted opportunity. Lambda helper functions reluctant to return 2D arrays.

PeterBartholomew1 

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.

lori_m 

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.

tboulden 

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!

lori_mI forgot briefly that this was an xlsm and wondered what voodoo was happening! This is pretty slick!

lori_m 

Too easy to forget attachments!

tboulden 

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_mSorry, the prior post ate my image for the MAP constructions.

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.

tboulden 

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]]]

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.

PeterBartholomew1Re: Can a thunk refer to a previously calculated array, just waiting to be called, or should it be thought of as a recipe for calculating the array should it ever be required?

I think I recall hearing Andy/Simon/someone along the way mention lazy evaluation?? And I think I recall that meaning that it stores the LAMBDA, but only evaluates it when needed. It's also possible that my brain made it up sometime during my research on lambda calculus implementations.

PeterBartholomew1 
Thanks for hosting these lively discussions. I have learned a number of useful general techniques from the examples and suggestions in these posts. These threads also provide a useful way to feedback to the community now uservoice is not available. And yes, it would be useful to have access to lambda functionality for testing, with any luck it won't be too much longer to wait until they go current!

tboulden
Nice solutions, It will take some time to fully grasp these techniques! Interesting exercise to generate subarrays, one option:

r,c,n=2,3,5
A = np.arange(n*n).reshape(n,n)+1
p = np.mgrid[0:n-r+1,0:n-c+1].reshape(2,-1)
list(map(lambda i,j:A[i:i+r,j:j+c],p[0],p[1]))

I believe a more elegant solution exists using 3D array manipulations without lambda/map but it eludes me at present.

In Excel I wonder if unions of ranges could be constructed of the form ref1,ref2,...,refn via REDUCE so that individual areas could be selected with INDEX. OFFSET might also work though it is volatile,

=SUM(SUBTOTAL(9,OFFSET(A,{0,1,1},{0,1,2},2,3)))

As Peter says arrays of ranges are unsupported at present but out of interest, can MAP return an array of references using INDEX that can be manipulated this way?

One further thought.  It looks like we are not going to get native support for arrays of arrays or arrays of ranges within the foreseeable future (no doubt for good reason, including the potential loss of backward compatibility), but I wondered whether a single formula could be co-opted to the cause.  MAKEARRAY and derived Lambdas such as VSTACK are already capable of packing arrays into an array 'container' with padded spaces.

At the moment a '0' parameter is illegal within MAKEARRAY, but it would be reasonable to interpret it in much the same manner as a '0' in the INDEX function (i.e. 'I want it all').  If a number of row arrays were to be packed into a 2D array, the '0' would be interpreted as 'make the width just as long as it needs to be to accommodate the longest row'.

Any thoughts?