Excel VBA 中使用集合和字典对比总结

郗peng 2021-07-12

展开全文

Excel VBA 中使用集合和字典对比总结

Collection（集合）对象

VBA中有一个集合对象（Collection），Collection 对象是有序保存数据的，我们把它理解为可以随意增减容量的动态一维数组，其保存的数据类型可以大部分类型，并且同一个集合内可以保存不同类型的数据。
与数组相比，集合有以下特点：

collection对象添加、删除元素的方法与数组不同及不能直接更新已经添加的元素。

备注： 集合的成员可以是大部分数据类型，包括对象（object类型），但是，通过 Tpye 定义的结构体的类型不能存储，在字典内也不能。

Collection 成员

object.count 属性：long类型，返回collection对象中元素的个数
object.add(item,key,before,after) 方法，给集合对象中添加新值，无返回值

部分	说明
object	集合对象表达式
item	必需的。要添加到集合中的元素或项目
key	可选的。在一个集合中这个key必需是唯一的字符串，不能出现重复，其作用是用于代替位置索引访问集合中的元素或项目，该key值不区分大小写，并且如果在key中已经使用某个字符串，那么，item中也不能出现该字符串。
before	可选的，介于1 和集合的成员数量（collection.count）之间。如果该值取值是字符串表达式，则为key值
after	同上，注意after和before不能同时指定

object.remove(index)方法，用于从集合中移除元素，无返回值
object.item(index) 或者 object(index) 方法，返回index所指的集合元素

示例

Sub Collection_Test()
    '声明并初始化一个Collection对象变量c
    Dim c As New Collection
    '最常规的Add方法的调用，可Add任意类型的对象
    c.Add "One"
    '有key参数的调用,这个字符串的key参数必须没有被占用
    c.Add "Two", "Second"
    '下面这句会导致运行时错误457，也即索引已经被占用
    ' c.Add "Hao", "Second"
    '下面这句传递了before参数，将"Zero"插入到原来的第二个元素之前，也即占用第二个元素的位置
    c.Add "Zero", , 2
    '下面这句传递了after参数，将"Oh yeah"插入到原来的第二个元素之后，也即现在的"Zero"之后，成为第三个元素
    c.Add "Oh yeah", , , 2
    '下面这句传递三个参数，key参数只是给集合中的这个新增元素一个除原有的数值索引外的另一个字符串索引，这个索引会更友好，更方便记忆
    c.Add "Five", "Fifth", , 4
    '下面是读取"Five"这个元素，可见字符串索引，是不区分大小写的
    Debug.Print c("fifth"), c("Fifth"), c(5), c.Item(5), c.Item("Fifth"), c.Item("fifth")
    '下面这一句会出错，错误提示：索引已经被占用
    ' c.Add "Six", "fifth"
End Sub

以上示例来自于：
VBA笔记——collection方法

示例2： 演示结构体不能保存到collection中

' 在普通模块中声明一个结构体
Public Type Person    
    name As String    
    age As Integer
End Type
Sub test()
    Dim mycollection As New Collection
    Dim p As Person    
    p.name = "john"
    p.age = 25    
    mycollection.Add p                             ' 此语句报错
    Debug.Print mycollection(1).name
End Sub

Dictionary（字典）对象

字典（Dictionary）对象是微软Windows脚本语言中的一个很有用的对象（经过我的测试也是有序的）。字典对象相当于一种键值对的集合，就是由具有唯一性的关键字（Key）和它的项（Item）组成。它等效于Perl语言的关联数组。
items保存在该关联数组中，可以保存任意类型的数据类型。每个item与唯一的key相关联。key用于获得单独的item，key通常是一个整数或字符串，其实也可以是任何类型的数据，除了数组和Tpye声明的结构体不能作为key。
由于字典不是Vba自带的，如果想要在代码编写过程中有智能提示，需要引用它。具体是[工具]–>[引用]–>找到以下位置：

C:\Windows\System32\scrrun.dll  （显示为选中：Microsoft Scripting Runtime Library）

Dictionary 成员

1. 方法：

方法	说明
Add	添加一新的key/item键值对到字典对象中
Exists	返回一个布尔值，提示字典对象中是否存在指定的key
Items	返回字典对象中的所有item的一位数组
Keys	返回字典对象中的所有key的一位数组
Remove	从字典对象中删除指定的键值对
RemoveAll	从字典对象中删除所有的键值对

2. 属性：

属性	说明
CompareMode	设置或返回字典对象中在比较key值时的比较模式，区分大小：binaryCompare设置的话区分大小写，TextCompare不区分大小写
Count	返回字典对象中键值对的数量或个数
Item	设置或返回字典对象中某个item的值
Key	在字典对象中对一个已经存在的key设置新值

'后期绑定：方便代码在其他电脑上运行，推荐。
dim dic as object
Set dic = CreateObject("scripting.dictionary")

