Sorting a collection by value

Question

I have a collection of dates with the format "1801,1802,1803,1710,1711 etc. I would like to be able to sort them by value so that the lowest ones are first e.g 1710,1711,1801,1802,1803.

The reason for this is so i can then put them into a string in the correct order.

I am not sure how to do this and any help would be appreciated. Thank you

You can find a collection sorting function in here: https://stackoverflow.com/questions/3587662/how-do-i-sort-a-collection — Oskar, Apr 27 '18 at 10:20
Possible duplicate of [How do I sort a collection?](https://stackoverflow.com/questions/3587662/how-do-i-sort-a-collection) — Darren Bartrup-Cook, Apr 27 '18 at 10:42

Gary's Student · Answer 1 · 2018-04-27T21:12:53.183

Here is a really simple example:

Sub Nathan()
    Dim c As Collection, arr, brr
    Dim MyString As String

    Set c = New Collection

    '   Part 1 build Collection


    arr = Split("1801,1802,1803,1710,1711", ",")
    For Each a In arr
        c.Add Val(a)
    Next a

    '   Part 2 put Collection into an array

    ReDim brr(1 To c.Count)
    For i = 1 To c.Count
        brr(i) = c.Item(i)
    Next i

    '   Part 3 sort the array

    Call aSort(brr)

    '   make and output a css

    MyString = Join(brr, ",")
    MsgBox MyString

End Sub

Public Sub aSort(ByRef InOut)

    Dim i As Long, j As Long, Low As Long
    Dim Hi As Long, Temp As Variant

    Low = LBound(InOut)
    Hi = UBound(InOut)

    j = (Hi - Low + 1) \ 2
    Do While j > 0
        For i = Low To Hi - j
          If InOut(i) > InOut(i + j) Then
            Temp = InOut(i)
            InOut(i) = InOut(i + j)
            InOut(i + j) = Temp
          End If
        Next i
        For i = Hi - j To Low Step -1
          If InOut(i) > InOut(i + j) Then
            Temp = InOut(i)
            InOut(i) = InOut(i + j)
            InOut(i + j) = Temp
          End If
        Next i
        j = j \ 2
    Loop
    End Sub

Consider using a scripting dictionary (because sort is already built-in)

EDIT#1:

To avoid writing a sort, you can use System.Collection.ArrayList. Here is an example that takes data from column A, sorts it, and puts the result in column B:

Sub AsortedAffair()
    Dim arr, o As Object, oc As Long, i As Long
    arr = Columns(1).SpecialCells(2)
    Set o = CreateObject("System.Collections.ArrayList")

    For Each a In arr
        o.Add a
    Next a

    o.Sort
    oc = o.Count
    For i = 1 To oc
        Cells(i, 2) = o.Item(i - 1)
    Next i

End Sub

It is equally easy to build a comma-separated string out of this.

Note that the index of this Item() property starts at zero rather than one.

Could you please elaborate what sort is already built-in for scripting dictionary? — omegastripes, Apr 27 '18 at 20:18
@omegastripes My error; the sort functionality is **not** built-in. I will edit my answer — Gary's Student, Apr 27 '18 at 20:33
@omegastripes I was thinking of the `System.Collections.ArrayList` — Gary's Student, Apr 27 '18 at 20:36
Is there any solution allowing to make fast data transfer between VBA array / collection and .Net arraylist back and forth, without loops? — omegastripes, Apr 27 '18 at 20:43
@omegastripes Not to my knowledge.......I have only looped over keys to fill the ArrayList and then looped again after the sort — Gary's Student, Apr 27 '18 at 20:48

Sorting a collection by value

1 Answers1