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?

Siddharth Rout · Accepted Answer · 2016-08-08T17:33:07.907

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

What is the point of using `Chr(34)` or `""""` at all? Why not just wrap it around with `CStr()`? — Mike Weir, Sep 14 '13 at 22:58
@PhoenixX_2: Yup you have a point, it completely skipped my mind :) — Siddharth Rout, Sep 15 '13 at 13:21

score 8 · Answer 2 · answered Sep 15 '13 at 01:01

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

+1 for suggesting dictionaries. I think a lot of people just aren't aware of them or how to use them. This is a great way to use them though, as the initial call creates the item if it didn't exist and subsequent calls to the same item just overwrite it. You don't even need to use the `.add` method. Nice work! — David Zemens, Sep 15 '13 at 17:35

score -1 · Answer 3 · edited Mar 12 '15 at 15:07

-1

Use the Add method along with 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

edited Mar 12 '15 at 15:07

bluish

26,356
27
122
180

answered Sep 14 '13 at 08:19

vcs

3,675
4
17
15

score -1 · Answer 4 · edited May 23 '17 at 12:01

-1

There is a built in method that allows you check for duplicates, assuming you are always assigning a Key value. This is preferably 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 could write a custom function similar to the approach given, here.

edited May 23 '17 at 12:01

Community

1
1

answered Sep 14 '13 at 13:40

David Zemens

53,033
11
81
130

That is for VB.NET and not for VB6/VBA. – Mike Weir Sep 14 '13 at 16:55
@PhoenixX_2 syntax is the same. I use this method in VBA all the time. It's a perfectly valid answer. – David Zemens Sep 14 '13 at 17:11
But now I've gone and changed my link (thinking you though it *should* have been a link to VB.NET) and now I lost the old URL, so yeah, now the URL is pointing to VB.NET, but in any case the method and its syntax are the same. – David Zemens Sep 14 '13 at 17:13
I get an "Object doesn't support this property or method." error. I wish what you were saying was true, because I'm using `On Error Resume Next` as well in my projects :(. – Mike Weir Sep 14 '13 at 18:36
Ahhh you're totally right I must have been thinking of `dictionary.Exists` method... I recently wrote a UDF to replicate this method on vector arrays, and I think I may have done that also for `Collection` objects, too so that must have been what I was thinking about. Thanks for setting me straight :) – David Zemens Sep 14 '13 at 23:38
I removed the -1, but someone else put another one in. Ugh, this site is pretty lame sometimes. – Mike Weir Sep 15 '13 at 15:09
@PhoenixX_2 no worries! – David Zemens Sep 15 '13 at 17:33

Avoid duplicate values in Collection

4 Answers4

Linked