'前期绑定：可以直接声明字典对象，有对象属性和方法的提示，但在其他没有勾选引用的电脑上无法正常运行。
'引用勾选：VBE窗体-工具-引用-勾选'Microsoft Scripting Runtime’
dim dic as New dictionary

'获取字典的键、值，字典计数，删除，判断键是否存在于字典
with activesheet
'dic.count：字典计数，字典中一共有多少条记录；
'dic.keys：字典的键，写入单元格以行写入，如需以列写入单元格，调用工作表函数transpose转置；
.cells(1,1).resize(dic.count,1) = application.worksheetfunction.transpose(dic.keys)
'清除工作表单元格内容
.cells.clearcontents

'dic.items：字典的值；
.cells(1,1).resize(1,dic.count) = dic.items

'判断某内容是否存在与字典的键中
if dic.exists("内容") then debug.print "字符串'内容’存在于字典的键中"

'清空字典，有时候其他过程也需要使用字典，当前过程已经使用完了，但我们又不想重新创建字典对象，这时候我们可以public字典全局变量，再清空字典，供新的过程使用该字典对象。
dic.removeall
'清除单个字典键-值对,key是字典的某个需要删除的键
dic.remove key
end with

 '==========================去重========================
dim dic as object
dim arr
dim st
Set dic = CreateObject("scripting.dictionary")

arr = array("可乐","雪碧","鸡翅",,"可乐","汉堡包","鸡翅")
for each st in arr
'字典的键是不能重复的，重复导入字典只会存在一个，可以利用字典这点特性去重。
'这里不需要字典的值，设置为空字符串或其他数值都可以。
dic(st) = ""
next
activesheet.range("a1").resize(dic.count,1) = application.worksheetfunction.transpose(d.keys)

' ==========================实现sumifs条件求和========================
Sub dic_sumif()
Application.ScreenUpdating = False
Dim dic As Object
Dim arr
Dim i As Byte

Set dic = CreateObject("scripting.dictionary")
With ActiveSheet
    arr = .UsedRange
    For i = 2 To UBound(arr)
    ' dic(arr(i,1))没有值是默认是0，通过下面方法对每一个水果的销量进行累加。
        dic(arr(i, 1)) = dic(arr(i, 1)) + arr(i, 2)
    Next
    '使用copy方法，将表头复制到e1，f1单元格
    .Range("a1:b1").Copy .Range("e1")
    '字典键去重纵向写入到单元格
    .Cells(2, "e").Resize(dic.Count, 1) = Application.WorksheetFunction.Transpose(dic.keys)
    For i = 2 To dic.Count + 1
    '循环输入字典键对应的值到f列
        .Cells(i, "f").Value2 = dic(.Cells(i, "e").Value2)
    Next
End With
set dic = Nothing
Application.ScreenUpdating = True
End Sub

字典与集合对比

字典比集合速度要快，有人测试要快三倍，字典应该采用hash值，所以查找速度快。
由于字典中有 Exists(key)方法，可以先进行判断，再操作，甚至直接赋值，而集合没有直接检查key是否存在。
如果想更新某个键值对，在集合中是不能直接进行的（可以先删除，然后再add），而字典中可以直接赋值，这点要方便多了。
对于字典中还没有key值，可以直接使用，例如： dic(key) = dic(key) + 1 ，这时dic(key)默认是 0 。
字典中的keys() 和 items() 分别返回一位数组，方便操作，而集合中没有该类方法。
特意提醒一下，字典有忘记add()的方法，即“呼之即来，挥之即去”。示例如下：

' 一、定义字典
Set d = CreateObject("Scripting.Dictionary")
' 二、呼之即来，挥之即去
d("张三“)=1     '相当于给字典赋值，张三过来（没有就生成）拿个1站一边去
d("李四”)=2     '相当于给字典赋值，李四过来（没有就生成）拿个2站一边去
d("李四”)=3     '相当于改变值，字典中已经有李四了，李四跑过来，丢下2换个3站一边去
注：这时字典中有两个人的存在，张三＝1 和 李四＝3，相当于实现了去重复的功能

s=d("张三")    's=1   即叫了声张三，张三就告诉你他拿的是1
s=d("李四")    's=3   即叫了声李四，李四就告诉你他拿的是3
s=d("麻子")    's=""  没有找到麻子怎么办呢，字典里就自动生成一个麻子d("麻子") ＝“”，告诉你他手上是空的
注：这时字典中有三个人的存在，张三＝1 ; 李四＝3;麻子＝“”

'  上文出处：http://club./thread-926188-1-1.html

以上示例出处：http://club./thread-926188-1-1.html

结论： 如果进行编程，推荐使用字典，但是也不能不使用集合，例如sheets(1)或者sheets(“明细”)就是一个集合的使用。