Error This key is already associated with an item in this collection.

Question

I am working on vba macros. I tried to use a dictionary. But this gives error 457 debugger pointing to toprow.Add ActiveCell.value, val . Can anyone talk about the problem? I even used Cstr(activecell.value), Cstr(val) , as mentioned in one of the answers to a similar problem.

 Dim toprow As New Dictionary, Dictkey As Variant Dim val As String Range("A1").Activate i = 0 Do Until i = ColLen val = Chr(65 + i) toprow.Add ActiveCell.value, val i = i + 1 ActiveCell.Offset(0, 1).Activate Loop 

Answer 1

Keys

Add with dictionaries is possible only when the key does not yet exist. By chance, you might have entered the key earlier, or you were watching key with a debugging observer, creating the key instantly. (= If you are viewing a specific key in a dictionary, it is created if it does not already exist).

You should

• make sure you are not viewing the key with the debugger
• create unique records by testing on d.Exists(keyname) , and then use the d.Add keyname, value method d.Add keyname, value
• alternatively you can overwrite existing keys by default using d.Item(keyname) = value

Answer 2

I received the same error message: "Error This key is already associated with an item in this collection." In my case, the problem was that I had this:

 'assign values to properties Property Let EmployeeName(Valor As String) m_employeename = Valor End Property Property Let EmployeeID(Valor As String) m_employeename = Valor End Property 

I should have this:

 'assign values to properties Property Let EmployeeName(Valor As String) m_employeename = Valor End Property Property Let EmployeeID(Valor As String) m_employeeid = Valor End Property 

Perhaps you just need to double check your Property Let code to see if you use the appropriate names for those variables that are private in your class.

Answer 3

You can also add some very simple error handling procedures if all you want to do is skip the record that reset this error. I simply inserted the bottom line directly above the one that generated this error for me, and now it is happy to move forward, ignoring the duplicate keys that were used to throw this error.

 On Error Resume Next