标题 | 在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 |
随便看 |
|
科学优质学术资源、百科知识分享平台,免费提供知识科普、生活经验分享、中外学术论文、各类范文、学术文献、教学资料、学术期刊、会议、报纸、杂志、工具书等各类资源检索、在线阅读和软件app下载服务。