看完这篇,如果你还不懂VBA字典,那我就没办法了

HI,大家好,我是星光。

在Excel基础教程里,咱们讲过函数+透视表是普通表格用户处理数据的最佳组合,函数用于数据清洗整理,透视表用于数据汇总分析。如果你接触过Power BI,会发现类似的情况,PowerQuery用于数据清洗整理,PowerPivot用于数据汇总分析。而VBA编程呢?也不例外,数组用于数据获取清洗整理,字典用于数据汇总分析……关于数组,可以看「零基础学VBA编程系列教程」中的数组四篇,关于字典——打个响指,今天就来给大家讲下什么是字典。

前方高能预警,本文共分8节,建议先收后看。

目录如下▼

1 什么是字典

2 如何创建字典

3 如何将数据存入字典

4 如何将数据从字典取出

5 如何移除字典元素

6 如何遍历字典元素

7 如何释放字典

8 思考和其它

……

1 丨

什么是字典

谈到字典,有些朋友可能会想到新华字典、康熙字典、英汉字典、有道字典等等。我今天说的字典,和这些家伙——都没关系。字典(Dictionary)是VBA编程中的一个对象,具有操作简单、运行高效的特点,常用于数据的条件查询、聚合汇总等。如果说数组是VBA处理数据的基础结构,那么字典就可以被称为核心。摊手,骗你娶你,不论男女。

我举个例子。

如上图所示的数据表,如果我们需要查询看见星光的特长,数组的解法步骤如下:

首先将数据源存入数组,然后遍历姓名,第1个人名不是,第2个人名不是,第3个人名不是,第4个还不是……一直到第7个人名才是,于是取特长,退出数组循环……

数组循环是线性查找的方式,只能一个个元素找过去,如果查找值过多,效率就很不理想。

如果是用字典呢?

首先将数据源装入字典,然后用一句代码就可以查询相关人名的特长了。

比如查询看见星光的特长..▼

字典('看见星光')

不用怀疑,不用循环,就是这么简单。

……

还记得吗?在数组四篇之什么是数组里,咱们讲过数组处理数据的过程可以分为5步。创建数组->将数据存入数组->数组运算->将数组数据写入Excel->释放数组。

字典处理数据的过程与数组十分相似:创建字典->将数据装入字典->将数据从字典取出->释放字典。

——那么如何创建字典?

2 丨

如何创建字典

如上文所讲,字典(Dictionary)是VBA编程的一个对象,但它并非VBA自带的妻妾,而是集成在动态链接库文件Scrrun.dll中,需要绑定文件后才能够使用。绑定文件有两种方式,前期绑定和后期绑定。

听起来很麻烦的样子,但操作起来其实——很简单。

先来说前期绑定

操作步骤如下▼

在VBE编辑器的[工具]选项卡下,单击[引用],在打开的[引用]对话框中勾选'Microsoft Scripting Runtim'选项,单击[确定]按钮,关闭对话框即可。

通过'前期绑定'的方式引用Scrrun.dll文件后,即可在VBA代码中利用Dim语句声明变量为字典对象,然后使用字典处理数据。

示例代码如下..▼

Sub 前期绑定()    Dim d As New Dictionary '声明一个字典对象    ……End Sub
第2句代码声明一个字典对象,其名为d。语法格式如下:
Dim 变量名 As New Dictionary
……

然后再说下后期绑定。

所谓后期绑定是指通过CreateObject函数创建对字典对象的引用,示例代码如下:

Sub 后期绑定()    Dim d As Object '声明一个对象    Set d = CreateObject('scripting.dictionary') '创建对字典的引用    ……End Sub

第2句代码声明一个对象类型的变量,其名为d。

第3句代码使用CreateObject函数创建对字典对象的引用……

……

那么前期绑定和后期绑定有什么不同呢?

有朋友说,星光你是不是个铁憨憨?很明显,前期绑定需要手动操作,后期绑定用代码就行了!——拜托,我五行多金,你可以说我憨,但不能带铁。而且,摊手,事情可没有这么简单▼

