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.

On a side note, you could get rid of INDEX by using the following instead: =LAMBDA(date1;date2;FILTER(WeatherTbl[Temp];(WeatherTbl[Date]>=date1)*(WeatherTbl[Date]<=date2))). — VBasic2008
– VBasic2008, Commented Feb 9, 2023 at 3:08
Thanks @VBasic2008 it is much better now! if I want the filter to return 2 columns for example Humidity and Temp, how do I modify the filter function? — Nikolaos
– Nikolaos, Commented Feb 9, 2023 at 15:52
Since the columns are adjacent, you could use WeatherTbl[Humidity]:[Temp] being tied to this order. A more flexible way would be to stack them horizontally when you could use the reverse order with e.g. HSTACK(WeatherTbl[Temp];WeatherTbl[Humidity]). — VBasic2008
– VBasic2008, Commented Feb 9, 2023 at 19:58

chris neilsen · Accepted Answer · 2023-04-10 21:22:50Z

4

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

edited Apr 10, 2023 at 21:22

answered Feb 9, 2023 at 2:27

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nikolaos Over a year ago

Thanks! your code works great. The date format of the column "Date" is D/M/YYYY.

Nikolaos Over a year ago

Thanks @chris neilsen ! your code works great. Note 1 reply: The date format of the column "Date" is D/M/Y, I had to mention it. Even though The Date format in my excel is set to D/M/Y, the Date variables in VBA still stores the date in M/D/Y. Note 2 reply: On excel the separator is ";" but in evaluate function I still have to use "," as separator. I just found it out!

Collectives™ on Stack Overflow

How do I use an excel lambda function in vba?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related