Sorting a collection of objects in VBA

15,528

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)
Share:
15,528
Cutter
Author by

Cutter

Updated on June 23, 2022

Comments

  • Cutter
    Cutter about 1 year

    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.

  • Dick Kusleika
    Dick Kusleika about 11 years
    If the collection is full of objects I think you need Set vTemp = Data(j)
  • Cutter
    Cutter about 11 years
    Thanks, but how could I write that function to sort Data by an arbitrary property? Do I have to hardcode the property's name in the function? I'd prefer passing it as an argument.
  • Authman Apatira
    Authman Apatira about 11 years
    Easy as pie. Use CallByName : pcreview.co.uk/forums/…