在Excel VBA中用字典對(duì)象存儲(chǔ)多列數(shù)據(jù)的方法

張文曉

摘要：本文旨在介紹一種方法，實(shí)現(xiàn)如何用VBA的字典對(duì)象，處理多列數(shù)據(jù)。并且結(jié)合工程實(shí)際，說(shuō)明此方法在實(shí)際編程中如何應(yīng)用。

關(guān)鍵詞：Excel;VBA;字典;多列數(shù)據(jù)

中圖分類號(hào)：TP311? ? ? 文獻(xiàn)標(biāo)識(shí)碼：A? ? ? 文章編號(hào)：1009-3044（2019）02-0214-04

當(dāng)我們基于VBA編程的時(shí)候，往往面對(duì)的源數(shù)據(jù)，是存儲(chǔ)在多個(gè)表單里的。并且每一個(gè)表單一般含有多列數(shù)據(jù)。在運(yùn)算過(guò)程中，要調(diào)用這些源數(shù)據(jù)，就涉及數(shù)據(jù)的查詢、檢索。當(dāng)然，最簡(jiǎn)單的方式就是去遍歷單元格，如果數(shù)據(jù)量不大，這樣的方式也是可以的。但如果數(shù)據(jù)量很大，需要頻繁讀寫操作，往往會(huì)導(dǎo)致程序運(yùn)行效率較低。

這時(shí)經(jīng)常用到的解決方法就是使用數(shù)組保存數(shù)據(jù)，在內(nèi)存中進(jìn)行數(shù)據(jù)處理和加工，最后一次性更新工作表，這樣的操作方式往往會(huì)極大提升程序的運(yùn)行效率。

但是，數(shù)組只能用于存儲(chǔ)數(shù)據(jù)，而我們往往需要對(duì)數(shù)據(jù)進(jìn)行查詢，用于計(jì)算。有時(shí)，我們也需要判斷某個(gè)數(shù)在數(shù)組里是否存在，有時(shí)要對(duì)數(shù)組進(jìn)行擴(kuò)展。同時(shí)，數(shù)組中的各個(gè)數(shù)據(jù)，它們的類型必須是相同的。

Dictionary是隨著Visual Basic 6.0新增的，具有某些強(qiáng)大功能的對(duì)象。字典不是VBA內(nèi)置的類型，它是Windows腳本語(yǔ)言的。但其實(shí)字典在VBA中也是非常重要的，它非常適用于需要進(jìn)行非重復(fù)性數(shù)據(jù)的操作。字典其實(shí)就是一些“鍵-值”對(duì)。在很多場(chǎng)合，它都能起到很大的作用，使用起來(lái)非常方便，有類似于微型數(shù)據(jù)庫(kù)的作用，可用于臨時(shí)保存一些數(shù)據(jù)信息?；谧值浯鎯?chǔ)數(shù)據(jù)的特點(diǎn)，人們往往認(rèn)為字典只能實(shí)現(xiàn)字典里的“值”，只能是單一的一個(gè)數(shù)據(jù)。

本文的目的是介紹如何把多個(gè)數(shù)據(jù)作為字典的“值”裝入字典。

1 了解字典

1.1 什么是字典？

Dictionary 對(duì)象是“Scripting? ?Runtime? ?Library”的一部分，最早VBScript 中實(shí)現(xiàn).（SCRRUN.DLL），它可以將任何形式的數(shù)據(jù)的條目存儲(chǔ)在數(shù)組中。每個(gè)條目都與一個(gè)唯一的關(guān)鍵字相關(guān)聯(lián)。該關(guān)鍵字用來(lái)檢索單個(gè)條目，通常是整數(shù)或字符串，也可以是對(duì)象、集合等任何類型。

字典對(duì)象的關(guān)鍵功能以及代碼：

1.2 VBA中的字典與現(xiàn)實(shí)世界中的字典

為了說(shuō)明什么是字典，可以拿現(xiàn)實(shí)世界中的字典來(lái)舉例。例如，你如果想查一個(gè)詞的意思，你會(huì)直接在字典里查這個(gè)詞，而不會(huì)把整個(gè)字典都通讀一遍。這個(gè)詞，在VBA的字典對(duì)象里，就是Key，而詞的意思，就是Item。

再舉一個(gè)實(shí)際生活中的例子，像手機(jī)里的電話本，人名就是Key（鍵），電話號(hào)碼就是Item（值）。

1.3 使用字典的實(shí)例

下面舉一個(gè)使用字典的實(shí)例，下面代碼按照下面步驟運(yùn)行：

1） 將三個(gè)人名，每個(gè)賦一個(gè)值代表年齡，添加到字典;

2） 彈出對(duì)話框讓用戶輸入一個(gè)人名

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 如何創(chuàng)建字典

