Sorting a collection of objects in VBA
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
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)
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 about 11 yearsIf the collection is full of objects I think you need Set vTemp = Data(j)
Cutter about 11 yearsThanks, 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 about 11 yearsEasy as pie. Use