Filter a Custom Class Using CallByName

Posted on June 18, 2015 by Dick Kusleika

Is there a way to dynamically state by which property do I want to filter/sort/unique? ppl.FilterBy(City, “Tokio”).FilterBy(LastName,”Smith”).Unique(FirstName)
Without doing select case

James Brown corrected my response by saying:

You should be able to write a generic filter function in your custom collection class based around CallByName from the object class.

Of course he’s right. Here’s how that might look. Suppose I have some sample contact data.

I could filter by any one of those properties like this

Public Property Get Filter(ByVal sProperty As String, vValue As Variant) As CContacts
    
    Dim clsReturn As CContacts
    Dim clsContact As CContact
    
    Set clsReturn = New CContacts
    
    For Each clsContact In Me
        If CallByName(clsContact, sProperty, VbGet) = vValue Then
            clsReturn.Add clsContact
        End If
    Next clsContact
    
    Set Filter = clsReturn
    
End Property

Public Property Get Filter(ByVal sProperty As String, vValue As Variant) As CContacts

Dim clsReturn As CContacts

Dim clsContact As CContact

Set clsReturn = New CContacts

For Each clsContact In Me

If CallByName(clsContact, sProperty, VbGet) = vValue Then

clsReturn.Add clsContact

End If

Next clsContact

Set Filter = clsReturn

End Property

The comparison value needs to be a variant to account for all the different data types your properties could be. If you had a property of your class that was another class it could complicate things. But this saves me having to write a bunch of Filter properties. So thanks James for making that comment.

Public Sub TestCallByName()
    
    Dim clsContacts As CContacts
    Dim clsFiltered As CContacts
    
    Set clsContacts = New CContacts
    clsContacts.FillFromRange Sheet1.ListObjects(1).DataBodyRange
    
    Set clsFiltered = clsContacts.Filter("State", "Nebraska")
    Debug.Print "Nebraska: " & clsFiltered.Count
    
    Set clsFiltered = clsContacts.Filter("Active", True)
    Debug.Print "Active: " & clsFiltered.Count
    
    Set clsFiltered = clsContacts.Filter("LastPayDate", #4/10/2015#)
    Debug.Print "April 10: " & clsFiltered.Count
    
End Sub

Public Sub TestCallByName()

Dim clsContacts As CContacts

Dim clsFiltered As CContacts

Set clsContacts = New CContacts

clsContacts.FillFromRange Sheet1.ListObjects(1).DataBodyRange

Set clsFiltered = clsContacts.Filter("State", "Nebraska")

Debug.Print "Nebraska: " & clsFiltered.Count

Set clsFiltered = clsContacts.Filter("Active", True)

Debug.Print "Active: " & clsFiltered.Count

Set clsFiltered = clsContacts.Filter("LastPayDate", #4/10/2015#)

Debug.Print "April 10: " & clsFiltered.Count

End Sub

7 thoughts on “Filter a Custom Class Using CallByName”

I’m a big fan of CallByName. It can also be used to write a generic sort function for your custom collection class:

Public Property Get Sort(ParamArray sortFields()) As CContacts

Dim obj As CContact
Dim i As Long

Dim objCollection As CContacts: Set objCollection = New CContacts
Dim SortedList As Object: Set SortedList = CreateObject("System.Collections.SortedList")

    'First loop: Add the items to the SortedList
    For Each obj In Me
      Dim sortKey As String: sortKey = returnSortKeyFromParamArray(obj, sortFields())
      SortedList.Add sortKey, obj
    Next obj
    
    'Second Loop: Add the sorted objects back in to the Custom collection class
    For i = 0 To SortedList.Count - 1
      objCollection.Add SortedList.getbyIndex(i)
    Next i

    
CleanExit:
  Set Sort = objCollection

CleanUp:
  Set objCollection = Nothing
  Set SortedList = Nothing

End Property

Public Property Get Sort(ParamArray sortFields()) As CContacts

Dim obj As CContact

Dim i As Long

Dim objCollection As CContacts: Set objCollection = New CContacts

Dim SortedList As Object: Set SortedList = CreateObject("System.Collections.SortedList")

'First loop: Add the items to the SortedList

For Each obj In Me

Dim sortKey As String: sortKey = returnSortKeyFromParamArray(obj, sortFields())

SortedList.Add sortKey, obj

Next obj

'Second Loop: Add the sorted objects back in to the Custom collection class

For i = 0 To SortedList.Count - 1

objCollection.Add SortedList.getbyIndex(i)

Next i

CleanExit:

Set Sort = objCollection

CleanUp:

Set objCollection = Nothing

Set SortedList = Nothing

End Property

Private Function returnSortKeyFromParamArray(ByVal obj As CContact, ParamArray sortFields()) As String

Dim sortString As String
Dim mySortValue As Variant


Dim j As Long: For j = LBound(sortFields) To UBound(sortFields)
  mySortValue = CallByName(obj, sortFields(0)(j), VbGet)

  If TypeName(mySortValue) = "Long" Or TypeName(mySortValue) = "Double" Then
    sortString = sortString & Format$(mySortValue, "0,000,000,000,000.00")
  Else
    sortString = sortString & mySortValue
  End If
Next j

CleanExit:
  returnSortKeyFromParamArray = sortString & obj.Index

End Function

Private Function returnSortKeyFromParamArray(ByVal obj As CContact, ParamArray sortFields()) As String

Dim sortString As String

Dim mySortValue As Variant

Dim j As Long: For j = LBound(sortFields) To UBound(sortFields)

mySortValue = CallByName(obj, sortFields(0)(j), VbGet)

If TypeName(mySortValue) = "Long" Or TypeName(mySortValue) = "Double" Then

sortString = sortString & Format$(mySortValue, "0,000,000,000,000.00")

Else

sortString = sortString & mySortValue

End If

Next j

CleanExit:

returnSortKeyFromParamArray = sortString & obj.Index

End Function

I use a ParamArray to store a ‘list’ of sort fields. I then return the value for each of those sort fields as a concatenated string. If the value is numeric (I only use Long and Double in my code for numbers) then it needs to be padded with leading zeros to ensure it sorts correctly.

Finally, I use the .Net 1.1 SortedList to do the heavy lifting for me. This sort is at least as fast (and usually many times faster) than any other VBA method I have found.

James Brown says:

June 19, 2015 at 6:18 am

I should add that SortedList requires each ‘Key’ (sortString) to be unqique so I add a UniqueKey field to my Object Class which (if there isn’t already a unique field) is the index number when it is loaded in to the collection.
Felipe Costa Gualberto says:

June 19, 2015 at 10:23 am

Nice, James. Do you know the reference library to use SortedList as early binding? Also, ArrayList, Queue, etc.
James Brown says:

June 19, 2015 at 10:41 am

They are contained in mscorlib.dll but (from memory) I have never been able to get them as early bound.
James Brown says:

June 19, 2015 at 10:46 am

@Felipe, if you really want on push this on then you might want to wrap the .Net class methods in your VBA class (see http://stackoverflow.com/questions/25580867/wrap-net-arraylist-with-custom-vba-class-get-iterator).
Felipe Costa Gualberto says:

June 19, 2015 at 12:25 pm

Thank you James, really interesting.
Doug Glancy says:

June 25, 2015 at 1:46 pm

This is the kind of post that makes DDOE great. The idea of filtering with CallByName is wonderful in itself, and the idea of calling .Net classes from VBA is just an added bonus.

I’ve used CallByName once when I was trying to replicate every possible Workbook-level event from an addin created on the fly, or something like that. It worked.