通過(guò)上述實(shí)例，我們可以大概了解在程序里怎么使用字典對(duì)象。下面我們來(lái)說(shuō)明如何創(chuàng)建字典。

要使用字典對(duì)象，首先要在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

像這樣創(chuàng)建字典叫“早期綁定”，當(dāng)然還有“延遲綁定”，下面我將討論二者的區(qū)別。

1.5 早期綁定與延遲綁定

如果用如下方式創(chuàng)建字典，我們稱之為延遲綁定。如果用延遲綁定，則不需要1.4節(jié)的添加引用。

Dim dict As Object

Set dict = CreateObject（"Scripting.Dictionary"）

技術(shù)層面上，早期綁定意味著我們實(shí)現(xiàn)已經(jīng)決定好要用什么，從而在用之前就綁定好。對(duì)于延遲綁定，意味著用什么這件事是在程序運(yùn)行的時(shí)候臨時(shí)決定的。簡(jiǎn)單理解二者的區(qū)別是：

1） 早期綁定需要引用參考，延遲綁定不需要;

2） 早期綁定允許編碼的時(shí)候使用*Intellisense，延遲綁定不可以;

3） 早期綁定需要手動(dòng)添加引用參考Microsoft Scripting Runtime。

（*Intellisense是一個(gè)特征，當(dāng)你鍵入一個(gè)條目的時(shí)候，編輯器會(huì)自動(dòng)給出可選的屬性、方法）

一般我們推薦用早期綁定的辦法。

1.6 如何填充字典

我們可以用Add函數(shù)，向字典里加項(xiàng)，在加項(xiàng)的同時(shí)，也可以給其賦值。

我們先看Add函數(shù)，Add函數(shù)有兩個(gè)參數(shù)：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可以是任何數(shù)據(jù)類型。例如對(duì)象，數(shù)組，甚至是字典本身。所以你可以創(chuàng)建包含字典、數(shù)組、集合的字典。本文的核心，就是利用把一個(gè)對(duì)象放到字典里，從而實(shí)現(xiàn)字典里存儲(chǔ)多個(gè)數(shù)據(jù)。

1.7 判斷鍵是否已經(jīng)存在

我們可以用Exist函數(shù)來(lái)判斷鍵在字典里是否已經(jīng)存在。示例代碼如下：

' 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

如果這個(gè)Key在字典里不存在，則會(huì)在字典里自動(dòng)添加這組Key-Item。

' 添加Orange并賦值45

dict（"Orange"） = 45

' 將Orange的值改為100

dict（"Orange"） = 100

2 如何在字典里存儲(chǔ)多列數(shù)據(jù)

有了上面字典的基礎(chǔ)知識(shí)，我們就可以開(kāi)始真正的內(nèi)容——如何在字典里用一個(gè)鍵，控制多個(gè)數(shù)據(jù)。

假如，我們有如下表格的數(shù)據(jù)：

從表中我們可以看出，每個(gè)CustomerID對(duì)應(yīng)兩個(gè)參數(shù)，Amout和Items，而字典只能存一個(gè)值，那么我們?cè)撊绾未孢@兩個(gè)數(shù)據(jù)？

當(dāng)然，我們可以使用令值為數(shù)組或者集合的辦法實(shí)現(xiàn)。但最好的辦法是——用類模塊。

見(jiàn)下面實(shí)例代碼：

' 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

通過(guò)上述代碼，可以看出，我們可以用這種方式存儲(chǔ)多個(gè)數(shù)據(jù)。這里，相當(dāng)于把一個(gè)對(duì)象當(dāng)作一個(gè)值裝進(jìn)了字典，而對(duì)象又有多個(gè)屬性，每個(gè)屬性可以單獨(dú)賦值。通過(guò)這種方法，我們即可實(shí)現(xiàn)將多個(gè)值裝入字典，用一個(gè)鍵來(lái)控制。

3 處理多個(gè)數(shù)據(jù)工程實(shí)例

下面，我們舉一個(gè)實(shí)際工程中的例子，來(lái)說(shuō)明用字典存儲(chǔ)多個(gè)數(shù)據(jù)的巧妙用法。

在空調(diào)箱產(chǎn)品的風(fēng)機(jī)段排布過(guò)程中，有很多數(shù)據(jù)表作為設(shè)計(jì)輸入，如：風(fēng)機(jī)尺寸表、電機(jī)尺寸表、風(fēng)機(jī)底盤尺寸表、以及其他基礎(chǔ)數(shù)據(jù)表格。在進(jìn)行風(fēng)機(jī)與電機(jī)排布計(jì)算的時(shí)候，需要頻繁進(jìn)行數(shù)據(jù)查詢，數(shù)據(jù)調(diào)用操作。當(dāng)然，我們可以用數(shù)組來(lái)做，字典與數(shù)組相比，有很多優(yōu)勢(shì)。因此，我們使用字典實(shí)現(xiàn)。

