Arbitrary filtering of anything enumerable is something Enumerable.Where does, and it does it with the help of delegates, something VBA has no knowledge of, or ability to implement.
WARNING what follows is experimental code that is not intended for production use. It is provided as-is for educational purposes. Use at your own risk.
You can simulate it though. see Wait, is this... LINQ? and Generating and calling code on the fly on Code Review - below is a class I've called Delegate - note that it has its PredeclaredId attribute set to True, so that its Create factory method can be invoked from the default instance. It uses the Regular Expressions library for parsing the definition of the function, and the VBE Extensibility API library to literally generate an "anonymous function" given a string, for example:
Set x = Delegate.Create("(x) => MsgBox(""Hello, "" & x & ""!"")")
x.Execute "Mug"
The above code generates and invokes this function:
Public Function AnonymousFunction(ByVal x As Variant) As Variant
AnonymousFunction = MsgBox("Hello, " & x & "!")
End Function
Which produces what you would expect:
Delegate class
Option Explicit
Private Type TDelegate
Body As String
Parameters As New Collection
End Type
Private Const methodName As String = "AnonymousFunction"
Private this As TDelegate
Friend Property Get Body() As String
Body = this.Body
End Property
Friend Property Let Body(ByVal value As String)
this.Body = value
End Property
Public Function Create(ByVal expression As String) As Delegate
Dim result As New Delegate
Dim regex As New RegExp
regex.Pattern = "\((.*)\)\s\=\>\s(.*)"
Dim regexMatches As MatchCollection
Set regexMatches = regex.Execute(expression)
If regexMatches.Count = 0 Then
Err.Raise 5, "Delegate", "Invalid anonymous function expression."
End If
Dim regexMatch As Match
For Each regexMatch In regexMatches
If regexMatch.SubMatches(0) = vbNullString Then
result.Body = methodName & " = " & Right(expression, Len(expression) - 6)
Else
Dim params() As String
params = Split(regexMatch.SubMatches(0), ",")
Dim i As Integer
For i = LBound(params) To UBound(params)
result.AddParameter Trim(params(i))
Next
result.Body = methodName & " = " & regexMatch.SubMatches(1)
End If
Next
Set Create = result
End Function
Public Function Execute(ParamArray params()) As Variant
On Error GoTo CleanFail
Dim paramCount As Integer
paramCount = UBound(params) + 1
GenerateAnonymousMethod
'cannot break beyond this point
Select Case paramCount
Case 0
Execute = Application.Run(methodName)
Case 1
Execute = Application.Run(methodName, params(0))
Case 2
Execute = Application.Run(methodName, params(0), params(1))
Case 3
Execute = Application.Run(methodName, params(0), params(1), params(2))
Case 4
Execute = Application.Run(methodName, params(0), params(1), params(2), _
params(3))
Case 5
Execute = Application.Run(methodName, params(0), params(1), params(2), _
params(3), params(4))
Case 6
Execute = Application.Run(methodName, params(0), params(1), params(2), _
params(3), params(4), params(5))
Case 7
Execute = Application.Run(methodName, params(0), params(1), params(2), _
params(3), params(4), params(5), _
params(6))
Case 8
Execute = Application.Run(methodName, params(0), params(1), params(2), _
params(3), params(4), params(5), _
params(6), params(7))
Case 9
Execute = Application.Run(methodName, params(0), params(1), params(2), _
params(3), params(4), params(5), _
params(6), params(7), params(8))
Case 10
Execute = Application.Run(methodName, params(0), params(1), params(2), _
params(3), params(4), params(5), _
params(6), params(7), params(8), _
params(9))
Case Else
Err.Raise 5, "Execute", "Too many parameters."
End Select
CleanExit:
DestroyAnonymousMethod
Exit Function
CleanFail:
Resume CleanExit
End Function
Friend Sub AddParameter(ByVal paramName As String)
this.Parameters.Add "ByVal " & paramName & " As Variant"
End Sub
Private Sub GenerateAnonymousMethod()
Dim component As VBComponent
Set component = Application.VBE.VBProjects("Reflection").VBComponents("AnonymousCode")
Dim params As String
If this.Parameters.Count > 0 Then
params = Join(Enumerable.FromCollection(this.Parameters).ToArray, ", ")
End If
Dim signature As String
signature = "Public Function " & methodName & "(" & params & ") As Variant" & vbNewLine
Dim content As String
content = vbNewLine & signature & this.Body & vbNewLine & "End Function" & vbNewLine
component.CodeModule.DeleteLines 1, component.CodeModule.CountOfLines
component.CodeModule.AddFromString content
End Sub
Private Sub DestroyAnonymousMethod()
Dim component As VBComponent
Set component = Application.VBE.VBProjects("Reflection").VBComponents("AnonymousCode")
component.CodeModule.DeleteLines 1, component.CodeModule.CountOfLines
End Sub
You'll want to change the VBProjects("Reflection").VBComponents("AnonymousCode") to point to some empty standard module in your VBA project... or have a project named Reflection with an empty standard module named AnonymousCode for the Execute method to generate the function into.
As an artifact of how VBA code is compiled, the generated code can be executed, but you can't place a breakpoint in it, and the VBE will refuse to break inside the generated code - so whatever string you supply the factory method with, you better be sure it's simple enough to be 100% bug-free.
What this gives you, is an object that encapsulates a specific action: this object can then be passed around as a parameter, like any other object - so if you have your own collection class implementation (here LinqEnumerable), then you can use it to implement a Where method that takes a Delegate parameter, assuming the predicate parameter encapsulates a function that returns a Boolean:
Public Function Where(ByVal predicate As Delegate) As LinqEnumerable
Dim result As LinqEnumerable
Set result = New LinqEnumerable
Dim element As Variant
For Each element In encapsulated
If predicate.Execute(element) Then result.Add element
Next
Set Where = result
End Function
So given that custom collection class, you can create a Delegate instance that defines your custom criteria, pass it to the Where method, and get the filtered results back.
You can even push it further and implement an Aggregate method:
Public Function Aggregate(ByVal accumulator As Delegate) As Variant
Dim result As Variant
Dim isFirst As Boolean
Dim value As Variant
For Each value In encapsulated
If isFirst Then
result = value
isFirst = False
Else
result = accumulator.Execute(result, value)
End If
Next
Aggregate = result
End Function
And run it pretty much as you would with C# LINQ, minus compile-time type safety and deferred execution:
Dim accumulator As Delegate
Set accumulator = Delegate.Create("(work,value) => value & "" "" & work")
Debug.Print LinqEnumerable.FromList(List.Create("the", "quick", "brown", "fox")) _
.Aggregate(accumulator)
Output:
fox brown quick the
This work was the basis of the Lambda stuff in the VBEX repository on GitHub (originally by Chris McClellan, co-founder of the Rubberduck project; most of the work can be credited to Philip Wales though) - a 100%-VBA project that gives you several other classes to play with. I'd encourage you to explore these and see if any of it is more appropriate for production use.
ArrayListand issuing a new one with only the items that match the specified criteria.ArrayListpredates LINQ, so even in the C# code from that era that's how you would implement filtering. The ArrayList class doesn't have aFiltermethod.this=that, similar toIComparable_CompareTo. But is there any data structure or filtering mechanism which will accept objects implementing such a function? Such as VBA builtinFilter, can I give it an array of something other than strings, but which implement the right interface?IComparerimplementation, the internal thing will be iterating all items - why not do the simple thing and make that loop yourself?