在VBA中,有一些用于存储数据的对象,例如字典、集合、数组、ActiveX组合框、ActiveX列表框、用户窗体组合框、用户窗体列表框、ArrayList等,SortedList也是其中的一种,它是一个集合对象,可用于存储任意类型的数据,包括数字、字符串、日期、数组、单元格区域、变量和对象。
除ArrayList外,几乎所有集合对象都没有内置的排序方法,这可能会促使你使用SortedList。并且,SortedList的另一个独特功能是键,Arraylist对元素的内容进行排序,Sortedlist对键进行排序。
可以使用SortedList存储来自多个源的数据,并对RAM中的元素进行操作。可以使用RAM代替Excel工作表、Word文档、PowerPoint演示文稿中的数据操作。并且,屏幕刷新、重新计算等不会浪费时间。
SortedList不是常规VBA库中的元素,它是System.Collections库的一部分,可以...\WINDOWS\Microsoft.NET\Framework\v4.0.30319\mscorlib.tlb或类似目录中找到该库。
在VBE中,单击菜单“工具—引用”,在“引用”对话框中找到“mscorlib.dll”并选取其前面的复选框链接到该库,如下图1所示。
图1
也可以在编写代码时创建链接:
ThisWorkbook.VBProject.References.AddFromFile"C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\mscorlib.tlb"
或:
ThisWorkbook.VBProject.References.AddFromguid"{BED7F4EA-1A96-11D2-8F08-00A0C9A6186D}", 2, 4
也可以使用后期绑定,在需要时创建对该库的链接:
createobject("System.Collections.SortedList")
创建SortedList
1.前期绑定创建SortedList常有下列几种方式:
Dim sl As New SortedList
sl.Add "完美Excel",6
或者:
Dim sl As SortedList
Set sl = New SortedList
sl.Add "完美Excel",66
或者:
Dim sl As Object
Set sl = New SortedList
sl.Add "完美Excel",666
2. 后期绑定创建SortedList常有下列几种方式:
Dim sl
Set sl =CreateObject("System.Collections.SortedList")
sl.Add "完美Excel",6
或者:
Dim sl
Set sl =GetObject("New:{026CC6D7-34B2-33D5-B551-CA31EB6CE345}")
sl.Add "完美Excel",66
填充SortedList
SortedList中的每个元素都有一个键和一个值。
1.键是唯一的。
2.SortedList中的元素按键升序排序。
3.有2种将元素添加到SortedList的方法:.Add和.Item(”key”)。
如果键已经存在,则.Add方法将产生错误。
方法.Item(”key”)替换已链接到现有键的元素。
4.元素可以是任意内容:数字、字符串、日期、数组、单元格区域、变量、集合、字典、空字符串、Nothing或对象。
5.只能逐项添加不同的元素。
Add方法
Add方法的第一个参数是键,第二个参数是元素内容。Add方法将元素添加到SortedList中并基于键对元素排序。排序会影响元素的索引号,但不影响其键或内容。
Dim sl As Object
Set sl =CreateObject("System.Collections.SortedList")
With sl
.Add "完美Excel","excelperfect"
.Add "1", "MS Excel"
.Add "2", 123
.Add "Now", Date
.Add "MyArray", Array("绿","红","蓝")
End With
Item方法
Item方法的参数是键。Item方法将元素添加到SortedList中并通过键对元素进行排序。排序会影响元素的索引号,但不影响其键或内容。
Dim sl As Object
Set sl =CreateObject("System.Collections.SortedList")
With sl
.Item("完美Excel")= "excelperfect"
.Item("2") = 123
.Item("MyCol") = New Collection
.Item("MyArray") = Array("绿","红","蓝")
End With
SortedList中的元素
当元素添加到SortedList中时会立即被排序。下面的代码演示在SortedList中添加不同的元素。
1.空字符串
With sl
.Add "test00", vbNullString
.Add "test01", ""
End With
2.字符串
With sl
.Add "test000", "完美Excel"
.Add "test001", "excelperfect"
End With
3.非打印字符
With sl
.Add "test0000", vbTab
.Add "test0001", vbLf
End With
4.数字
With sl
.Add "test00000", 12345
.Item("test00001") = RGB(23, 45, 67)
End With
5.日期
With sl
.Add "t0", Date
.Add "t1", CDate("2019-12-15")
.Item("t2") = DateSerial(2019, 12, 15)
End With
6.一维数组
With sl
.Add "t00", Array("aa1", "aa2","aa3")
.Add "t01", Split("bb1,bb2,bb3", ",")
.Item("t02") = Array("a1", "a2","a3")
End With
7.多维数组
With sl
ReDim arr(5, 8)
.Add "t000", arr
.Item("t001") = arr
End With
8.对象
With sl
.Add "t0000", Range("A1:D10")
.Item("t0001") = Range("E2:H10")
End With
9.用户窗体中的控件
With sl
Dim ctl
For Each ctl In Controls
.Add ctl.Name, ctl
Next ctl
End With
10.工作表中的ActiveX控件
With sl
Dim ctl
For Each ctl In ActiveSheet.OLEObjects
.Add ctl.Name, ctl
Next ctl
End With
11.工作簿中的工作表
With sl
Dim wks As Worksheet
For Each wks In Worksheets
.Add wks.Name, wks
‘.Item(.Count) = wks
Next wks
End With
SortedList的大小
Count属性代表SortedList中元素的数量,该属性同时代表SortedList中键的数量。
SortedList有两个等价属性:.Values.Count和.Keys.Count。
演示代码如下:
With sl
Dim wks As Worksheet
For Each wks In Worksheets
.Add wks.Name, wks
Next wks
MsgBox .Count
MsgBox .Values.Count
MsgBox .Keys.Count
End With
未完待续......