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.

JKPieterse's avatar
JKPieterse
Silver Contributor
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!
  • ExcelRobot's avatar
    ExcelRobot
    Brass Contributor
    Apr 13, 2022
    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.

Resources