比如——在使用前期绑定后,编写代码时系统会自动显示字典的成员列表,而后期绑定不会显示;有些属性,前期绑定是支持的,但后期绑定不能使用(详情见文末说明);另外,通常前期绑定的代码运算效率要比后期快一些。

但是——前期绑定的代码不适合发送给其它用户使用,毕竟其它用户未必会去手动绑定字典对象;因此后期绑定的方式兼容性更强些。

总结——建议编写代码时采用前期绑定,编写完成后,如需发送他人使用,再改为后期绑定,如此鱼和熊掌必可兼得矣~

3 丨

如何将数据装入字典

创建完字典后,我们需要将数据装入字典。但在讲如何将数据装入字典前,需要先给大家说一下字典的结构。

从表格角度,通俗而言,字典是有两个一维数组或者说两列数据构成的特殊表。第1列是关键字,被称为Key;第2列是每个关键字对应的条目,被称为Item。其中每个关键字在字典中都是唯一的,不会也不允许出现重复值。

和数组所不同的是,字典的行数看起来并不固定,你往字典里装了多少个关键字,它就会有几行数据,每一行数据都由两列组成,第1列是关键字,第2列是关键字对应的条目。

那么如何将数据装入字典呢?

直接赋值就可以了。语法格式如下:

字典(关键字)=条目

举个例子。

Sub 字典添加数据()    Dim d As New Dictionary '声明一个字典对象    d('看见星光') = 99    d('Excel星球') = 98End Sub

第2行代码使用前期绑定的方式声明一个字典对象,其名为d。

第3行代码将关键字'看见星光'装入字典,对应的条目是99。此时字典有一行数据,如下图所示。

第4行代码将关键字'Excel星球'装入字典,对应的条目是98。此时字典就会有两行数据,如下图所示。

……

但是我们前面讲过,字典的关键字必须是唯一的,如果字典中已经存在了某个关键字,我们又往里面添加了同样的关键字,会怎么样呢?

举个例子。

Sub 字典添加重复数据() Dim d As New Dictionary '声明一个字典对象 d('看见星光') = 99 d('看见星光') = 59End Sub
第3行代码将关键字'看见星光'装入字典,对应的条目是99。
第4行代码再次装入一个相同的关键字'看见星光',条目是59。此时字典会将已经存在的关键字'看见星光'的条目更新为59。

如果我不想字典更新已经存在的关键字记录呢?凭什么把我从99分改为59分?对不对?我凭本事抄的答案你凭啥给我改成不及格?作为一个人,难道我连抄答案的自由都没有了吗?多么糟糕的组织会干出这样无情的事?——摘自跨国出版物《星光日记》。

解决方案代码如下:

Sub 判断字典是否存在相同关键字()    Dim d As New Dictionary '声明一个字典对象    d('看见星光') = 99    If Not d.Exists('看见星光') Then        d('看见星光') = 59    End IfEnd Sub
第4行代码使用字典的Exists方法判断是否存在关键字'看见星光';如果不存在,则添加该关键字并设置条目为59;如果存在,则忽略不处理。
Exists是字典的方法,作用是判断字典中是否存在指定关键字,结果返回一个布尔值,存在返回True,不存在返回False。语法格式如下:
字典.Exists(关键字)
……
以上是讲的如何将单个关键字装入字典,那么如何将一组数据批量装入字典呢?
如上图所示的数据表,需要将A列的姓名作为关键字,B列的特长作为条目,全部装入字典——摊手,古老的VBA并没有提供批量处理的方法,只能先将数据源存入数组,再遍历数组将每个元素一一存入字典。
示例代码如下:
Sub 数据表数据存入字典()    Dim d As New Dictionary    Dim arr, i As Long    arr = Worksheets('数据表').Range('a1').CurrentRegion    For i = 2 To UBound(arr) '遍历数组元素        d(arr(i, 1)) = arr(i, 2) '姓名是key,特长是item    NextEnd Sub
第4行代码将数据源数据存入数组arr

第5至第7行代码遍历数组元素,将姓名作为key,特长作为item,分别存入字典。

……

