Evaluate Lambda in VBA

[ExcelRobot]

Q:  How can I evaluate an expression containing a custom Lambda call in VBA?

I've created a LAMBDA function that returns a column vector array that I would like to be able to evaluate in VBA passing it a parameter and return that array to a variant variable.  Something like this:

Dim v As Variant

v = Evaluate("MyLambda(" & param & ")")

But the Evaluate function chokes on the MyLambda with #NAME! error.  The same formula evaluates fine in a cell in the same workbook.

If it were a relatively simple Lambda, I could use Evaluate(Names("MyLambda").RefersTo&"(" & param & ")").  But My Lambda is fairly complex and it calls other Lambdas.  Once hacky workaround I came up with is to stick the =MyLambda(<param>) into a cell on a sheet, calculate, and then grab the array off the sheet, but I'd prefer to find a way that doesn't mess with the user's sheets.

Has anyone figured out a way to get this to work in VBA???

Thanks in advance,

Erik

[#2 · Accepted solution]

@ExcelRobot If I create a very simple lambda:

ListOfNumbers: =LAMBDA(n,SEQUENCE(n,1,1,1))

and  execute this code:

Sub foobar()
    Dim v
    v = Evaluate("listofnumbers(10)")
    Stop
End Sub

and open the locals window, v is an array containing the numbers 1 to 10.

[#3]

Interesting! Your simple example works find for me as well, so it's something about one of my lower level lambdas that the EVALUATE function doesn't like. It works fine in the cell but I get an Error 2015 in VBA. Well, that promising... I'll have to dig in further and see if I can isolate the source of the problem.

[#4]

@Jan Karel Pieterse 

Okay, so this is pretty bewildering. I've isolated the problem down to an IFS statement. This is a hugely simplified version, the simplest way I could find to demonstrate the problem in Immediate Window:

?EVALUATE("=IFS(FALSE,""logical_test1"",FALSE,""logical_test2"",FALSE,""logical_test3"",FALSE,""logical_test4"",FALSE,""logical_test5"",FALSE,""logical_test6"",FALSE,""logical_test7"",FALSE,""logical_test8"",FALSE,""logical_test9"",FALSE,""logical_test10"",FALSE,""logical_test11"",TRUE,""logical_test12"")")
Error 2015

The crazy thing is that these two both work fine.  Very minor differences that shouldn't make a difference.

?EVALUATE("=IFS(FALSE,""logical_test1"",FALSE,""logical_test2"",FALSE,""logical_test3"",FALSE,""logical_test4"",FALSE,""logical_test5"",FALSE,""logical_test6"",FALSE,""logical_test7"",FALSE,""logical_test8"",FALSE,""logical_test9"",FALSE,""logical_test10"",TRUE,""logical_test11"")")
logical_test11
?EVALUATE("=IFS(FALSE,""test1"",FALSE,""test2"",FALSE,""test3"",FALSE,""test4"",FALSE,""test5"",FALSE,""test6"",FALSE,""test7"",FALSE,""test8"",FALSE,""test9"",FALSE,""test10"",FALSE,""test11"",TRUE,""test12"")")
test12

I'm sure I'll find a way to work around this but sheesh, that's gotta just be an Excel bug.

[#5]

Not that surprising, the Evaluate method accepts a string argument which is limited to 255 characters. See: https://docs.microsoft.com/en-us/office/vba/api/excel.application.evaluate

[#6]

Bingo! So it seems that when a Lambda is evaluated using EVALUATE, it must be breaking the LET arguments down and evaluating them individually, so any LET variable assignments that are over 255 characters cause the whole EVALUATE to either fail or return unexpected results. So, moral of the story, if you want to use EVALUATE on Lambdas, the lambdas can be complex as whole but must be broken down into smaller calculation units within. Thanks Jan!