Forum Discussion
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...
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 Suband open the locals window, v is an array containing the numbers 1 to 10.
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
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!