vba 避免集合中的重复值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18799590/
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
Avoid duplicate values in Collection
提问by user2758292
I have following values, and I want to add these to a collection. If the values are already in the collection, a message should show "this is already added in your collection".
我有以下值，我想将它们添加到集合中。如果这些值已在集合中，则应显示一条消息“这已添加到您的集合中”。
Dim OrdLines As New Collection
OrdLines.Add (111,this is first item)
OrdLines.Add (222,this is second item)
OrdLines.Add (333,this is third item)
OrdLines.Add (444,this is fourth item)
How do I avoid duplicate values in a collection?
如何避免集合中的重复值？
回答by Siddharth Rout
To avoid duplicates without any promptsuse this method.
为避免重复，请without any prompts使用此方法。
Code
代码
Sub Sample()
Dim col As New Collection
Dim itm
On Error Resume Next
col.Add 111, Cstr(111)
col.Add 222, Cstr(222)
col.Add 111, Cstr(111)
col.Add 111, Cstr(111)
col.Add 333, Cstr(333)
col.Add 111, Cstr(111)
col.Add 444, Cstr(444)
col.Add 555, Cstr(555)
On Error GoTo 0
For Each itm In col
Debug.Print itm
Next
End Sub
ScreenShot
截屏
Explanation
解释
A collection is an ordered set of items that you can refer to as a unit. The syntax is
集合是一组有序的项目，您可以将其称为一个单元。语法是
col.Add item, key, before, after
A collection cannot have the same key twice so what we are doing is creating a key using the item that we are adding. This will ensure that we will not get duplicates. The On Error Resume Nextis just telling the code to ignore the error we get when we try to add a duplicate and simply move on to the next item to add. The CHR(34)is nothing but "so the above statement can also be written as
一个集合不能有两次相同的键，所以我们正在做的是使用我们添加的项目创建一个键。这将确保我们不会得到重复。这On Error Resume Next只是告诉代码忽略我们在尝试添加重复项时遇到的错误，只需移至要添加的下一项。The CHR(34)is nothing but"所以上面的语句也可以写成
col.Add 111, """" & 111 & """"
Suggested Read
推荐阅读
The Visual Basic Collection Object
HTH
HTH
回答by Bob77
This is one of those scenarios where a Dictionary offers some advantages.
这是字典提供一些优势的场景之一。
Option Explicit
'Requires a reference to Microsoft Scripting Runtime.
Private Sub Main()
Dim Dict As Scripting.Dictionary 'As New XXX adds overhead.
Dim Item As Variant
Set Dict = New Scripting.Dictionary
With Dict
.Item(111) = 111
.Item(222) = 222
.Item(111) = 111
.Item(111) = 111
.Item(333) = 333
.Item(111) = 111
.Item(222) = 222
.Item(333) = 333
For Each Item In .Items
Debug.Print Item
Next
End With
End Sub
回答by vcs
Use the Addmethod along with key.
将Add方法与密钥一起使用。
Syntax:
句法：
OrderLines.Add(ObjectToAdd, Key)
Remember key is a string.
记住 key 是一个字符串。
Example:
例子：
OrdLines.Add(222,"222")
OrdLines.Add(222,"333")
OrdLines.Add(222,"444")
OrdLines.Add(222,"222") 'This will give error
回答by David Zemens
There is a built in method that allows you check for duplicates, assuming you arealways assigning a Keyvalue. This is preferably than On Error Resume Next.
有一种内置的方法，可以让你重复检查，假设你是始终分配一个Key值。这最好比On Error Resume Next。
If Not OrdLines.Contains(key_value) Then
OrdLines.Add(item_value, key_value, before, after)
End If
NOTEThis is VB.NET, not VBA/VB6. In VBA/VB6 you could write a custom function similar to the approach given, here.
注意这是 VB.NET，而不是 VBA/VB6。在 VBA/VB6 中，您可以编写类似于此处给出的方法的自定义函数。
