Sorting a collection of objects in VBA by using a comparator

Question

I'm trying to implement my own collection in VBA in which to store a list of objects. I'm new to VBA and I need to find a way in which to implement a solution.

What I have: a list of objects with different properties on which the most important is the duration of a specific action.

What I need: I want a sorted list of objects by using a comparator based on a time property (duration of a specific action).

What kind of Collection do you recommend? I need to create my own helper method in order to sort the elements of the collection? VBA has a comparator interface that I can implement on my own collection object?

It would be good to see your code. But sorting a collection of objects is illogical. From what you've described you might want to write a custom class. — , Jun 20 '19 at 12:57
yes, I want to create a custom class in which you store a colection. In the custom class colection I can add my objects and also I have a helper method to return a sorted colection based on duration — SocketM, Jun 20 '19 at 13:06
Have a look [here](https://stackoverflow.com/questions/42681650/how-to-make-a-list-in-vba-not-a-dictionary/42682697#42682697). — Daniel Dušek, Jun 25 '19 at 10:20

score 2 · Answer 1 · answered Jun 20 '19 at 13:04

I have solved quite similar problem using Types.

Option Compare Database
Option Explicit
Type tPersonalData
    FamilyName As String
    Name As String
    BirthDate As Date
End Type
Type tHuman
    PersonalData As tPersonalData
    Height As Integer
    CashAmount As Integer
End Type
Public Sub subUsage()
    Dim Humans(10) As tHuman
    Dim HumanCurrent As tHuman
    With HumanCurrent
        .CashAmount = 100
        .Height = 190
        .PersonalData.FamilyName = "Smith"
        .PersonalData.Name = "John"
        .PersonalData.BirthDate = CDate("01.01.1980")
    End With
    Humans(1) = HumanCurrent
End Sub

So after that you can implement your own sorting functions for an array, using given field.

score 1 · Accepted Answer · answered Jun 25 '19 at 13:36

I have created a sorting method based on Van Ng's, in order to sort my objects like that:

 Public Sub sortedCollectionByDuration()

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

 Set vTemp = New ClsCron
 Set vItm = New ClsCron

For i = 1 To myCol.Count - 1
        For j = i + 1 To myCol.Count
            If myCol(i).Duration > myCol(j).Duration Then

               Set vTemp = myCol(j)

                myCol.Remove j

                myCol.add vTemp, , i
            End If
        Next j
    Next i

   -- testing or logging

    For Each vItm In myCol
        Debug.Print vItm.ModuleName & " " & vItm.FunctionName & VBA.Format(vItm.Duration, "HH:MM:SS")

    Next vItm


End Sub

Sorting a collection of objects in VBA by using a comparator

2 Answers2