虽然VBA可以直接用Application.WorksheetFunction
来直接调用EXCEL中的Vlookup
函数(可缩略为Application.Vlookup
或者WorksheetFunction.Vlookup
)。可用用For ... Next
或 Do While ... Loop
循环逐行调用.Vlookup
,或者用Range().FormulaR1C1 = "=VLOOKUP()"
来给一列数据直接处理。
但这两种方法效率都不高,最好是能有一种VBA自有的数据存储方案,可以高效的调用和查询数据,所以有了这篇笔记(主要参考内容取自VBA for smarties站点,地址见底部reference)
Dictionary 简介
Dictionary
是VBA中collection
对象的一种,可用于整合和组织不同类型的数据。Dictionary
中的数据均临时存储于内存中,因而可以快速的读取和操作。比起EXCEL函数从硬盘调用数据并运算,这种处理方式让程序有可观的提高。
Dictionary
可整合拥有共同属性的数据,所谓共同属性,即Key。一个Dictionary
只能拥有不重复的key。就好像在EXCEL中用VLOOKUP
函数查询时,被查询的数据集里只能拥有唯一的索引ID一样,否则就无法实现精确查找。
Dictionary
并不是VBA标准库中的一员,它出自Microsof Scripting Runtime Object Library。VBE调用前,可以先行引用此库(中文版VBE中:工具 -> 引用 -> 勾选”Microsoft Scripting Runtime”)。
创建Dictionary
创建Dictionary
的语句是CreateObject("Scripting.Dictionary")
。简单的创建一个Dictionary
可使用以下语句:
|
|
如果要添加一个条目(Item),可使用.Add
这个method:
|
|
那么Name
就是这个条目的Key,而Alex Ruffle
是key对应的值。MsgBox .Count
弹出消息框,告诉你这个Dictionary
中有多少条目。
为Dictionary添加和替换条目
Dictionary
只能逐条填充,且每个item只能有唯一的key。
- item可以是各种类型的数据(numbers, strings, dates, arrays, ranges, variables, collections, dictionaries, an empty string, nothing 和 objects)。这个概念上,一个
Dictionary
与R语言中的List有些类似。 - key可以是number单个, string, date 或者 object, 或者是包含单个number, string, date 或者 object的变量, 但不能是array,哪怕是1维数组。
添加和修改item有4种方法:
Method .Add
上一节里已经提到,最基本的给Dictionary
添加item的方法。
要注意的是,在这种添加Item的方法下,如果一个key已经被添加,则无法再添加相同的key。如下面的这段代码就会报错:
|
|
Method .Item()=
|
|
.Item()
的括号中接key的名字(即”Name”),等号后面跟着这个item的内容。
从这个用法也可以管中窥豹,一探Dictionary中的索引方式。
通过.Item(key)
来搜索对应key下的item内容,如果key不存在,则等号赋值添加一对新的key和item;如果key存在,那么等号可以重新赋值。如:
|
|
Method =.Item()
|
|
这是一个比较奇葩的方式。将key为Name的item赋值给变量x0。如果这个key不存在,则该key会自动添加到Dictionary
中,但是item内容为空。
此种方法无法用于修改Dictionary
中已存在的Item的数据。
调用对象变量(object variable)
Dictionary
是个对象,所以也可以将它赋值给对象变量(object variable)。
|
|
上面最后两行实现同样的效果,分别创建了条目Name
及Gender
。
此处修改指定item的逻辑与第二种方式无异。
键(Keys)
如上所述,Key可以是除了数组以外的许多种对象或数据格式,但是key的长度只能为1。
键的类型
目前为止,本文代码中用到的均为string key。除此之外,我们还可以使用多种类型的数据或对象作键。
|
|
以上是可能会常用的key的类型,有兴趣的话还可以继续探索其他类型的key。
键的唯一性
需要注意的是,当我们使用string key的时候,字母的大小写也需要注意区别。
VBA提供了两种模式,用.CompareMode
属性来区分。
此属性默认值为0,表示区别大小写。如果赋值为1,则不区分大小写。
|
|
以上返回8个排它的key,而下面的代码只返回两个key,且返回值均默认为小写。
|
|
条目(Items)
条目可以为空集,也可以是上文提到的任意类型数据
.Items
及.Keys
属性
两者以array的形式分别返回所有的item和key。Dictionary
提供.Count
方式返回其中元素的数量。同样我们也可以用UBound()
函数去计算数组的上限,从而返回元素的数量。在数组中,第一个元素下标为0,所以UBound(array) + 1
才是一个数组的元素总量。
|
|
.Exists(key)
查看键是否存在
为避免Dictionary
的录入错误,我们可以在添加新的key之前先行检查被添加项是否已经存在。这里要用到的方法是.Exists(key)
。
|
|
下一篇Dictionary将主要介绍Dicitonary中元素的提取、删除、合并以及实例的应用。