Avoid duplicate values in Collection

Question

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?

Answer 1

To avoid duplicates 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

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 Next is 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

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

Suggested Read

The Visual Basic Collection Object

HTH

Answer 2

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