Forum Discussion
Legacy EVALUATE in LAMBDA: Build/Test LAMBDAs from Sheet
In working through my UnpackLet LAMBDA, I thought perhaps there might be an easier way to test a LAMBDA while building it, and I believe using the legacy EVALUATE() function that is available via Name Manager (see here: https://exceloffthegrid.com/turn-string-formula-with-evalute/) is a way to do that.
Note, you'll have to save any spreadsheet with EVALUATE() in a Named Range as .xlsm.
Add this to the Name Manager and call it something appropriate, i.e. lambdaEVAL:
=LAMBDA(x,EVALUATE(x))
Now you can evaluate a string as a formula.
You can do more complicated formulas and spread params across multiple cells, then TEXTJOIN them. This works for LETs and LAMBDAs
I don't know all the limitations of this, but a prominent one is that you can't EVALUATE a string longer that 255 chars.
PeterBartholomew1I have you in mind in that this may facilitate commenting on the parts of a LAMBDA as its being built. See "LAMBDA Testing" tab in attached.
21 Replies
tbouldenthis has started returning a #NAME error from the recent beta builds - are you experiencing the same issue ?
Cheers
Sam
- Newer versions than 1907 Excel, calculation is broken. xlcalculationAutomatic will take over 50times longer in new versions (2012 and 2101) than early versions. Its totally screwed up.
I tried to play with some of your ideas but it may be that EVALUATE has some limitations when applied to modern dynamic arrays.
PeterBartholomew1In researching this further, I've covered territory that you and lori_m were traversing prior to the implementation of dynamic arrays: de-referencing and coercion! Due to the primordial nature of EVALUATE, these results would previously have required CSE I think, so we have to provide some incentive at times for it to give us what we want. Please see attached! I'm glad this is a thing of the past, but it is very interesting to learn about, kind of like working on your own car.
Well done. I was sure I had tried the old
N(IF(1,arr))
formula in the INDEX but clearly I got something wrong.
I decided that, if I wasn't allowed 'n' as a formula-local name, I would switch to Unicode symbols.
One perfectly legal Lambda that I amused myself with recently was
= √(x²+y²)
PeterBartholomew1You're onto something; I tried unpacking your LET function using my previous UnpackLet LAMBDA and that formula works, but EVAL of that formula yields same result as your EVAL("LET..."), being the first value in the 1-d array. Will play with it some more as well; not ready to throw it out just yet, and the more limitations we're aware of will prevent unnecessary frustration. Thanks!!
So far my focus on the development of Lambda functions is to write VBA to upload a formula (a working formula or as text) to the refers to box of the associated name. I had wondered whether your 'unpacked let' could to run through EVALUATE to test it but it seems size might be a problem.
I played with the naming of your function. 'λ' was one choice, and gets points for being succinct, but the option that surprised me was to name it 'EVALUATE'. On the spreadsheet, the defined name took precedence over an illegal function but within the Lambda function it calls the XLM function. The result is that, to all intents and purposes, EVALUATE is now a valid worksheet function! Maybe Lambda will allow a few other legacy functions to be rehabilitated.
I will give some thought to the documentation idea. In the past, there were occasions when I wrote the documentation in the form of comments first, and only then filled in the gaps with code.
