Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Sort VB Dictionary with Keys in "MMM-DD" format

I am trying to sort a VBA dictionary with the following keys and values. It is sorting the keys alphabetically. What I want is for it to treat the keys as "MMM-DD" format and sort. Is it possible? How do I make array list convert the keys to a date format (MMM-DD)?

May-1  12
Jan-21 14 
Dec-6  11
Feb-24 15 

This is the code

Sub TestSortByKey()

    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    dict.Add "May-1", 12
    dict.Add "Jan-21", 14
    dict.Add "Dec-6", 11
    dict.Add "Feb-24", 15
     
    ' Sort Ascending
    Set dict = SortDictionaryByKey(dict)
    PrintDictionary "Key Ascending", dict 
End Sub

Public Function SortDictionaryByKey(dict As Object, Optional sortorder As XlSortOrder = xlAscending) As Object
    
    Dim arrList As Object      
    Dim key As Variant, coll As New Collection
    Dim dictNew As Object
    Set arrList = CreateObject("System.Collections.ArrayList")   
    For Each key In dict
        arrList.Add key
    Next key
     
    arrList.Sort
     
    If sortorder = xlDescending Then
        arrList.Reverse
    End If
     
    
    Set dictNew = CreateObject("Scripting.Dictionary")
     
    For Each key In arrList
        dictNew.Add key, dict(key)
    Next key
     
    Set arrList = Nothing
    Set dict = Nothing     
    Set SortDictionaryByKey = dictNew
        
End Function

Public Sub PrintDictionary(ByVal sText As String, dict As Object)        
    Debug.Print vbCrLf & sText & vbCrLf & String(Len(sText), "=")        
    Dim key As Variant
    For Each key In dict.Keys
        Debug.Print key, dict(key)
    Next
End Sub

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

When adding to the dictionary convert the keys from text to Date using the CDate function should fix the sorting issue.

Sub TestSortByKey()

    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    dict.Add CDate("May-1"), 12
    dict.Add Cdate("Jan-21"), 14
    dict.Add CDate("Dec-6"), 11
    dict.Add CDate("Feb-24"), 15
     
    ' Sort Ascending
    Set dict = SortDictionaryByKey(dict)
    PrintDictionary "Key Ascending", dict 
End Sub
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading