Filter a Custom Class Using CallByName

Andrzej asks

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

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.

7 thoughts on “Filter a Custom Class Using CallByName

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

    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.

  2. 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.

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

  4. 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.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.