VBA: 字典(Dictionary)的基本概念

文章背景： 在VBA中，有这么一个对象：字典（Dictionary），它像我们用过的纸质字典一样，用键值对（key:item）来表示。键可以理解为新华字典检字表中的关键字，而值可以理解为对关键字的解释。字典在数据的去重上很有用。

在VBA字典中，有4个属性和6种方法，相比其它的对象要简洁得多，而且容易理解。

1 字典的属性1.1 Count2.2 Key2.3 Item2.4 CompareMode2 字典的方法2.1 Add 2.2 Keys2.3 Items2.4 Exists2.5 Remove2.6 RemoveAll

1 字典的属性

1.1 Count

返回一个Dictionary 对象中的条目数。只读属性。

代码示例：

Option Explicit
Option Base 1

Sub dic_test()

    Dim aa, d, ii As Integer             'Create some variables

    Set d = CreateObject("Scripting.Dictionary")
    
    d.Add "a", "Athens"     'Add some keys and items.
    d.Add "b", "Belgrade"
    d.Add "c", "Cairo"
    
    aa = d.Keys              'Get the keys
    
    For ii = 0 To d.Count - 1   'Iterate the array
    
        Debug.Print aa(ii)          'Print key
        
    Next

End Sub

值得一提的是，虽然在模块开头设置了Option Base 1，但是对aa并不起作用，下标是依然从0开始。

2.2 Key

Sets a key in a Dictionary object.

在Dictionary对象中设置一个key。

代码示例：

Sub dic_key()

    Dim dic, aa
    Set dic = CreateObject("Scripting.Dictionary")
    
    dic.Add "1", "苹果"
    dic.Add "2", "香蕉"
    dic.Add "3", "雪"
    
    dic.Key("1") = "橘子"
    
    aa = dic.Keys   'Get the keys
    
    MsgBox aa(0)    '橘子

End Sub

Notice: If the key is not found when changing a key, a run-time error will occur.

2.3 Item

Sets or returns an item for a specified key in a Dictionary object. For collections, returns an item based on the specified key. Read/write.

在一个Dictionary对象中设置或者返回所指定key的item。对于集合，则根据所指定的key返回一个item。读/写。

代码示例：

Sub dic_item()

    Dim dic, aa
    Set dic = CreateObject("Scripting.Dictionary")
    
    dic.Add "1", "苹果"
    dic.Add "2", "香蕉"
    dic.Add "3", "雪梨"
    
    MsgBox dic.Item("3")    '返回Item，雪梨
    
    dic.Item("4") = "橘子"
    MsgBox dic.Item("4")    '返回Item，橘子
    
    MsgBox dic.Item("5")    '返回空
    
    aa = dic.Keys   'Get the keys
    MsgBox aa(4)    '返回关键字，5

End Sub

Notice: If the key is not found when changing an item, a new key is created with the specified new item. If the key is not found when attempting to return an existing item, a new key is created and its corresponding item is left empty.

2.4 CompareMode

Sets and returns the comparison mode for comparing string keys in a Dictionary object.

设置或者返回在Dictionary对象中进行字符串关键字比较时所使用的比较模式。

object.CompareMode [ = compare ]

• compare: 代表比较模式的值。常用的值有0和1。0代表二进制比较，区分大小写；1代表文本比较，不区分大小写。默认是二进制比较，区分大小写，即关键字B和b是不一样的。 代码示例： Sub dic_compare() Dim dic Set dic = CreateObject("Scripting.Dictionary") dic.CompareMode = vbTextCompare '文本比较，不区分大小写 dic.Add "a", "苹果" dic.Add "b", "香蕉" dic.Add "c", "雪梨" dic.Add "B", "火龙果" '程序报错，提示key已存在 End Sub

上述代码中，由于设置的比较模式为文本模式，不区分关键字的大小写，即关键字b和B是一样的，因此程序会报错。

Notice: An error occurs if you try to change the comparison mode of a Dictionary object that already contains data.

如果试图改变一个已经包含有数据的Dictionary对象的比较模式，那么将导致一个错误。

2 字典的方法

2.1 Add

Adds a key and item pair to a Dictionary object.

向Dictionary对象中添加一个关键字项目对。

object.Add key, item

An error occurs if the key already exists.

2.2 Keys

Returns an array containing all existing keys in a Dictionary object.

返回一个数组，其中包含了一个 Dictionary 对象中的全部现有的关键字。

object.Keys

2.3 Items

Returns an array containing all the items in a Dictionary object.

返回一个数组，其中包含了一个 Dictionary 对象中的所有项目。

object.Items

2.4 Exists

Returns True if a specified key exists in the Dictionary object; False if it does not.

如果Dictionary对象中存在所指定的关键字则返回true，否则返回false。

Sub dic_exist()

    Dim dic
    Set dic = CreateObject("Scripting.Dictionary")
    
    dic.Add "a", "苹果"
    dic.Add "b", "香蕉"
    dic.Add "c", "雪梨"
    
    If dic.Exists("c") Then
    
       MsgBox "指定的关键字已经存在。"
       
    Else
    
       MsgBox "指定的关键字不存在。"
       
    End If

End Sub

2.5 Remove

Removes a key/item pair from a Dictionary object.

Remove 方法从一个 Dictionary 对象中清除一个关键字，项目对。

object.Remove (key)

An error occurs if the specified key/item pair does not exist.

代码示例：

Option Explicit

Sub Start()

    Dim d As Object
    Set d = CreateObject("Scripting.Dictionary")
    
    d.Add "a", "Athens"
    d.Add "b", "Belgrade"
    d.Add "c", "Cairo"
    
    Debug.Print "Keys, before using Remove."
    PrintKeys d
    
    d.Remove "b"
    
    Debug.Print "Keys, after removing key 'b'."
    PrintKeys d

End Sub

Private Sub PrintKeys(ByVal d As Object)
    
    Dim k As Variant
    For Each k In d.Keys
        Debug.Print k
    Next k
    
End Sub

运行结果:

Keys, before using Remove.
a
b
c
Keys, after removing key 'b'.
a
c

2.6 RemoveAll

The RemoveAll method removes all key, item pairs from a Dictionary object.

RemoveAll 方法从一个 Dictionary 对象中清除所有的关键字，项目对。

object.RemoveAll

代码示例：

Sub dic_RemoveAll()

    Dim d, i             'Create some variables
    
    Set d = CreateObject("Scripting.Dictionary")
    d.Add "a", "Athens"     'Add some keys and items
    d.Add "b", "Belgrade"
    d.Add "c", "Cairo"

    d.RemoveAll         'Clear the dictionary
    
    d.Add "a", "苹果"
    
    MsgBox d.Count      '结果是1
    
End Sub

参考资料：

[1] VBA字典(dictionary)(https://blog.csdn.net/Gordennizaicunzai/article/details/73730221)

[2] 数据处理VBA篇:字典基础概念 - 简书 (jianshu.com)(https://www.jianshu.com/p/d36a0b907883)

[3] Dictionary object(https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dictionary-object)