Forum Discussion

ExcelRobot's avatar
ExcelRobot
Brass Contributor
Apr 08, 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.