我似乎无法使用ArrayList
. 该代码有效,这意味着它从Range("A1:A20")
. 它只是不对值进行排序。有任何想法吗?
Option Explicit
Public Sub Test()
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim ws As Worksheet
Set ws = wb.ActiveSheet
Dim rng As ListObject
Set rng = ws.ListObjects(1)
Dim arr() As Variant
arr = Application.Transpose(rng.DataBodyRange.Value)
Dim d As Scripting.Dictionary
Set d = GetUnique(arr)
Dim tempD As Scripting.Dictionary
Set tempD = New Scripting.Dictionary
Set tempD = SortDictionary(d)
Dim key As Variant
For Each key In d.Keys
Debug.Print key, d.Item(key)
Next key
End Sub
Private Function GetUnique(ByRef arr() As Variant) As Scripting.Dictionary
Dim v As Variant
Set GetUnique = New Scripting.Dictionary
On Error Resume Next
For Each v In arr
GetUnique.Add v, v
Next v
End Function
Private Function SortDictionary(dicObject As Scripting.Dictionary, Optional xlSortOrder As xlSortOrder = xlAscending) As Scripting.Dictionary
Dim obj As Object
Set obj = CreateObject("System.Collections.ArrayList")
Dim v As Variant
With obj
For Each v In dicObject
.Add v
Next v
.Sort
End With
Dim tempDic As Scripting.Dictionary
Set tempDic = New Scripting.Dictionary
Dim k As Variant
For Each k In obj
tempDic.Add k, dicObject(k)
Next k
Set SortDictionary = tempDic
End Function
那是因为当您将已排序的字典分配给 时tempD
,您指的是原始未排序的字典。尝试...
For Each key In tempD.Keys
Debug.Print key, tempD.Item(key)
Next key
希望这可以帮助!
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句