VBA 有字典结构吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/915317/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Does VBA have Dictionary Structure?
提问by Mitch Wheat
Does VBA have dictionary structure? Like key<>value array?
VBA 有字典结构吗？像键<>值数组？
回答by Mitch Wheat
Yes.
是的。
Set a reference to MS Scripting runtime ('Microsoft Scripting Runtime'). As per @regjo's comment, go to Tools->References and tick the box for 'Microsoft Scripting Runtime'.
设置对 MS 脚本运行时（“Microsoft 脚本运行时”）的引用。根据@regjo 的评论，转到“工具”->“参考”并勾选“Microsoft Scripting Runtime”框。
Create a dictionary instance using the code below:
使用以下代码创建字典实例：
Set dict = CreateObject("Scripting.Dictionary")
or
或者
Dim dict As New Scripting.Dictionary
Example of use:
使用示例：
If Not dict.Exists(key) Then
dict.Add key, value
End If
Don't forget to set the dictionary to Nothingwhen you have finished using it.
不要忘记Nothing在使用完字典时将其设置为。
Set dict = Nothing
回答by Caleb Hattingh
VBA has the collection object:
VBA 有集合对象：
Dim c As Collection
Set c = New Collection
c.Add "Data1", "Key1"
c.Add "Data2", "Key2"
c.Add "Data3", "Key3"
'Insert data via key into cell A1
Range("A1").Value = c.Item("Key2")
The Collectionobject performs key-based lookups using a hash so it's quick.
该Collection对象使用散列执行基于键的查找，因此速度很快。
You can use a Contains()function to check whether a particular collection contains a key:
您可以使用Contains()函数来检查特定集合是否包含键：
Public Function Contains(col As Collection, key As Variant) As Boolean
On Error Resume Next
col(key) ' Just try it. If it fails, Err.Number will be nonzero.
Contains = (Err.Number = 0)
Err.Clear
End Function
Edit 24 June 2015: Shorter Contains()thanks to @TWiStErRob.
2015 年 6 月 24 日编辑：Contains()感谢@TWiStErRob，时间更短。
Edit 25 September 2015: Added Err.Clear()thanks to @scipilot.
2015 年 9 月 25 日编辑：Err.Clear()感谢@scipilot。
回答by Jarmo
VBA does not have an internal implementation of a dictionary, but from VBA you can still use the dictionary object from MS Scripting Runtime Library.
VBA 没有字典的内部实现，但在 VBA 中，您仍然可以使用 MS Scripting Runtime Library 中的字典对象。
Dim d
Set d = CreateObject("Scripting.Dictionary")
d.Add "a", "aaa"
d.Add "b", "bbb"
d.Add "c", "ccc"
If d.Exists("c") Then
MsgBox d("c")
End If
回答by John M
An additional dictionary example that is useful for containing frequency of occurence.
一个额外的字典示例，可用于包含出现频率。
Outside of loop:
循环外：
Dim dict As New Scripting.dictionary
Dim MyVar as String
Within a loop:
在一个循环内：
'dictionary
If dict.Exists(MyVar) Then
dict.Item(MyVar) = dict.Item(MyVar) + 1 'increment
Else
dict.Item(MyVar) = 1 'set as 1st occurence
End If
To check on frequency:
检查频率：
Dim i As Integer
For i = 0 To dict.Count - 1 ' lower index 0 (instead of 1)
Debug.Print dict.Items(i) & " " & dict.Keys(i)
Next i
回答by Evan Kennedy
Building off cjrh's answer, we can build a Contains function requiring no labels (I don't like using labels).
建立关cjrh的回答中，我们可以构建一个包含不需要标签功能（我不喜欢使用标签）。
Public Function Contains(Col As Collection, Key As String) As Boolean
Contains = True
On Error Resume Next
err.Clear
Col (Key)
If err.Number <> 0 Then
Contains = False
err.Clear
End If
On Error GoTo 0
End Function
For a project of mine, I wrote a set of helper functions to make a Collectionbehave more like a Dictionary. It still allows recursive collections. You'll notice Key always comes first because it was mandatory and made more sense in my implementation. I also used only Stringkeys. You can change it back if you like.
对于我的一个项目，我编写了一组辅助函数来使Collection行为更像Dictionary. 它仍然允许递归集合。您会注意到 Key 总是排在第一位，因为它是强制性的，并且在我的实现中更有意义。我也只用过String钥匙。如果你愿意，你可以把它改回来。
Set
放
I renamed this to set because it will overwrite old values.
我将其重命名为 set ，因为它会覆盖旧值。
Private Sub cSet(ByRef Col As Collection, Key As String, Item As Variant)
If (cHas(Col, Key)) Then Col.Remove Key
Col.Add Array(Key, Item), Key
End Sub
Get
得到
The errstuff is for objects since you would pass objects using setand variables without. I think you can just check if it's an object, but I was pressed for time.
这些err东西是用于对象的，因为您将使用对象传递对象set，而无需传递变量。我想你可以检查它是否是一个对象，但我时间紧迫。
Private Function cGet(ByRef Col As Collection, Key As String) As Variant
If Not cHas(Col, Key) Then Exit Function
On Error Resume Next
err.Clear
Set cGet = Col(Key)(1)
If err.Number = 13 Then
err.Clear
cGet = Col(Key)(1)
End If
On Error GoTo 0
If err.Number <> 0 Then Call err.raise(err.Number, err.Source, err.Description, err.HelpFile, err.HelpContext)
End Function
Has
已
The reason for this post...
这个帖子的原因...
Public Function cHas(Col As Collection, Key As String) As Boolean
cHas = True
On Error Resume Next
err.Clear
Col (Key)
If err.Number <> 0 Then
cHas = False
err.Clear
End If
On Error GoTo 0
End Function
Remove
消除
Doesn't throw if it doesn't exist. Just makes sure it's removed.
如果不存在则不抛出。只要确保它被删除。
Private Sub cRemove(ByRef Col As Collection, Key As String)
If cHas(Col, Key) Then Col.Remove Key
End Sub
Keys
钥匙
Get an array of keys.
获取一组键。
Private Function cKeys(ByRef Col As Collection) As String()
Dim Initialized As Boolean
Dim Keys() As String
For Each Item In Col
If Not Initialized Then
ReDim Preserve Keys(0)
Keys(UBound(Keys)) = Item(0)
Initialized = True
Else
ReDim Preserve Keys(UBound(Keys) + 1)
Keys(UBound(Keys)) = Item(0)
End If
Next Item
cKeys = Keys
End Function
回答by Kalidas
The scripting runtime dictionary seems to have a bug that can ruin your design at advanced stages.
脚本运行时字典似乎有一个错误，可能会在高级阶段破坏您的设计。
If the dictionary value is an array, you cannot update values of elements contained in the array through a reference to the dictionary.
如果字典值是数组，则不能通过对字典的引用来更新数组中包含的元素的值。
回答by user2604899
If by any reason, you can't install additional features to your Excel or don't want to, you can use arrays as well, at least for simple problems. As WhatIsCapital you put name of the country and the function returns you its capital.
如果出于任何原因，您无法为 Excel 安装附加功能或不想安装其他功能，您也可以使用数组，至少对于简单的问题是这样。作为 WhatIsCapital，您输入国家/地区名称，该函数将返回其首都。
Sub arrays()
Dim WhatIsCapital As String, Country As Array, Capital As Array, Answer As String
WhatIsCapital = "Sweden"
Country = Array("UK", "Sweden", "Germany", "France")
Capital = Array("London", "Stockholm", "Berlin", "Paris")
For i = 0 To 10
If WhatIsCapital = Country(i) Then Answer = Capital(i)
Next i
Debug.Print Answer
End Sub
回答by Michiel van der Blonk
All the others have already mentioned the use of the scripting.runtime version of the Dictionary class. If you are unable to use this DLL you can also use this version, simply add it to your code.
所有其他人都已经提到了使用 Dictionary 类的 scripting.runtime 版本。如果您无法使用此 DLL，您也可以使用此版本，只需将其添加到您的代码中即可。
https://github.com/VBA-tools/VBA-Dictionary/blob/master/Dictionary.cls
https://github.com/VBA-tools/VBA-Dictionary/blob/master/Dictionary.cls
It is identical to Microsoft's version.
它与 Microsoft 的版本相同。