一个小问题,如何更新字典中指定关键字的条目呢?
假设字典中已经存在关键字'看见星光',对应的条目是99分;但是由于他这成绩是抄别人答案抄来的,所以需要更改为59分。这时应该怎么编写代码呢?——请返回本节中段,查看跨国出版物《星光日记》
……

4 丨

如何将数据从字典取出

了解了如何将数据装入字典,接下来再看一下如何将数据从字典中取出,毕竟装从来不是咱们的目的,咱们的目的是——要酷。字典的最大优势也并不在装,而在于根据指定关键字取对应条目的简洁和高效。
举个例子。
Sub 读取数据() Dim d As New Dictionary '声明一个字典对象 d('看见星光') = 99 d('Excel星球') = 98 MsgBox d('Excel星球')End Sub
上述代码中,第3行和第4行代码将两个关键字和对应条目存入字典,第5行代码获取关键字'Excel星球'的对应条目,并使用消息框显示。
获取字典指定关键字对应Item的语法格式如下:
字典(关键字)
就是这么简单~朴实无华且枯燥……
打个响指,我再举个实用的例子。

如上图所示的数据为例,需要根据A:B列的数据源,查询D列人名对应的特长,这就是所谓的条件查询了。

示例代码如下:
Sub 读取数据2() Dim d As New Dictionary Dim arr, brr, i As Long arr = Range('a1').CurrentRegion '数据源 For i = 2 To UBound(arr) '遍历数组,数据装入字典 d(arr(i, 1)) = arr(i, 2) 'key是人名,item是特长 Next brr = Range('d1:e' & Cells(Rows.Count, 'd').End(xlUp).Row) '查询区域 For i = 2 To UBound(brr) '遍历查询值 If d.Exists(brr(i, 1)) Then '如果字典存在查询值            brr(i, 2) = d(brr(i, 1)) '获取人名对应的条目 Else            brr(i, 2) = '查无此人' End If Next Range('d1:e' & Cells(Rows.Count, 'd').End(xlUp).Row) = brr Set d = NothingEnd Sub
代码解析:
第5至第7行代码将数据源数据存入字典,其中人名为关键字,特长为条目。
第8行代码将查询区域的数据存入数组brr。
第9行至第15行代码遍历获取相关人名在字典中对应的特长。第10行代码判断字典中是否存在相关人名,如果存在,则取对应的条目存入数组brr;如果不存在,则将结果设置为字符串'查无此人'。
第16行代码将查询结果写入单元格区域。
代码运行后结果如下:
……

字典除了支持通过指定关键字查询对应Item外,也支持一次性将所有的关键字或条目转换为一维数组,这需要用到字典的Keys和Items属性。

获取字典所有的Key,语法格式如下:

字典.Keys
获取字典所有的Item,语法格式如下:
字典.Items
……

我用麒麟双臂给大家举个典型的例子:数据去重。

如下图所示的数据表,A列人名存在重复值,需要去重复,获取不重复的人员名单,结果如D列。

示例代码如下:

Sub 去重复()    Dim d As New Dictionary    Dim arr, i As Long    arr = Range('a1').CurrentRegion    For i = 1 To UBound(arr)        If Not d.Exists(arr(i, 1)) Then            d(arr(i, 1)) = ''        End If    Next    Range('d:d').ClearContents    Range('d1').Resize(d.Count, 1) = Application.Transpose(d.Keys)    Set d = NothingEnd Sub
代码解析:

第4行至第9行代码将数据源的姓名作为关键字存入字典。很多新手朋友可能困惑于下面这句代码。

d(arr(i, 1)) = ''

代码的意思是姓名为关键字,条目为空文本。为什么要将条目设置为空文本?对于字典来说,Key和Item都是必须的,两者必须成对出现,缺一不可,但这里我们对Item并没有特别的要求,所以就随便赏个空文本打发一下算了。有朋友说,那我将代码改成下面这样行不行?——当然可以!我真诚的祝愿你梦想成真!

d(arr(i, 1)) = '我想有个真人女朋友'

需要重点说一下第11行代码,它的作用是将字典所有关键字存入当前工作表的D列。语句如下。

Range('d1').Resize(d.Count, 1) = Application.Transpose(d.Keys)
Count是字典的属性之一,作用是统计字典关键字的数量,d.Count也就是名称为d的字典有多少个关键字。

Range('d1').Resize(d.Count, 1),以d1单元格为起点,向下扩展指定行数,作为存放字典所有关键字的单元格区域。

d.Keys是以一维数组的形式返回字典所有的关键字。在数组四篇里咱们讲过,一维数组就是一行数据,需要通过Transpose函数进行一次转置才能转换为二维,然后才能直接写入垂直单元格区域。

综上所述——这四个字老霸气了,但更霸气的是随后的四个字——您就懂了!

……

扩展一下,我再举个与之相似的例子。

如下图所示,需要在D:E列,获取A:B列不重复的人名及其特长数据。

示例代码如下:

Sub 去重复2()    Dim d As New Dictionary    Dim arr, i As Long    arr = Range('a1').CurrentRegion    For i = 1 To UBound(arr)        If Not d.Exists(arr(i, 1)) Then            d(arr(i, 1)) = arr(i, 2)        End If    Next    Range('d:e').ClearContents    Range('d1').Resize(d.Count, 2) = Application.Transpose(Array(d.Keys, d.Items))    Set d = NothingEnd Sub
和上一段代码相比,所不同的有两句。
第7行代码,d(arr(i, 1)) = arr(i, 2),指定了关键字对应的条目为arr(i,2),也就是人员的特长。为什么不再是d(arr(i, 1)) = ''了?因为你需要Item为指定值,不能再随便打发了。人呐,就是这么现实。
第11行代码放置数据的列数由1列改为了2列。
Range('d1').Resize(d.Count, 2)

代码使用以下语句一次性获取字典的Keys和Items,存入一个一维数组(在数组四篇里咱们讲过,Array函数结果为一维数组),最后再通过一次转置将一维数组修改为2维,直接写入指定单元格区域。

Application.Transpose(Array(d.Keys, d.Items))

……

5 丨

如何移除字典中的元素

前面讲了如何对字典的数据进行存和取,这是使用字典最常见的两种情况;但还有一种情况:移除,也就是将数据从字典中移除。

如果移除指定关键字,可以使用方法Remove,语法格式如下:

字典.Remove 关键字
示例代码如下:
Sub 移除指定关键字()    Dim d As New Dictionary '声明一个字典对象    d('看见星光') = 99    d('Excel星球') = 98    d.Remove '看见星光'End Sub

第3行和第4行代码分别添加两个关键字到字典中。此时字典有两行数据,如下图所示:

第5行代码将关键字'看见星光'移除,关键字和条目总是成对出现的,关键字移除了,对应的条目也就移除了,于是此时字典就还剩下一个关键字:
除了根据指定关键字移除数据外,还可以使用方法RemoveAll将字典数据一次性清空。语法格式如下:
字典.RemoveAll

当一个字典需要重复使用的时候,就需要用上RemoveAll方法了。

举个例子,如下图所示,需要在H列获取每期开奖号码不重复的号码,并用逗号作为分隔符,合并成一个字符串。

示例代码如下:
Sub 全部移除字典中的元素()    Dim d As New Dictionary    Dim arr, i As Long, j As Long    arr = Range('a1').CurrentRegion    For i = 2 To UBound(arr) '遍历行        d.RemoveAll '移除字典中所有的元素        For j = 2 To UBound(arr, 2) - 1 '遍历列            If Not d.Exists(arr(i, j)) Then                d(arr(i, j)) = '' '将不重复的号码存入字典            End If        Next        arr(i, UBound(arr, 2)) = VBA.Join(d.Keys, ',') '合并为一个字符串    Next    Range('a1').CurrentRegion = arr    Set d = NothingEnd Sub
第5行代码遍历处理每行数据。

第6行代码在实际处理每行数据之前,先清空字典中的所有元素。

第7至第11行代码遍历当前行每列的元素,只将唯一值存入字典。

第12行代码使用Join函数,以逗号为分隔符,将当前字典所有的关键字合并成为一个字符串,并存入结果数组。

arr(i, UBound(arr, 2)) = VBA.Join(d.Keys, ',')
然后再遍历处理下一行数据……

……

6 丨

如何遍历字典中的元素

有时候,为了筛选出符合条件的数据,我们需要像遍历数组一样,遍历字典中的每个元素。这通常需要先获取字典的Keys集合,再遍历每个Key去筛选字典中符合条件的数据。

什么意思呢?举个例子还是我。

如下图所示的数据表,需要筛选出人名重复出现次数大于2次的人员名单,以及相关出现次数,结果参考C:D列。

示例代码如下:
Sub 遍历字典元素_索引法()    Dim d As New Dictionary    Dim arr, aKey, aRes, i As Long, k As Long    arr = Range('a1').CurrentRegion    For i = 2 To UBound(arr)        d(arr(i, 1)) = d(arr(i, 1)) + 1    Next    aKey = d.Keys    ReDim aRes(1 To d.Count, 1 To 2) '结果数组    For i = 0 To UBound(aKey)        If d(aKey(i)) > 2 Then '次数大于2次            k = k + 1            aRes(k, 1) = aKey(i)            aRes(k, 2) = d(aKey(i))        End If    Next    Range('c:c').ClearContents    Range('c1') = '重复2次以上的人名'    Range('c2').Resize(k, 2) = aRes    Set d = NothingEnd Sub

代码解析:

第4行代码将数据源数据存入数组arr。

第5行至第7行代码将数组arr中的人名存入字典,重点是下面这句代码。

d(arr(i, 1)) = d(arr(i, 1)) + 1
这句代码类似于咱们在什么是变量里讲过的计数器k=k+1。

作为赋值语句,它首先运算的是等号右侧的表达式:d(arr(i,1))+1。有趣的是,在代码运行这里的d(arr(i,1))的时候,我们还没有将arr(i,1)的关键字存入字典,所以正常理解,这句代码应该返回程序错误,但事实并没有。字典(关键字)语句的运算规则是,如果字典中存在指定关键字,则返回对应的Item,否则会将该关键字存入字典,同时将其对应的Item设置为Nothing。

因此,在第一次运行这句代码时……
d(arr(i, 1)) = d(arr(i, 1)) + 1
等于:
d(arr(i, 1)) = Nothing + 1
等于:
d(arr(i, 1)) =1
也就是在字典中存入一个关键字arr(i,1),对应的条目为1。

当出现第2次出现同名的关键字时…

d(arr(i, 1)) = d(arr(i, 1)) + 1
等于:
d(arr(i, 1)) = 1 + 1
等于:
d(arr(i, 1)) = 2
也就是在字典将关键字arr(i,1)对应的条目更新为2.

……以此实现了相同值出现次数在字典中不断累加的效果。

你品品,是不是这个道理?品不出来?没事,不怕你堕落,我送你一瓶82年的雪碧,你慢慢品。

……

第8行代码返回字典中所有的Key,结果是一个下标为0的一维数组,命名为aKey。

第9行代码声明一个结果数组,行数为字典的个数,列数是2列,一列放人名,一列放次数。

第10行至第16行代码采用索引的方式遍历数组aKey,查看每一个Key在字典中的Item是否大于2次,如果大于2次则将Key和Item分别存入结果数组。相似的套路咱们在数组4篇的数组运算里详细讲过了,这里就不再敲击键盘,免得浪费它所剩无几的生命力。

……

7 丨

如何释放字典内存

如果你是细心的人儿,会发现前面每小节的代码末尾都会出现下面这条语句。
Set d = Nothing
在章节什么是变量里咱们讲过了,它的作用是释放对象变量所占用的内存,提高代码运行效率,虽然它未必是必须的,但聪明的您最好像优秀的我一样,养成使用它的好习惯。

