VBA学习笔记2-数据结构类型arraylist

一、这个东西是干什么的

在VBA里数组的排序是很让人头痛的事情，一般采用冒泡，选择，快速，插入，希尔等算法来对数组进行排序

• 冒泡，选择，插入排序等因为循环次数太多，效率感人
• 快速排序，因为条件苛刻，要求整数，并且最大和最小相差太大，速度也会很慢
• 希尔排序，算法真心它能看明白我，我看不明白它

有没有一种又简单，又好用的对数组进行排序的
答案是有，知道的就有2个现成的对象，可以调用，今天我们来学习其中一个ArrayList对象

二、创建ArrayList

1，前期绑定

单击“工具——引用”，在“引用”对话框中，找到并选中mscorlib.dll前的复选框

Dim arrlist As New arraylist

2，后期绑定

Sub lizi2()
    Dim arrList As Object
    Set arrList = CreateObject("System.Collections.ArrayList")
    
    Set arrList = Nothing
End Sub

三、常用方法和属性

1，添加

• Add方法，arrList.Add item
  itme 可以是数字，字符串，对象等变量，但是如果排序数据类型尽量统一，经过测试数字和文本比较会出错，用这方法的时候，都用数字好了

Sub lizi2()
    Dim arrList As Object
    Set arrList = CreateObject("System.Collections.ArrayList")
    arrList.Add 1
    arrList.Add 11
    arrList.Add 5
    arrList.Add 6
    arrList.Add 9
    Set arrList = Nothing
End Sub

• Insert方法在特定位置插入数据

Sub lizi2()
    Dim arrList As Object, i As Long
    Set arrList = CreateObject("System.Collections.ArrayList")
    arrList.Add 1
    arrList.Add 11
    arrList.Add 5
    arrList.Add 6
    arrList.Add 9
    arrList.Insert 0, 10
    For i = 0 To arrList.count - 1
        Debug.Print i & ":" & arrList(i)
    Next
    Set arrList = Nothing
End Sub

显示结果

0:10
1:1
2:11
3:5
4:6
5:9

2，判断集合大小

• ArrayList.Count，来判断，是从0开始到ArrayList.Count-1的，循环的时候注意

For i = 0 To arrList.count - 1
    Debug.Print i & ":" & arrList(i)
Next

3，重点来了排序的办法

3.1，升序排序

ArrayList.sort

3.2，降序排序

ArrayList.sort
ArrayList.Reverse
先排序，后面接一句这个就可以降序排序

Sub lizi2()
    Dim arrList As Object, i As Long
    Set arrList = CreateObject("System.Collections.ArrayList")
    arrList.Add 1
    arrList.Add 11
    arrList.Add 5
    arrList.Add 6
    arrList.Add 9
    arrList.Insert 0, 10
    arrList.Sort
    For i = 0 To arrList.count - 1
        Debug.Print i & ":" & arrList(i)
    Next
    Set arrList = Nothing
End Sub

显示结果

0:1
1:5
2:6
3:9
4:10
5:11

降序

Sub lizi2()
    Dim arrList As Object, i As Long
    Set arrList = CreateObject("System.Collections.ArrayList")
    arrList.Add 1
    arrList.Add 11
    arrList.Add 5
    arrList.Add 6
    arrList.Add 9
    arrList.Insert 0, 10
    arrList.Sort  '先排序，这时候是升序
    arrList.Reverse  '再来一句相反的，变降序了
    For i = 0 To arrList.count - 1
        Debug.Print i & ":" & arrList(i)
    Next
    Set arrList = Nothing
End Sub

显示结果

0:11
1:10
2:9
3:6
4:5
5:1

速度又快，有简单

4，复制集合的方法

• Clone方法

Sub lizi2()
    Dim arrList As Object, i As Long
    Dim arrList2 As Object, brr()
    Set arrList = CreateObject("System.Collections.ArrayList")
    Set arrList2 = CreateObject("System.Collections.ArrayList")
    arrList.Add 1: arrList.Add 11: arrList.Add 9
    arrList.Add 5: arrList.Add 6: arrList.Insert 0, 10
    Set arrList2 = arrList.Clone  '把没排序之前的集合复制一个新的
    arrList.Sort
    arrList.Reverse
    ReDim brr(1 To arrList.count + 1, 1 To 1)
    For i = 0 To arrList.count - 1
        brr(i + 1, 1) = arrList(i)
    Next
    Range("a1").Resize(arrList.count + 1, 1).Value = brr
    ReDim brr(1 To arrList.count + 1, 1 To 1)
    For i = 0 To arrList2.count - 1
        brr(i + 1, 1) = arrList2(i)
    Next
    Range("b1").Resize(arrList.count + 1, 1).Value = brr
    Set arrList = Nothing: Set arrList2 = Nothing
End Sub

结果

5，删除办法

• ArrayList.Clear
  直接全部干掉了
• ArrayList.Remove item
  item必须是添加进去的内容，不支持索引这里

Sub lizi2()
    Dim arrList As Object, i As Long
    Dim arrList2 As Object, brr()
    Set arrList = CreateObject("System.Collections.ArrayList")
    Set arrList2 = CreateObject("System.Collections.ArrayList")
    arrList.Add 1: arrList.Add 11: arrList.Add 9
    arrList.Add 5: arrList.Add 6: arrList.Insert 0, 10
    Set arrList2 = arrList.Clone
    arrList.Remove 11  '删除item是11的内容
    arrList.Sort
    arrList.Reverse  '降序
    '下面内容全部是返回单元格
    ReDim brr(1 To arrList.count + 1, 1 To 1)
    For i = 0 To arrList.count - 1
        brr(i + 1, 1) = arrList(i)
    Next
    Range("a1").Resize(arrList.count + 1, 1).Value = brr
    ReDim brr(1 To arrList.count + 1, 1 To 1)
    For i = 0 To arrList2.count - 1
        brr(i + 1, 1) = arrList2(i)
    Next
    Range("b1").Resize(arrList.count + 1, 1).Value = brr
    Set arrList = Nothing: Set arrList2 = Nothing
End Sub

显示结果

6，可以直接复制成一个数组

• ToArray方法

Sub lizi2()
    Dim arrList As Object, i As Long
    Dim arrList2 As Object, brr()
    Set arrList = CreateObject("System.Collections.ArrayList")
    arrList.Add 1: arrList.Add 11: arrList.Add 9
    arrList.Add 5: arrList.Add 6: arrList.Insert 0, 10
    brr = arrList.toarray  '从0开始的一维数组
    Range("a1").Resize(UBound(brr) + 1, 1).Value = _
            Application.WorksheetFunction.Transpose(brr)
    Set arrList = Nothing
End Sub

因为这个，只有单纯的item不能进行更为快速的查找，有没有一个对象，可以再添加进去的过程中就排好序呢？

下次分享更为健全的SortedList对象使用

学习来源：完美Excel微信公众号，感谢大佬的无私分享