Avoid duplicating values ​​in a collection

Question

I have the following values, and I want to add them to the collection. If the values ​​are already in the collection, the message should show that "it has already been added to 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 to avoid duplication of values ​​in a collection?

Answer 1

To avoid duplication without any prompts , use this method.

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 

Screen shot

Explanation

A collection is an ordered set of elements that can be called a unit. Syntax

 col.Add item, key, before, after 

A collection cannot have the same key twice, so we create the key using the element to be added. This ensures that we do not get duplicates. On Error Resume Next simply says that the code ignores the error that we get when we try to add a duplicate and simply move on to the next element to be added. CHR(34) is nothing more than " , so the above statement can also be written as

 col.Add 111, """" & 111 & """" 

Recommended Reading

Visual Basic Collection Object

NTN

Answer 2

This is one of those scenarios in which the 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 

Answer 3

Use the Add method with the key.

Syntax:

 OrderLines.Add(ObjectToAdd, Key) 

Remember key is a string.

Example:

 OrdLines.Add(222,"222") OrdLines.Add(222,"333") OrdLines.Add(222,"444") OrdLines.Add(222,"222") 'This will give error 

Answer 4

There is a built-in method that allows you to check for duplicates , assuming that you always assign a value to Key . This is preferable than On Error Resume Next .

 If Not OrdLines.Contains(key_value) Then OrdLines.Add(item_value, key_value, before, after) End If 

NOTE This is VB.NET, not VBA / VB6. In VBA / VB6, you can write a user-defined function similar to the approach given here .