有朋友可能会问,我用字典.RemoveAll语句清空字典,是不是可以代替Set 字典=Nothing?答案是否定的,你失恋了,伤感的把合租的房子清空,和你一怒之下把房子给烧没了,是两个概念好吧?前者房子虽然空了,但还在,还能住新欢和旧爱,后者是连房子都没了,再爱也都烟消云散了……。

8 丨

思考题和其它

最后说一下几个零散的知识点。

……

1丨前期绑定和后期绑定的不同

在本章第2节如何创建字典,咱们讲过,有些属性前期绑定是支持的,但后期绑定并不能使用。这个有些属性,其实指的就是Items和Keys。

在前期绑定的情况下,我们可以使用以下语句读取Keys集合的指定索引元素。

读取字典Keys的第2个元素,并赋值为变量strKey..▼

strKey = d.Keys(1)
但后期绑定并不支持运行该语句,必须先将Key或Item转换为数组才能够索引遍历。

读取字典Keys的第2个元素,并赋值为变量strKey..▼

r = d.Keys'关键字集合赋值数组rstrKey = r(1)
……

2丨如何让字典不区分字母大小写

字典是默认是区分字母大小写的。如果不需要区分字母大小写,有两种解决方法……

一种是设置字典的CompareMode属性为TextCompar,示例代码如下:

Sub 不区分字母大小写() Dim d As New Dictionary d.CompareMode = TextCompare d('a') = 1 MsgBox d('A')End Sub
一种是将所有的字母统一转换为大写(UCase)或小写(LCase),这种方式明显修改了原值,因此通常不建议使用。示例代码如下▼
Sub 不区分字母大小写2()    Dim d As New Dictionary    d(LCase('a')) = 1'LCase将字母统一转换为小写    MsgBox d(LCase('A')) End Sub
……

3丨字典对数据类型的态度是严格的

字典对数据类型的态度是严格的——这句话是什么意思呢?我们知道Excel是一款对数据类型要求很宽松的软件,数值可以分为文本型数值和纯数值两种,在VBA的逻辑判断中,文本型数值和纯数值是相等的,比如以下代码返回True。

Sub t() MsgBox '1' = 1End Sub
但在字典中,纯数值和文本型数值并不相等。

举个例子,示例代码如下:

Sub 数据类型1()    Dim d As New Dictionary    d(1) = '爱就一个字'    MsgBox d.Exists('1')End Sub
第3行代码将纯数值1作为Key存入字典。

第4行代码判断文本型数值1在字典中是否存在,结果返回False。

知道这个知识点有什么用?

当你需要处理的Key有数值类型时,最好将源数据和查询值都统一转换为文本的形式,避免踩坑。如何统一转换为文本的形式?可以通过声明一个字符串类型的变量,强制进行转换。

示例代码如下:

Sub 数据类型2() Dim d As New Dictionary Dim strKey As String '定义一个字符串类型的变量 strKey = '1' d(strKey) = '爱就一个字' strKey = '2' d(strKey) = '我要说两次' strKey = 1 '强制转换为字符串 MsgBox d.Exists(strKey) '结果返回TrueEnd Sub
……

4丨字典常用方法和属性的另外表达方式

如果你看的VBA代码多了,可能会看到有人使用以下方式往字典中添加关键字和对应条目:

d.Add '夏天','吃冰棍'

其中夏天是Key,吃冰棍是Item。

但这种方式在常规VBA代码中我并不推荐使用,原因很简单,它不够灵活。如果字典中已存在相同关键字,该语句会返回错误值,而且它并不支持更新相关条目。当然,最重要的是,它打字太多了,你数数,相比下句代码它多打了几个字?别不把手指当亲骨肉啊同志们呐!!

d('夏天')='吃冰棍'

与之相似的还有下面几种语句,同样因为不够灵活,打字偏多等不推荐使用。

Sub test1()    Dim d As New Dictionary    d.Add '看见星光', '曾经20岁' '添加一个关键字看见星光    MsgBox d.Item('看见星光') '获取关键字看见星光对应的条目    d.Item('看见星光') = '年年18岁' '将看见星光对应的条目修改为'年年18岁'End Sub

5丨如何学习更多VBA编程知识和技巧?

(0)

相关推荐