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中进行copy和paste的时候,无规律的出现 “类Worksheet的Paste方法无效”错误 怀疑是电脑太快,copy操作还没有完成,就直接运行paste,导致paste出错。 所以在copy操作后,sleep一小段时间,结果真的解决了问题。
评论
sss

sss

3个月前
public static void main(String[] args) {
System.out.println("hello world!!")
}