LAMBDA.EVALUATE

[tboulden]

The old Excel 4.0 macro EVALUATE has been available via the Name Manager for some time, but was hard to use. Now with LAMBDA we can name a function that uses EVALUATE and use that function in a sheet.

Some limitations:

• can only be instantiated from Name Manager and workbook must be xlsm
• string to evaluate must be fewer than 256 chars.
• if the formula would have required array (Ctrl+Shift+Enter) entry before, you may need to coerce/dereference the formula
• LAMBDA.EVALUATE can't evaluate another reference to LAMBDA.EVALUATE
• probably others I haven't quite come across or figured out just yet

I've named my implementation with "λ_EVAL" for brevity, but figure LAMBDA.EVALUATE makes more sense as an intro. I've used this to help me debug LET/LAMBDA functions as I've been wrapping my head around how the functions work. It loses some utility as formulas get longer, but I use REPLACESTRINGS to shorten formula length if needed while building/testing.

Link to original on Microsoft Excel discussion board: Legacy EVALUATE in LAMBDA: Build/Test LAMBDAs from Sheet

LAMBDA_Testing.xlsm
	A	B	C	D	E	F
1	Can only be instantiated from Name Manager, not from cell/sheet, and workbook must be xlsm
2	=LAMBDA(x,EVALUATE(x))
3
4	Length		Entry Text	Formula Text	lambdaEVAL	Formula Text
5	8		FACT(10)		3628800	=λ_EVAL(C5)
6
7	18		LET(a,20,b,30,a+b)		50	=λ_EVAL(C7)
8
9			LET(
10			a,20,
11			b,30,
12			a+b
13			)
14	18		LET(a,20,b,30,a+b)	=TEXTJOIN("",TRUE,C9:C13)	50	=λ_EVAL(C14)
15
16			LAMBDA(
17			x,
18			x+1
19			)
20	13		LAMBDA(x,x+1)	=TEXTJOIN("",TRUE,C16:C19)	8	=λ_EVAL(C20&"(7)")
Examples and Limitations

Cell Formulas
Range	Formula
A5,A20,A14,A7	A5	=LEN(C5)
E5,E14,E7	E5	=λ_EVAL(C5)
F5,D20,F20,D14,F14,F7	F5	=FORMULATEXT(E5)
C14	C14	=TEXTJOIN("",TRUE,C9:C13)
C20	C20	=TEXTJOIN("",TRUE,C16:C19)
E20	E20	=λ_EVAL(C20&"(7)")

 

[tboulden]

Note I've tested several other old Excel 4.0 XLM macro functions and got the following working using LAMBDA: FILES, GET.WORKBOOK, NAMES, GET.NAME. I'm sure there are many others that work as long as they are informational and don't try to manipulate other objects. I'm not well-versed enough to know how much utility these provide, so don't know that they need there own thread.