VBA 字典 Dictionary 按Key排序(利用System.Collections.ArrayList)

原创文章,欢迎转载:http://miao.blog/article/vba-dictionary-sort-by-key

' sort dictionary, sortBy: ASC, DESC
Function SortDictionary(dic As Dictionary, Optional sortBy As String = "ASC")
    
    ' create ArryaList Object
    Dim objArrayList As Object: Set objArrayList = CreateObject("System.Collections.ArrayList")
    
    ' add dictionary key to ArrayList
    Dim key
    For Each key In dic.Keys
        objArrayList.Add key
    Next
    
    ' Sort (ASC)
    objArrayList.Sort
    
    ' create a new dic
    Dim new_dic As Dictionary: Set new_dic = New Dictionary
    
    ' sort by ASC
    If sortBy = "ASC" Then
        For i = 0 To objArrayList.count - 1
            key = objArrayList(i)
            new_dic.Add key, dic(key)
        Next
    ' sort by desc
    Else
        For i = objArrayList.count - 1 To 0 Step -1
            key = objArrayList(i)
            new_dic.Add key, dic(key)
        Next
    End If
    
    Set SortDictionary = new_dic
End Function
推荐阅读
使用VBA调用Outlook发送邮件
评论