证明Excel VBA Scripting.Dictionary不会保留项目插入的顺序 [英] Prove that Excel VBA Scripting.Dictionary does not preserve order of item insertion

问题描述

我正在尝试决定是否对我的项目使用Excel VBA Collection或Dictionary.出于多种原因，我倾向于使用字典，但是我一直在阅读，当使用For.. Each循环检索字典项或从dictionary Items()数组中读取项时，检索顺序可能不是项被排序的顺序.添加.对于我的应用程序来说，这将是一个严重的问题，因此我尝试了许多测试用例，试图证明此顺序不匹配确实发生了.到目前为止，我还无法创建这种情况，包括以随机顺序添加和删除项目.

任何人都可以提供一个示例来证明检索顺序与Excel VBA Scripting.Dictionary的插入顺序不匹配吗?

解决方案

没有人可以提供

您可能是指添加密钥的速记覆盖"方法，而没有检查它们是否存在.如果您尝试.添加存在错误的键将被抛出457.但是，如果您只写键和

覆盖方法比检查键是否更快. ，但应了解其行为.我已经使用覆盖来有意地检索具有重复键的列表中任何键的最后一项，但这是一个特例.请注意， 2 .Key 不是原始顺序不对；它只是将其 .Item 替换为后面的版本.

如果添加了Microsoft脚本运行时参考库，则可以使用早期绑定在您的变量声明中.这样的好处是创建了一个更快的字典对象，并公开了其他索引枚举的方法.

Dim dict As New Scripting.Dictionary

'enumerate by index position (zero-based index)
Dim i As Long
For i = 0 To dict.Count - 1
    Debug.Print dict.Keys(i) & " - " & dict.Items(i)
Next i

'enumerate as elements of an array
Dim a As Long
For a = LBound(dict.Keys) To UBound(dict.Keys)
    Debug.Print dict.Keys(a) & " - " & dict.Items(a)
Next a

首先，我发现当字典的 .Count <时，此索引枚举不可靠. /a>超过一个有符号整数(32,767).

最后，密钥/项对可以一次删除，也可以一次全部删除.

dict.Remove 2                              'remove by key

dict.Remove dict.Keys(0)                   'remove first key/item by index position
dict.Remove dict.Keys(UBound(dict.Keys))   'remove last key/item by index position

dict.RemoveAll                             'remove all

I'm trying to decide whether to use Excel VBA Collection or Dictionary for my project. For several reasons, I lean toward dictionary, but I keep reading that when using a For.. Each loop to retrieve dictionary items or when reading items from the dictionary Items() array, the order of retrieval may not be the order in which items were added. This would be a serious problem for my application, so I have tried many test cases trying to prove this order mismatch actually occurs. So far, I have not been able to create a scenario where this happens, including adding and removing items in random order.

Can anyone provide an example that proves the retrieval order does not match the order of insertion for the Excel VBA Scripting.Dictionary?

解决方案

No one can provide an example of a Scripting.Dictionary object returning keys/items in a different order than they were added because a Scripting.Dictionary does not do that. Never. If you add the following key/item pairs in a progressive order then that is the ways they are returned.

key  item
 1    A
 2    B
 3    C
 4    D

'enumerate by key
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

dict.Add Key:=1, Item:="A"
dict.Add Key:=2, Item:="B"
dict.Add Key:=3, Item:="C"
dict.Add Key:=4, Item:="D"

Dim k As Variant
For Each k In dict.Keys
    Debug.Print k & " - " & dict.Item(k)
Next k

You might be referring to the shorthand 'overwrite' method of adding keys without checking to see it they exist. If you try to .Add a key that exists error 457 is thrown. However, if you simply write Keys and Items into the dictionary a new key and item will be created if the key does not exist but if the key exists the key remains the same and the item is overwritten.

key  item
 1    A
 2    B
 3    C
 4    D
 2    E

'enumerate by key
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

dict.Item(1) = "A"
dict.Item(2) = "B"
dict.Item(3) = "C"
dict.Item(4) = "D"
dict.Item(2) = "E"     'the second entry's item is overwritten

Dim k As Variant
For Each k In dict.Keys
    Debug.Print k & " - " & dict.Item(k)
Next k

The overwrite method is a little faster than checking if a key .Exists but the behavior should be understood. I've used the overwrite to intentionally retrieve the last item for any key in a list with duplicate keys but that was a special case. Note that the 2 .Key is not out of original order; it has merely had its .Item replaced with the later one.

If you have added the Microsoft Scripting Runtime reference library you can use Early Binding in your variable declaration. This has the benefit of creating a slightly faster dictionary object and exposing other methods of indexed enumeration.

Dim dict As New Scripting.Dictionary

'enumerate by index position (zero-based index)
Dim i As Long
For i = 0 To dict.Count - 1
    Debug.Print dict.Keys(i) & " - " & dict.Items(i)
Next i

'enumerate as elements of an array
Dim a As Long
For a = LBound(dict.Keys) To UBound(dict.Keys)
    Debug.Print dict.Keys(a) & " - " & dict.Items(a)
Next a

fwiw, I've found this indexed enumeration unreliable when the dictionary's .Count exceeds a signed integer (32,767).

Finally, key/item pairs can be removed one at a time or all at once.

dict.Remove 2                              'remove by key

dict.Remove dict.Keys(0)                   'remove first key/item by index position
dict.Remove dict.Keys(UBound(dict.Keys))   'remove last key/item by index position

dict.RemoveAll                             'remove all

这篇关于证明Excel VBA Scripting.Dictionary不会保留项目插入的顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！