在Excel VBA中用字典对象存储多列数据的方法

张文晓

摘要：本文旨在介绍一种方法，实现如何用VBA的字典对象，处理多列数据。并且结合工程实际，说明此方法在实际编程中如何应用。

关键词：Excel;VBA;字典;多列数据

中图分类号：TP311      文献标识码：A      文章编号：1009-3044（2019）02-0214-04

当我们基于VBA编程的时候，往往面对的源数据，是存储在多个表单里的。并且每一个表单一般含有多列数据。在运算过程中，要调用这些源数据，就涉及数据的查询、检索。当然，最简单的方式就是去遍历单元格，如果数据量不大，这样的方式也是可以的。但如果数据量很大，需要频繁读写操作，往往会导致程序运行效率较低。

这时经常用到的解决方法就是使用数组保存数据，在内存中进行数据处理和加工，最后一次性更新工作表，这样的操作方式往往会极大提升程序的运行效率。

但是，数组只能用于存储数据，而我们往往需要对数据进行查询，用于计算。有时，我们也需要判断某个数在数组里是否存在，有时要对数组进行扩展。同时，数组中的各个数据，它们的类型必须是相同的。

Dictionary是随着Visual Basic 6.0新增的，具有某些強大功能的对象。字典不是VBA内置的类型，它是Windows脚本语言的。但其实字典在VBA中也是非常重要的，它非常适用于需要进行非重复性数据的操作。字典其实就是一些“键-值”对。在很多场合，它都能起到很大的作用，使用起来非常方便，有类似于微型数据库的作用，可用于临时保存一些数据信息。基于字典存储数据的特点，人们往往认为字典只能实现字典里的“值”，只能是单一的一个数据。

本文的目的是介绍如何把多个数据作为字典的“值”装入字典。

1 了解字典

1.1 什么是字典？

Dictionary 对象是“Scripting   Runtime   Library”的一部分，最早VBScript 中实现.（SCRRUN.DLL），它可以将任何形式的数据的条目存储在数组中。每个条目都与一个唯一的关键字相关联。该关键字用来检索单个条目，通常是整数或字符串，也可以是对象、集合等任何类型。

字典对象的关键功能以及代码：

1.2 VBA中的字典与现实世界中的字典

为了说明什么是字典，可以拿现实世界中的字典来举例。例如，你如果想查一个词的意思，你会直接在字典里查这个词，而不会把整个字典都通读一遍。这个词，在VBA的字典对象里，就是Key，而词的意思，就是Item。

再举一个实际生活中的例子，像手机里的电话本，人名就是Key（键），电话号码就是Item（值）。

1.3 使用字典的实例

下面举一个使用字典的实例，下面代码按照下面步骤运行：

1） 将三个人名，每个赋一个值代表年龄，添加到字典;

2） 弹出对话框让用户输入一个人名

3） 在字典里检索此人是否在字典里

4） 如果是，显示人名和它的年龄

5） 如果不是通知用户不存在

Sub CheckAge（）

' 在Visual Basic里选 Tools->References

' 在下拉菜单里勾选 "Microsoft Scripting Runtime"

Dim dict As New Scripting.Dictionary

' 将人添加到字典

dict.Add key：="Vincent"， Item：=33

dict.Add key：="Terry"， Item：=34

dict.Add key：="Jerry"， Item：=23

Dim sPerson As String

' 要求用户输入人名

sPerson = InputBox（"Please enter the name of a person"）

If dict.Exists（sPerson） Then

MsgBox sPerson & " exists and the age is " & dict（sPerson）

Else

MsgBox sPerson & " does not exist."

End If

Set dict = Nothing

End Sub

1.4 如何创建字典

通过上述实例，我们可以大概了解在程序里怎么使用字典对象。下面我们来说明如何创建字典。

要使用字典对象，首先要在VBA里添加引用：

1） 在Visual Basic 菜单里，选Tools->References

2） 找到Microsoft Scripting Runtime勾选

在程序代码里声明字典如下：

Dim dict As New Scripting.Dictionary

或者：

Dim dict As Scripting.Dictionary

Set dict = New Scripting.Dictionary

像这样创建字典叫“早期绑定”，当然还有“延迟绑定”，下面我将讨论二者的区别。

1.5 早期绑定与延迟绑定

如果用如下方式创建字典，我们称之为延迟绑定。如果用延迟绑定，则不需要1.4节的添加引用。

Dim dict As Object

Set dict = CreateObject（"Scripting.Dictionary"）

技术层面上，早期绑定意味着我们实现已经决定好要用什么，从而在用之前就绑定好。对于延迟绑定，意味着用什么这件事是在程序运行的时候临时决定的。简单理解二者的区别是：

1） 早期绑定需要引用参考，延迟绑定不需要;

2） 早期绑定允许编码的时候使用*Intellisense，延迟绑定不可以;

3） 早期绑定需要手动添加引用参考Microsoft Scripting Runtime。

（*Intellisense是一个特征，当你键入一个条目的时候，编辑器会自动给出可选的属性、方法）

一般我们推荐用早期绑定的办法。

1.6 如何填充字典

我们可以用Add函数，向字典里加项，在加项的同时，也可以给其赋值。

我们先看Add函数，Add函数有两个参数：Key 和 Item。

dict.Add Key：="Orange"， Item：=45

dict.Add "Apple"， 66

dict.Add "12/12/2018"， "John"

dict.Add 10， 45.56

值得注意的是，Key和Item可以是任何数据类型。例如对象，数组，甚至是字典本身。所以你可以创建包含字典、数组、集合的字典。本文的核心，就是利用把一个对象放到字典里，从而实现字典里存储多个数据。

1.7 判断键是否已经存在

我们可以用Exist函数来判断键在字典里是否已经存在。示例代码如下：

' Checks for the key 'Orange' in the dictionary

If dict.Exists（"Orange"） Then

MsgBox "The number of oranges is " & dict（"Orange"）

Else

MsgBox "There is no entry for Orange in the dictionary."

End If

1.8 如何赋值

我们可以用下面代码给字典赋值：

dict（"Orange"） = 75

如果这个Key在字典里不存在，则会在字典里自动添加这组Key-Item。

' 添加Orange并赋值45

dict（"Orange"） = 45

' 将Orange的值改为100

dict（"Orange"） = 100

2 如何在字典里存储多列数据

有了上面字典的基础知识，我们就可以开始真正的内容——如何在字典里用一个键，控制多个数据。

假如，我们有如下表格的数据：

从表中我们可以看出，每个CustomerID对应两个参数，Amout和Items，而字典只能存一个值，那么我們该如何存这两个数据？

当然，我们可以使用令值为数组或者集合的办法实现。但最好的办法是——用类模块。

见下面实例代码：

' Define Class Module Code

Public CustomerID As String

Public Amount As Long

Public Items As Long

' Create a new clsCustomer object

Set oCust = New clsCustomer

' Set the values

oCust.Customer = rg.Cells（i， 1）.Value

oCust.Amount = rg.Cells（i， 2）.Value

oCust.Items = rg.Cells（i， 3）.Value

' Add the new clsCustomer object to the dictionary

dict.Add oCust.Customer， oCust

通过上述代码，可以看出，我们可以用这种方式存储多个数据。这里，相当于把一个对象当作一个值装进了字典，而对象又有多个属性，每个属性可以单独赋值。通过这种方法，我们即可实现将多个值装入字典，用一个键来控制。

3 处理多个数据工程实例

下面，我们举一个实际工程中的例子，来说明用字典存储多个数据的巧妙用法。

在空调箱产品的风机段排布过程中，有很多数据表作为设计输入，如：风机尺寸表、电机尺寸表、风机底盘尺寸表、以及其他基础数据表格。在进行风机与电机排布计算的时候，需要频繁进行数据查询，数据调用操作。当然，我们可以用数组来做，字典与数组相比，有很多优势。因此，我们使用字典实现。

以电机尺寸表为例：电机尺寸表里，有计算要用到的几个参数——普通电机长度、变频电机长度、IE3电机长度。我们按照如下步骤使用字典：

1） 如第2节所述方法，在VBA Class Modules模块里新增Motor类;

2） 在Motor类里增加MotorID，MotorStandardL，MotorIE3L，MotorBPL等属性;

3） 在VBA Modules模块里，增加CreateDic模块，并添加如下代码;

4） 在CreateDic模块里添加下面代码，代码的主要功能是——定义一个将给定区域里的数据，装入字典的函数。此函数的返回值是一个字典对象。

5） 在风机排布计算的主函数里，调用步骤4中创建的函数，用于建字典、装字典;

6） 在计算过程中查字典;

首先定义查字典相关参数

按照MotorKey的值查询;

注意下面代码中用到了Exist方法来判断数据在字典里是否存在，如果存在，直接就得到查询数据。这个过程对于编程人员无疑是很好用的，因为我们不用去再单独写过程来完成。

2） 在实际计算语句中使用查询结果;

注意这里就体现出了定义类的好处，在编辑代码的时候，可以清楚地知道调用的参数代表什么，并且编辑器会自动给出此类所有可选的属性。

通过上述步骤，即可完成了从建字典、装字典、查字典的整个过程，从而实现了用字典对象处理表格里有多列数据的情况。

4 用字典对象优势分析

4.1 字典与数组对比

字典与数组的区别在于：

1） 数组更适用于实现知道数据量（行列数）的情况，而字典不需要定义边界;

2） 字典对象内部封装了很多实用的方法，如Exist，Remove等，而使用数组需要重新编写这些方法的代码;

3） 数组存储需要定义数据类型，字典里的值可以是数值、数组、对象，甚至字典里可以再装字典。

4） 字典最重要的特点是便于检索，给一个Key，即可得到一个对应的Item。用数组的话需要再用其他函数去检索數据。

5） 字典的缺点是——字典里的键必须有唯一性，不能有重复数据。而数组没有这样的限制。这也决定了字典的使用场合。

4.2 字典与集合对比

字典与集合（Collection）的区别：

1） 字典能用Exist方法查“键”是否存在，集合没有这样的方法;

2） 字典能改键和值，而集合具有只读性，键和值不能改。

5 字典的使用场合

经过上述阐述，以及诸多实例，可以看出，如果我们有这样的数据结构的时候，我们就可以使用字典对象：

1） 数据中的索引ID是一列非重复的数值;

2） 需要通过索引ID检索数据。

6 结束语

本文主要阐述了什么是字典，字典与数组、集合之间比较有什么优势，在什么场合可以使用。更重要的，说明了如何使用字典对象来存储多列数据，并在工程中加以应用。

当然，字典对象还有其他实用的经典用法，如在递归算法中遍历树，数据去重、排序等场合，都大有用处。

参考文献：

[1] Excel Home，Excel VBA 实战技巧精粹[M].人民邮电出版社，2013