以電機(jī)尺寸表為例：電機(jī)尺寸表里，有計(jì)算要用到的幾個(gè)參數(shù)——普通電機(jī)長(zhǎng)度、變頻電機(jī)長(zhǎng)度、IE3電機(jī)長(zhǎng)度。我們按照如下步驟使用字典：

1） 如第2節(jié)所述方法，在VBA Class Modules模塊里新增Motor類;

2） 在Motor類里增加MotorID，MotorStandardL，MotorIE3L，MotorBPL等屬性;

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

4） 在CreateDic模塊里添加下面代碼，代碼的主要功能是——定義一個(gè)將給定區(qū)域里的數(shù)據(jù)，裝入字典的函數(shù)。此函數(shù)的返回值是一個(gè)字典對(duì)象。

5） 在風(fēng)機(jī)排布計(jì)算的主函數(shù)里，調(diào)用步驟4中創(chuàng)建的函數(shù)，用于建字典、裝字典;

6） 在計(jì)算過(guò)程中查字典;

首先定義查字典相關(guān)參數(shù)

按照MotorKey的值查詢;

注意下面代碼中用到了Exist方法來(lái)判斷數(shù)據(jù)在字典里是否存在，如果存在，直接就得到查詢數(shù)據(jù)。這個(gè)過(guò)程對(duì)于編程人員無(wú)疑是很好用的，因?yàn)槲覀儾挥萌ピ賳为?dú)寫過(guò)程來(lái)完成。

2） 在實(shí)際計(jì)算語(yǔ)句中使用查詢結(jié)果;

注意這里就體現(xiàn)出了定義類的好處，在編輯代碼的時(shí)候，可以清楚地知道調(diào)用的參數(shù)代表什么，并且編輯器會(huì)自動(dòng)給出此類所有可選的屬性。

通過(guò)上述步驟，即可完成了從建字典、裝字典、查字典的整個(gè)過(guò)程，從而實(shí)現(xiàn)了用字典對(duì)象處理表格里有多列數(shù)據(jù)的情況。

4 用字典對(duì)象優(yōu)勢(shì)分析

4.1 字典與數(shù)組對(duì)比

字典與數(shù)組的區(qū)別在于：

1） 數(shù)組更適用于實(shí)現(xiàn)知道數(shù)據(jù)量（行列數(shù)）的情況，而字典不需要定義邊界;

2） 字典對(duì)象內(nèi)部封裝了很多實(shí)用的方法，如Exist，Remove等，而使用數(shù)組需要重新編寫這些方法的代碼;

3） 數(shù)組存儲(chǔ)需要定義數(shù)據(jù)類型，字典里的值可以是數(shù)值、數(shù)組、對(duì)象，甚至字典里可以再裝字典。

4） 字典最重要的特點(diǎn)是便于檢索，給一個(gè)Key，即可得到一個(gè)對(duì)應(yīng)的Item。用數(shù)組的話需要再用其他函數(shù)去檢索數(shù)據(jù)。

5） 字典的缺點(diǎn)是——字典里的鍵必須有唯一性，不能有重復(fù)數(shù)據(jù)。而數(shù)組沒(méi)有這樣的限制。這也決定了字典的使用場(chǎng)合。

4.2 字典與集合對(duì)比

字典與集合（Collection）的區(qū)別：

1） 字典能用Exist方法查“鍵”是否存在，集合沒(méi)有這樣的方法;

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

5 字典的使用場(chǎng)合

經(jīng)過(guò)上述闡述，以及諸多實(shí)例，可以看出，如果我們有這樣的數(shù)據(jù)結(jié)構(gòu)的時(shí)候，我們就可以使用字典對(duì)象：

1） 數(shù)據(jù)中的索引ID是一列非重復(fù)的數(shù)值;

2） 需要通過(guò)索引ID檢索數(shù)據(jù)。

6 結(jié)束語(yǔ)

本文主要闡述了什么是字典，字典與數(shù)組、集合之間比較有什么優(yōu)勢(shì)，在什么場(chǎng)合可以使用。更重要的，說(shuō)明了如何使用字典對(duì)象來(lái)存儲(chǔ)多列數(shù)據(jù)，并在工程中加以應(yīng)用。

當(dāng)然，字典對(duì)象還有其他實(shí)用的經(jīng)典用法，如在遞歸算法中遍歷樹(shù)，數(shù)據(jù)去重、排序等場(chǎng)合，都大有用處。

參考文獻(xiàn)：

[1] Excel Home，Excel VBA 實(shí)戰(zhàn)技巧精粹[M].人民郵電出版社，2013