vba：我如何搜索集合？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3587120/
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
vba: how do i search through a collection?
提问by l--''''''---------''''''''''''
i am looking for a particular element in a collection. how do i know if it exists in the collection?
我正在寻找集合中的特定元素。我怎么知道它是否存在于集合中？
回答by shahkalpesh
Collection are index based. Hence, you will have to loop through the collection to search for an item.
集合是基于索引的。因此，您必须遍历集合以搜索项目。
Sub test()
Dim iCtr As Integer
Dim itemCount As Integer
Dim myData As Collection
Set myData = New Collection
Dim searchFor As String
myData.Add "MS", "11"
myData.Add "Oracle", "22"
myData.Add "Google", "33"
'** Searching based on value
searchFor = "Google"
itemCount = myData.Count
For iCtr = 1 To itemCount
If myData(iCtr) = searchFor Then
MsgBox myData(iCtr)
Exit For
End If
Next
'** Searching by key
MsgBox myData.Item("22")
End Sub
回答by GSerg
If you used a key when you added the item to the collection, see if referring to this key gives an error:
如果在将项目添加到集合时使用了键，请查看引用此键是否会出错：
on error goto no_item
col.Item "key"
msgbox "Item exists"
exit sub
no_item:
msgbox "Item does not exist"
Otherwise you have to loop through all items to see if there's the one you need.
否则，您必须遍历所有项目以查看是否有您需要的项目。
回答by schmijos
I use a simple tool function which iterates through a collection. It's without directly accessing indexes and it uses VBA language features like they should be used (Comparison of variants and each-Loop).
我使用一个简单的工具函数来遍历一个集合。它没有直接访问索引，它使用了应该使用的 VBA 语言功能（变体和每个-Loop 的比较）。
Public Function ExistsIn(item As Variant, lots As Collection) As Boolean
Dim e As Variant
ExistsIn = False
For Each e In lots
If item = e Then
ExistsIn = True
Exit For
End If
Next
End Function
回答by dee
@Josua Schmid:
@约书亚施密德：
I think the code in your answer could be correct but could be not correct as well. Your function has paremeter of type Variant and it is then compared to each menber of the collection. But what is compared actually? In this case the default member is compared. So firts problem could arise if the collection will contain members of some custom class which does not have default member specified. In such case runtime error 438 object doesn't support this property or method will be raised. Well you could add default member but even then it will work in a way you maybe not like I am afraid.
我认为您的答案中的代码可能是正确的，但也可能不正确。您的函数具有 Variant 类型的参数，然后将其与集合的每个成员进行比较。但实际上比较的是什么呢？在这种情况下，将比较默认成员。因此，如果集合将包含某些未指定默认成员的自定义类的成员，则可能会出现第一个问题。在这种情况下，将引发运行时错误 438 对象不支持此属性或方法。好吧，您可以添加默认成员，但即便如此，它也会以您可能不喜欢的方式工作。
Example with ranges (for Range-Class Value is the default member so Values will be compared). Maybe it is exactly what you wanted but maybe not. So from my point of view better is to use 'Key' for each Item added to collection and then try to get this Item by its Key.
带有范围的示例（对于 Range-Class Value 是默认成员，因此将比较 Values）。也许这正是您想要的，但也许不是。因此，从我的角度来看，更好的是对添加到集合中的每个项目使用“密钥”，然后尝试通过其密钥获取该项目。
Debug.Print col.item(r1.Address) ' A1 Value
Debug.Print col.item(r1.Address) ' A1 值
Or by index if no keys were used:
或者如果没有使用键，则按索引：
Debug.Print col.item(1) ' A1 Value
Debug.Print col.item(1) ' A1 值
Sub test()
Dim col As New VBA.Collection
Dim r1 As Range
Dim r2 As Range
Dim r3 As Range
Set r1 = Range("a1")
Set r2 = Range("b1")
Set r3 = Range("c1")
r1 = "A1 Value"
r2 = "B1 Value"
r3 = "C1 Value"
col.Add r1, r1.Address
col.Add r2, r2.Address
col.Add r3, r3.Address
Debug.Print ExistsIn(r1, col)
Debug.Print ExistsIn(r2, col)
Debug.Print ExistsIn(r3, col)
Dim r4 As Range
Set r4 = Range("d1")
r4 = "A1 Value"
Debug.Print ExistsIn(r4, col)
End Sub
Output:
输出：
True
True
True
True
回答by Ankur G
It is possible to determine the collection item with a variant. In the example below, I am determining if an object with a specific index exists in the FXItems collection or not. If it doesnt exist, system will create it otherwise, do some other action
可以使用变体来确定集合项目。在下面的示例中，我正在确定 FXItems 集合中是否存在具有特定索引的对象。如果它不存在，系统将创建它，否则，执行一些其他操作
On Error Resume Next
Dim myFXItem as FXItem
Set myFXItem = FXItems.item("USDEUR")
On Error GoTo 0
If myFXItem Is Nothing Then
Set myFXItem = New FXItem
myFXItem.sCurr = "USDEUR"
FXItems.Add item:=myFXItem, Key:="USDEUR"
Else
myFXItem.dRate = 0.834
myFXItem.dtValueDate = #12-03-2018#
End If
Set myFXItem = Nothing
