Forum Discussion

ExcelRobot's avatar
ExcelRobot
Brass Contributor
Apr 09, 2022
Solved

Evaluate Lambda in VBA

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 pass...
excel
Formulas and Functions
Macros and VBA
  • JKPieterse's avatar
    JKPieterse
    Apr 11, 2022

    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.

ExcelRobot's avatar
ExcelRobot
Brass Contributor
Apr 13, 2022

JKPieterse 

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.

  • ExcelRobot's avatar
    ExcelRobot
    Brass Contributor
    Apr 15, 2022
    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!

Resources