VBA进阶：SortedList详解之基础

在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

未完待续......