在 Excel VBA 中使用字典对象
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6834141/
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
Using Dictionary Object in Excel VBA
提问by Sky Cobb
I am struggling to make my Dictionary object work to return economic details of selected Reference number.
我正在努力使我的 Dictionary 对象能够返回所选参考编号的经济详细信息。
e.g. I have below reference nos and corresponding values, however, not sure if I can achieve this using Dictionary object , and alternative, suggestion would be highly appreciated.
例如，我有以下参考编号和相应的值，但是，不确定我是否可以使用 Dictionary 对象来实现这一点，并且非常感谢建议。
Ref No Amount Price Year
IB1232 1000 1.23 2011
IB1231 1000 3.23 2011
IB1233 1000 3.43 2011
IB1234 1000 3.43 2011
I thought would be able to achieve by forming Key and Value for reference and their corresponding details, but not been able to achieve ..
我以为可以通过形成Key和Value以供参考及其对应的细节来实现，但未能实现..
回答by Adam Clason
@das_weezul
@das_weezul
There is a Dictionary object in VBA in the scripting library (you need to add that reference to use it). The Dictionary has some extra functionality, such as the ability to check if a key exists before trying to access it.
脚本库中的 VBA 中有一个 Dictionary 对象（您需要添加该引用才能使用它）。Dictionary 有一些额外的功能，例如在尝试访问之前检查密钥是否存在的能力。
@Sky Cobb
@天空科布
Yes, you could do all of the above tasks with the Dictionary. The syntax will the same, except you should provide a key for every item that you add to it.
是的，您可以使用字典完成上述所有任务。语法将相同，但您应该为添加到其中的每个项目提供一个键。
回答by das_weezul
I don't know what you're referring to as Dictionary in VBA, as the data structure with the said functionality is called Collectionin VBA (but maybe you coded your own Ditionary, in that case we need the code in order to be able to help you).
我不知道你在 VBA 中指的是什么字典，因为具有上述功能的数据结构Collection在 VBA 中被调用（但也许你编写了自己的字典，在这种情况下我们需要代码以便能够帮你）。
If I get your example right, you want to access e.g {1000,1.23,2011} via the key "IB1232". You can do this easily by creating a Collection of Collections like this:
如果我理解你的例子，你想通过键“IB1232”访问例如{1000,1.23,2011}。您可以通过创建一个像这样的集合集合来轻松地做到这一点：
Dim coll as new Collection
Dim data as new Collection
data.Add 1000
data.Add 1.23
data.Add 2011
coll.Add data, "IB1232"
To access your data just get the desired record (Collection) via the key
要访问您的数据，只需通过密钥获取所需的记录（集合）
Debug.Print coll.Item("IB1232")(1) 'Prints 1000
Debug.Print coll.Item("IB1232")(2) 'Prints 1.23
Debug.Print coll.Item("IB1232")(3) 'Prints 2010
You can also use an array of Variants for the data
您还可以对数据使用 Variants 数组
回答by Kamel
As mentioned before, you need to enable a reference to get the Dictionary object, but it absolutely does exist. To add the reference: Tools > References > [x] Microsoft Scripting Runtime
如前所述，您需要启用引用才能获取 Dictionary 对象，但它绝对存在。添加引用：Tools > References > [x] Microsoft Scripting Runtime
Public Sub test_dict()
Dim td As Object
Set td = New Dictionary
td("IB1232") = "1000 1.23 2011"
td("IB1233") = "1000 3.43 2011"
'Another way to do it, may be around for legacy support
td.Item("IB1234") = "1000 3.43 2011"
'What you probably want... a key:value dictionary where the value is a collection
Set td("IB1231") = New Collection
td("IB1231").add 1000
td("IB1231").add 3.23
td("IB1231").add 2011
'Get value by key
Debug.Print td("IB1234")
'Get a collection's value.... it's 1-indexed
Debug.Print td("IB1231")(1)
'Test if a key exists
Debug.Print td.exists("IB12345")
'See how many items there are
Debug.Print td.Count()
End Sub
