CODEWITHSUNDEEP
vbasortingcollectionsexcel
Cutter
Cutter

Reputation: 1820

Sorting a collection of objects in VBA

I'm trying to write a function that would sort a collection of objects. Since the objects are all of the same type (the same user-defined class), their property set is the same. Is it possible to discover the object's properties (through code) so as to put the collection in a bi-dimensional array, each row being for an object, each column for one of its property?

Another solution would be to copy each object from the collection to an array of objects, and sort them by one of their property, whose name is passed to the function as a string. But I don't see how I can point to the object's property using the property's name passed as a string.

Upvotes: 4

Views: 19187

Answers (1)

Authman Apatira
Authman Apatira

Reputation: 4054

For a collection, it is best to sort it by it's Keys (that's what they're there for) -- but in case you don't have the keys list (lost your keys!):

'Give an input "Data As Collection"

    Dim vItm As Variant
    Dim i As Long, j As Long
    Dim vTemp As Variant

    For i = 1 To Data.Count – 1
        For j = i + 1 To Data.Count
            If CompareKeys(Data(i).myMemberKey, Data(j).myMemberKey) Then
                'store the lesser item
                vTemp = Data(j)

                'remove the lesser item
                Data.Remove j

                're-add the lesser item before the greater Item
                Data.Add vTemp, , i
            End If
        Next j
    Next i

Come up with your own CompareKey function which will return true or false if the UDT member variables are >, < or 0 to one another. The reason you have to delete and re-add is because you cannot 'swap' internal members in a vb6/vba collection object.

Best of luck

EDIT:

To access a property you have the name of programmatically (as a string), use VB's CallByName function in the form:

 Result = CallByName(MyObject, "MyProperty", vbGet)

Upvotes: 8

Related Questions