VBA字典参考 [英] VBA Dictionary References

问题描述

我不是专业程序员，但是相当精通VBA.我目前正在编写一个程序来分析大型数据库.当我在数据库中找到需要进一步分析和潜在更正的区域时，我想要的方法是在许多具有相同结构的词典中收集必要的数据.

虽然我可以使用早期绑定声明字典，但是我需要一种方法来引用每个具有可变名称的字典，然后可以使用该名称创建循环来快速分析我收集的所有信息.理想情况下，我想为字典命名，例如A1，A2，A3等，然后创建一个循环，在其中以递增的方式递增名称的数字部分，因为每个字典中的所有数据都以相同的方式进行分析/校正

尽管我知道间接引用不能在标准VBA中使用，但可以为每个字典使用对象变量.但是，对象变量本身似乎并不具有具有可变结构的灵活性.对于某些对象(如WorkSheets)，这似乎是可能的，其中可以在WorkSheets后面的括号中定义变量名(即WorkSheets("A1"))，但对于字典，集合，数组列表或任何其他类型的存储机制，则似乎不可行可能会用.

我可以通过创建嵌套字典和一些拼凑的键名称来完成我想做的事情.我有一些数据，对于相同的键值，需要使用不同的字典项.因此，我需要为同一键创建不同的版本".不用说，尽管这种方法行得通，但是如果我可以遍历这些名称，则分析将更加简单.

希望我已经充分描述了我的问题.我对自己要做的事情完全不走运，还是有人对实现这一目标有想法?非常感谢！

解决方案

在我最终弄清 VBA字典(和收藏集).我对您的问题尚不完全清楚，因此这可能无法直接解决您的问题，但我一直在寻找借口来传递我在两个主题中发现(并且经常提到)的出色资源:

• Paul Kelley:> Excel VBA词典–完整指南

• Paul Kelley:> Excel VBA中的收藏终极指南 >

---

仅此而已，我没有发布仅链接的答案，摘录如下:

VBA词典常见任务备忘单:

早期绑定

图书馆/参考文献(早期绑定)
Microsoft Scripting Runtime
(使用VBE中的Tools-> References添加)

声明(早期绑定)
Dim dict As Scripting.Dictionary

创建(早期绑定)
Set dict = New Scripting.Dictionary

---

后期绑定

声明(后期绑定)
Dim dict As Object

创建(后期绑定)
Set dict = CreateObject("Scripting.Dictionary")

---

添加/更改/获取

添加项目(密钥必须不存在)
dict.Add Key, Value
^_例如 dict.Add "Apples", 50

更改键的值.如果密钥不存在，则自动添加.
dict(Key) = Value
^_例如 dict("Oranges") = 60

检查密钥是否存在
dict.Exists(Key)
^_例如 If dict.Exists("Apples") Then

获取项目数
dict.Count

使用键从字典中获取一个值
Value = dict(Key)
^_例如 appleCount = dict("Apples")

---

删除

删除项目
dict.Remove Key
^_例如 dict.Remove "Apples"

删除所有项目
dict.RemoveAll

---

枚举

遍历所有项目(For..Each循环)
Dim key As Variant
For Each key In dict.Keys
Debug.Print key, dict(key)
Next key

遍历所有项目(For..Next循环-仅早期绑定)
Dim i As Long
For i = 0 To dict.Count - 1
Debug.Print dict.Keys(i),
dict.Items(i)
Next i

---

区分大小写

区分大小写(字典必须为空).
dict.CompareMode = vbBinaryCompare

使密钥不区分大小写(词典必须为空).
dict.CompareMode = vbTextCompare

_{(有关详细信息，请参见 来源 : Paul Kelly Excel精通宏)}

I am not a professional programmer, but reasonably proficient in VBA. I currently am writing a program to analyze a large database. When I find the area in the database that needs further analysis and potential correction, my desired approach is to collect the necessary data in a number of dictionaries with a common structure.

While I can declare the dictionaries with early binding, I need a way to reference each dictionary with a variable name I can then use to create a loop to quickly analyze all the information I collected. Ideally, I would want to name the dictionaries something like A1, A2, A3, etc. and then just create a loop where I increment through the numerical part of the name as all the data in each dictionary is analyzed/corrected in the same manner.

While I know indirect references cannot be used in standard VBA, it is possible to use Object Variables for each of the dictionaries. However, Object Variables themselves to not appear to have the flexibility to have a variable structure. This appears possible with some objects like WorkSheets, where the variable name can be defined in parentheses behind WorkSheets (i.e. WorkSheets("A1")), but does not appear possible for dictionaries, collections, arraylists, or any other type of storage mechanism I might use.

I can do what I want to do by creating nested dictionaries and some cobbled up names for the keys. I have some data which would require a different dictionary item for the same key value. Thus I need to create different "versions" of the same key. Needless to say, while this approach can work, the analysis would be much simpler if I could just loop through the names.

Hopefully, I described my problem adequately. Am I totally out of luck in what I am trying to do or does someone have an idea on how to achieve this? Thanks much!

解决方案

I, too, had plenty of years experience with VBA before I finally figured out VBA Dictionaries (and Collections). I'm not fully clear on your question, and therefore this may not directly solve your problem, but I've been looking for an excuse to pass along an excellent resource I discovered (and I frequently refer to) on both topics:

• Paul Kelley : Excel VBA Dictionary – A Complete Guide

• Paul Kelley : The Ultimate Guide To Collections in Excel VBA

---

Just so I'm not posting a link-only answer, here's an excerpt:

VBA Dictionaries common tasks cheat sheet:

Early Binding

Library/Reference (early binding)
Microsoft Scripting Runtime
   (Add using Tools->References from the VBE)

Declare (early binding)
Dim dict As Scripting.Dictionary

Create (early binding)
Set dict = New Scripting.Dictionary

---

Late Binding

Declare (late binding)
Dim dict As Object

Create(late binding)
Set dict = CreateObject("Scripting.Dictionary")

---

Add/Change/Get

Add item (key must not already exist)
dict.Add Key, Value
_^e.g. dict.Add "Apples", 50

Change value at key. Automatically adds if the key does not exist.
dict(Key) = Value
_^e.g. dict("Oranges") = 60

Check if key exists
dict.Exists(Key)
_^e.g. If dict.Exists("Apples") Then

Get the number of items
dict.Count

Get a value from the dictionary using the key
Value = dict(Key)
_^e.g. appleCount = dict("Apples")

---

Remove

Remove item
dict.Remove Key
_^e.g. dict.Remove "Apples"

Remove all items
dict.RemoveAll

---

Enumerate

Loop through all items (For..Each loop)
Dim key As Variant
For Each key In dict.Keys
Debug.Print key, dict(key)
Next key

Loop through all items (For..Next loop - early binding only)
Dim i As Long
For i = 0 To dict.Count - 1
Debug.Print dict.Keys(i),
dict.Items(i)
Next i

---

Case Sensitivity

Make key case sensitive (the dictionary must be empty).
dict.CompareMode = vbBinaryCompare

Make key non case sensitive (the dictionary must be empty).
dict.CompareMode = vbTextCompare

_{(More info at the source: Paul Kelly Excel Macro Mastery)}

这篇关于VBA字典参考的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！