Verifying unique keys in collection

Question

I'm attempting to verify that the key for a VBA collection does not already exist in the collection. I am using a list of UNIX accounts as my keys, which I've already verified is unique. I then iterate over a series of sheets containing the user data. Because of how the data is laid out, I run a two pass system, one to pick the usernames and the second to correlate data not having a username in the record. When I go to add the usernames on the first pass, sometimes it will throw an error saying that the key already exists in the collection.

My most recent attempt to overcome this error is as follows: NOTE: account is a class I wrote to store all of the information, and Accounts is the collection.

Public Function ContainsKey (key as String)
    Dim retVal as Boolean
    Dim record as account

    retVal = False

    On Error GoTo Finish
    record = Account.item (key)

    If  not record = Empty Then
        retVal = True
    End If

Finish:

    ContainsKey = retVal
End Function

I've stepped through the code all the way through to the error and the if statement is never executed, but it's still complaning about a duplicate key. I don't undertand what's going on with the collection, why it's throwing a duplicate key error when I'm checking for duplcates before adding them into the collections.

Any and all help is much appreciated! Thank you all in advance for your help.

Thanks, Jeremy

`Set record = Account.item (key)` since record is an object variable... If you don't check the error number you can't distinguish one cause from another... See also: http://stackoverflow.com/questions/137845/determining-whether-an-object-is-a-member-of-a-collection-in-vba — Tim Williams, Nov 25 '13 at 22:06
Wow, I can't believe I forgot the "set". I'm still stuck in the C/Java mindset. — Madviola, Nov 26 '13 at 14:52

score 1 · Accepted Answer · answered Nov 25 '13 at 22:53

To add to my comment and @rheitzman's answer - the error number you get depends on exactly what caused the error, so you need to check:

Sub Tester()
Dim c As New Collection, x As Long
Dim v As Object

    For x = 1 To 10
        c.Add ActiveSheet.Cells(x, 1), "Cell" & x
    Next x

    On Error Resume Next

    v = c.Item("Cell4") 'missing Set keyword
    Debug.Print Err.Number, Err.Description
    '91   "Object variable or With block variable not set"
    If Err.Number <> 0 Then Err.Clear

    Set v = c.Item("Cell4") ' should work OK assuming key exists
    Debug.Print Err.Number, Err.Description
    '0
    If Err.Number <> 0 Then Err.Clear

    Set v = c.Item("Cell33") 'non-existent key
    Debug.Print Err.Number, Err.Description
    '5  "Invalid procedure call or argument"
    If Err.Number <> 0 Then Err.Clear

    c.Add ActiveSheet.Cells(x, 11), "Cell5" 'add duplicate key
    Debug.Print Err.Number, Err.Description
    '457 "This key is already associated with an element of this collection"

End Sub

@Madviola have you read the comments? +1 Tim great demonstration — , Nov 26 '13 at 14:54
I did read it, but I didn't fully understand the importance of "on error resume next" till just now. That allowed me to get to the if statement and assign the proper return value. Oh, and I did end up changing the if condition some to make it work. Thank you Tim! — Madviola, Nov 26 '13 at 16:12

score 0 · Answer 2 · answered Nov 25 '13 at 22:44

0

This code assumes account is a simple Collection

        Public Function ContainsKey2(key As String) As Integer
        On Error Resume Next
            account.Add key, key
            ContainsKey2 = (Err = 457)
            If Err <> 0 And Err <> 457 Then MsgBox Err & " " & Err.Description
        End Function

answered Nov 25 '13 at 22:44

rheitzman

2,187
1
15
32

Thank you for your answer. The solution was indeed in here, but I just didn't see it till later. Thank you for your help! – Madviola Nov 26 '13 at 18:22

Verifying unique keys in collection

2 Answers2