0

I'm trying to write a generalized sort that will sort a VBA collection on the key. I'm using the sort here as a starting point.

The sort doesn't work if the collection contains objects (as shown below). How do I modify the code to get the key to use for the sort?

The information I was able to find gets the collection of keys rather than getting the key for a single item (for example here: VBA collection: list of keys).

The current code shown below gives "Object doesn't support this property or method" on the Debug.print line. 

Public Function sort(ByRef col As VBA.collection)
    For i = 1 To col.Count - 1
        For j = i + 1 To col.Count
            Debug.Print ("Key: " & col(i))
            If col(i) > col(j) Then
                'store the lesser item
                temp = col(j)
                'remove the lesser item
                col.Remove j
                're-add the lesser item before the greater item
                col.Add temp, temp, i
            End If
        Next j
    Next i
End Function

Sub test()
    Dim col As collection
    Set col = New collection
    Dim pi As ProgramIncrement
    ' val
    Set pi = New ProgramIncrement
    pi.name = "foo3"
    col.Add pi, pi.name
    ' val
    Set pi = New ProgramIncrement
    pi.name = "foo0"
    col.Add pi, pi.name
    ' val
    Set pi = New ProgramIncrement
    pi.name = "foo2"
    col.Add pi, pi.name
    ' val
    Set pi = New ProgramIncrement
    pi.name = "foo1"
    col.Add pi, pi.name

    ' sort
    sort col
    Debug.Print ("Done.")

End Sub

' ---------------------------------------------------------------
'
' Class to represent Program Increment
'
' ---------------------------------------------------------------

Public name As String

Public sprints As New collection
John
  • 3,458
  • 4
  • 33
  • 54
  • 5
    You can't access the keys of a collection. Use a `Dictionary` for that... but this has to be the single most inefficient use of a collection or dictionary - you definitely want to use arrays instead: what makes a dictionary useful is its extremely efficient key lookup mechanism - iterating keys and accessing items by index completely defeats its very purpose. Also collections *want* to contain objects, and be iterated with `For Each` - see [this article](https://analystcave.com/vba-for-loop-vs-for-each-loop/) for more info. – Mathieu Guindon Jun 08 '19 at 16:27
  • You can use .NET (non-generic) collection classes in in VBA. This example shows the SortedList being used in VBA https://exceldevelopmentplatform.blogspot.com/2018/09/vba-net-interop-systemcollectionssorted.html 'Set sortedList = CreateObject("System.Collections.SortedList")' – S Meaden Jun 08 '19 at 18:46
  • ADODB disconnected recordsets can be sorted or filtered. https://stackoverflow.com/questions/29552725/sorting-files-by-numerical-order – Noodles Jun 08 '19 at 23:34

0 Answers0