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

jammerules

New Member
Joined
Nov 16, 2009
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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


VBA 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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
no dictionary need if you use a query , 1 format to show ,1 to sort.
select DteAbbv: Format([dateFld],"mmm-dd") from table order by format([DateFld],"yyyymmdd")
 
Upvote 1
Solution
Thank you, Ranman. Here is my answer too.

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,242
Members
452,623
Latest member
russelllowellpercy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top