Sort Dictionary by Numerical Key

Question

I have a dictionary with integer keys and integer items, and just need to sort the dictionary based on the key, but all examples I've found work only for string keys.

Answer 1

Grab the keys as an array, sort that array, then use the sorted array to pull the values from the dictionary.

Sub Tester()

    Dim d As Object
    Dim i As Long, arr, k

    Set d = CreateObject("scripting.dictionary")


    With d
        .Add 3, 33
        .Add 1, 33
        .Add 2, 55
        .Add 5, 77
    End With

    arr = d.keys  '<< get keys in an array

    ' "sort" through the array, and get the values from the dictionary
    Debug.Print "key", "value"
    For i = 0 To UBound(arr)
        k = Application.Small(arr, i + 1)
        Debug.Print k, d(k)
    Next i

End Sub

Output:

  key          value
  1             33   
  2             55   
  3             33   
  5             77

Answer 2

edited to add a solution to output X and Y arrays

you could use SortedList object and build a helper sub like follows:

Sub SortDictionary(dict As Object)
    Dim i As Long
    Dim key As Variant

    With CreateObject("System.Collections.SortedList")
        For Each key In dict
            .Add key, dict(key)
        Next
        dict.RemoveAll
        For i = 0 To .Keys.Count - 1
            dict.Add .GetKey(i), .Item(.GetKey(i))
        Next
    End With
End Sub

to be exploited as follows:

SortDictionary dict '<--| give 'SortDictionary()' sub a dictionary object to sort by its keys

for instance here's a test:

Sub main()

    Dim dict As Object
    Dim key As Variant

    Set dict = CreateObject("Scripting.Dictionary")
    With dict
        .Add 5, 15
        .Add 4, 14
        .Add 3, 13
        .Add 2, 12
        .Add 1, 11
    End With

    SortDictionary dict

    With dict
        For Each key In .Keys
            Debug.Print key, .Item(key)
        Next
    End With
End Sub

---

what above can be easily twicked to return X and Y arrays out of dictionary keys and items, as follows:

Sub SortDictionaryToArray(dict As Object, XArray As Variant, YArray As Variant)
    Dim i As Long
    Dim key As Variant

    With CreateObject("System.Collections.SortedList")
        For Each key In dict
            .Add key, dict(key)
        Next
        ReDim XArray(0 To .Count)
        ReDim YArray(0 To .Count)
        For i = 0 To .Keys.Count - 1
            XArray(i) = .GetKey(i)
            YArray(i) = .Item(.GetKey(i))
        Next
    End With
End Sub

to be exploited in your main sub as follows:

SortDictionaryToArray dict, Xs, Ys

as you can see in this complete test:

Sub main()

    Dim dict As Object
    Dim i As Long
    Dim Xs As Variant, Ys As Variant

    Set dict = CreateObject("Scripting.Dictionary")
    With dict
        .Add 5, 15
        .Add 4, 14
        .Add 3, 13
        .Add 2, 12
        .Add 1, 11
    End With

    SortDictionaryToArray dict, Xs, Ys

    For i = 0 To UBound(Xs)
        Debug.Print Xs(i), Ys(i)
    Next
End Sub