Forum Discussion
A statement of truth or a wasted opportunity. Lambda helper functions reluctant to return 2D arrays.
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!
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.
