How do I use an excel lambda function in vba?

Question

I have an excel workbook where I have create many lambda functions, each one named in the Name manager. For example I have a table named WeatherTbl like this:

Date      Place   Humidity Temp    Windspeed
1/1/2022  Athens     87      12       4
2/1/2022  Athens     83      11       3
3/1/2022  Athens     81      13       3
4/1/2022  Athens     79      10       4

I created the lambda function named Temp which returns the values between date1 and date2 of Temp column:

Temp=LAMBDA(date1;date2;INDEX(FILTER(WeatherTbl;(WeatherTbl[Date]>=date1)*(WeatherTbl[Date]<=date2));;4))

In the excel worksheet the Temp function works great and returns a dynamic array. My question is: Can I access this function through VBA? In a VBA function I want to get the returned dynamic array and use it in calculations Below I give an abstract example of how I want to use it.

Dim TempArray As Object
set TempArray=Temp("1/1/2022","4/1/2022")
For Each element In TempArray
   if a.value=4 then a.value=5
Next element

the command

ActiveWorkbook.Names(Temp)

Returns the string =LAMBDA(date1;date2;INDEX(FILTER(WeatherTbl;(WeatherTbl[Date]>=date1)*(WeatherTbl[Date]<=date2));;4)) that I can't use.

Answer 1

You can execute a LAMBDA with the Evaluate method.

You'll need to create a string that represents the function call, including the parameters to the LAMBDA in a form the LAMBDA understands.

In this case your LAMBDA is expecting Date Serial Numbers.

_{Note 1: Your dates are ambiguous. They might be all in January, or all the 1st of a month. You'll need to adjust the demo code date constants to match your data.}

_{Note 2: In the LAMBDA formula your local uses ;, mine uses ,. In the Evaluate string, you must use ,}

Sub Demo()
    Dim TempArray As Variant
    Dim d1 As Date
    Dim d2 As Date
    Dim OutputRange As Range
    
    d1 = #1/2/2022#  ' Date constants are in M/D/Y
    d2 = #1/3/2022#
    
    TempArray = Application.Evaluate("TEMP(" & CLng(d1) & "," & CLng(d2) & ")")
    
    ' do something with the array
    Set OutputRange = ActiveSheet.Cells(20, 5)
    OutputRange.Resize(UBound(TempArray, 1), 1) = TempArray
    
    
End Sub