How to filter an array with a worksheet function?

Viewed 1437

I'm looking for a way to filter an array with an evaluated expression such as:

Dim arr1(), arr2(), arr3()

arr1 = Array(1, 2, 3, 4, 5)           ' > [1, 2, 3, 4, 5]

arr2 = Map(arr1, "Values() * 2")      ' > [2, 4, 6, 8, 10]

arr3 = Filter(arr2, "Values() > 6")   ' > [8, 10]

I've already implemented the Map function with an UDF and with Application.Evaluate("INDEX(expression, )"), but I'm struggling to make it work for Filter:

Private arr_()

Public Function Values() As Variant()
  Values = arr_
End Function

Public Function Map(arr(), expression As String) As Variant()
  arr_ = arr
  Map = Application.Evaluate("INDEX(" & expression & ",)")
End Function

Public Function Filter(arr(), expression As String) As Variant()
  arr_ = arr
  Filter = Application.Evaluate("INDEX(Values(), " & expression & ")")
End Function

Is there a way other than looping/shifting each value? Maby with VLOOKUP?

2 Answers
Related