如何按MMM-DD日期格式对VBA字典的键进行排序？

当然可行！你的代码当前是对字符串键执行字母排序，才会出现Dec-6排在Feb-24前面的问题。要实现日期逻辑的排序，核心是把MMM-DD格式的字符串键转换为日期值，再基于日期排序。下面提供两种实用方案：

方案1：自定义比较器排序（推荐） #

通过实现IComparer接口自定义排序规则，直接解析字符串键为日期后比较，无需额外处理数据结构：

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
     
    ' 按日期升序排序
    Set dict = SortDictionaryByDateKey(dict)
    PrintDictionary "按日期升序排序结果", dict
End Sub

Public Function SortDictionaryByDateKey(dict As Object, Optional sortorder As XlSortOrder = xlAscending) As Object
    Dim arrList As Object
    Dim key As Variant
    Dim dictNew As Object
    Set arrList = CreateObject("System.Collections.ArrayList")
    
    ' 将字典键加入ArrayList
    For Each key In dict
        arrList.Add key
    Next key
    
    ' 使用自定义日期比较器排序
    arrList.Sort CreateObject("VBAProject.DateComparer")
    
    ' 处理降序需求
    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 SortDictionaryByDateKey = dictNew
End Function

' 注意：需新建一个类模块，命名为「DateComparer」，并粘贴以下代码
Implements IComparer

Private Function IComparer_Compare(ByVal x As Variant, ByVal y As Variant) As Integer
    Dim dateX As Date, dateY As Date
    ' 拼接当前年份（不影响月日排序逻辑），将MMM-DD转为完整日期
    dateX = DateValue(x & "-" & Year(Date))
    dateY = DateValue(y & "-" & Year(Date))
    
    If dateX < dateY Then
        IComparer_Compare = -1
    ElseIf dateX > dateY Then
        IComparer_Compare = 1
    Else
        IComparer_Compare = 0
    End If
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

方案2：日期值映射排序（无需额外类模块） #

将键与对应的日期值打包存入ArrayList，按日期排序后提取原键，实现更简单：

Public Function SortDictionaryByDateKey(dict As Object, Optional sortorder As XlSortOrder = xlAscending) As Object
    Dim arrList As Object
    Dim key As Variant
    Dim dictNew As Object
    Dim item As Variant
    
    Set arrList = CreateObject("System.Collections.ArrayList")
    
    ' 存入数组：(转换后的日期值, 原字符串键)
    For Each key In dict
        arrList.Add Array(DateValue(key & "-" & Year(Date)), key)
    Next key
    
    ' 按数组第一个元素（日期值）排序
    arrList.Sort
    
    ' 处理降序需求
    If sortorder = xlDescending Then
        arrList.Reverse
    End If
    
    ' 构建排序后的新字典
    Set dictNew = CreateObject("Scripting.Dictionary")
    For Each item In arrList
        dictNew.Add item(1), dict(item(1))
    Next item
    
    Set arrList = Nothing
    Set SortDictionaryByDateKey = dictNew
End Function

关键说明 #

• 两种方案都通过DateValue函数将MMM-DD字符串转为日期对象，拼接当前年份是因为DateValue需要完整的年月日格式，而年份不影响月日的排序逻辑。
• 方案1的自定义比较器扩展性更强，适合后续调整排序规则；方案2无需新增类模块，代码更轻量化。

内容的提问来源于stack exchange，提问作者Jrules80