Error This key is already associated with an element of this collection

Question

I am working on vba macros. I was trying to use a dictionary. But it is giving error 457 with debugger pointing to toprow.Add ActiveCell.value, val. Can anyone please tell the issue? I even used Cstr(activecell.value), Cstr(val) as mentioned in one of the answer on similar issue.

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

Bundestag

It seems that ActiveCell.value is not unique, i.e. there is at leasta duplicated value in the A column. – Mcardle

They are all unique in my excel – Bundestag

Maybe ColLen > the range in column A, in which case it tries to add multiple empty cells = "" keys? – Witham

Actually it was working fine and when I put some more code below it to operate on those values, it started giving this issue! – Bundestag

I'd inspect the current value of "ActiveCell.Value" when VBA gives you an error - then try to find that value in column A. Another option is that your Range("A1") is on another workbook than the one you expect? – Witham

Do you want to add the values in the row or the values in the column to the dictionary? If it's the values in the column, you should use ActiveCell.Offset(1, 0) instead of ActiveCell.Offset(0, 1). – Mcardle

Answer 1

Adding keys with dictionaries is only possible when a key does not already exist. Accidentally you could entered the key before, or you are watching the key with the debug watcher, creating the key instanteneously. (= If you watch a certain key in a dictionary it gets created if it doesn't already exist).

You have to

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

Cushiony

Answer 2

You can also add some very basic error handling, if all you wish to do is skip over the record throwing this error. I simply inserted the below line immediately above the one which was generating this error for me, and now it happily moves along, ignoring duplicate keys which used to throw this error.

On Error Resume Next

Nl

How will this line help with the error handling? Perhaps an On Error Goto 0 or even a if Err.number <> 0 then would help. – Planetstruck

I am new to VBA so please do correct me if I'm mistaken here; my comments are based on my own testing with running my full macro across a limited data set. All I needed was to ignore the duplicate value because I'm using a roll-up macro, which provides a union of values. (So, once I have a value once I don't need to do anything with it if it shows up again) I added the line I reference above, and my tested results were good - seems to ignore the error just fine. Am I missing something? – Nl

bpboldin, the reason for dakab's comment is that this is not error handling, it is error skipping. Using if err.number <> 0 and then if not a known error, with something the code can do to work around it, printing err.number, err.source, and err.description to screen or file would handle the error, rather than causing an unexpected halting of the script which doesn't allow for files to be closed, a log of the details of the reason for the halting, or any user-friendly error messages to be displayed. – Purree

Answer 3

I was getting the same error message: "Error This key is already associated with an element of 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 was supposed to 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

Maybe you just have to double check your "Property Let" code to see if you are using appropriate names for those variables that are private in your class.

Variant

Answer 4

You can test if Activecell.value already exists as key in the dictionary or not to fix this error. If the key already exists, trying to add it again returns the above run-time error '457'.

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)
    
    If Not toprow.exists(ActiveCell.value) Then 
         toprow.Add ActiveCell.value, val
    End if

    i = i + 1
    ActiveCell.Offset(0, 1).Activate
Loop

Bertina