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 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

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.

5 Replies

  • 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 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