Runtime Error 457: “This key is already associated with an element of this collection,” explained and fixed

Error 457, “This key is already associated with an element of this collection”, fires when Collection.Add is given a key that the collection already holds. Keys in a VBA Collection must be unique; they are the collection's index-by-name mechanism, and a duplicate is refused outright. The usual story is data that was assumed unique (names, codes, IDs) meeting a real duplicate, or code that adds an item on a path that can run twice for the same item. The collection is protecting its index; the assumption of uniqueness is what failed.

For people who just run the program

  1. Look for the duplicate in the data. Two records with the same name, code, or ID, sometimes differing only by a trailing space or capitalization, are the everyday cause.
  2. Note whether the job ran twice. A re-run that re-adds items already loaded, where the first run was expected to be the only one, produces the same collision.
  3. Do not delete records at random to make it stop. Identify the actual duplicated key first; the error is doing you the favor of pointing at a genuine data quality issue.
  4. Report the suspected duplicate value. The key in question is the maintainer's fastest reproduction.

For people who write the code

  1. Check existence before adding when duplicates are possible. Collections raise an error for a missing key read, so the existence test is a deliberately trapped probe:
    2. Function HasKey(c As Collection, k As String) As Boolean
    Dim probe As Variant
    On Error Resume Next
    probe = c(k)
    HasKey = (Err.Number = 0)
    On Error GoTo 0
End Function

If Not HasKey(cache, key) Then cache.Add value, key
  2. Normalize keys at the boundary. Trim spaces and fix case (UCase$(Trim$(key))) when building keys from data, so “ABC” and “abc ” collide visibly in testing rather than silently becoming two entries for one thing, or one error for two.
  3. Choose the structure that matches the semantics. If duplicates are legitimate data, a keyed Collection is the wrong container: use an unkeyed collection, or a Dictionary with an explicit overwrite/append policy. Error 457 is often the design announcing itself.
Suppressing 457 with On Error Resume Next hides data problems. Swallowing the error leaves the first item under the key and discards the second silently, which converts a visible collision into quietly wrong results. Handle the duplicate deliberately and log it.

If it still happens

If the data is provably unique, the same item is being added twice by two code paths (an initialization that runs on both open and first use, for example). Log each Add with its key and call site; the double-add shows up as the same key arriving from two places.

Common questions

Are collection keys case-sensitive? No. VBA Collection keys compare without regard to case, so “Key1” and “KEY1” are the same key and the second Add raises error 457.

Why is there no simple Exists method on Collection? A long-standing gap in the VBA object model. The trapped-probe pattern is the standard workaround; the Scripting.Dictionary, by contrast, provides a native Exists method.

Related errors

Runtime Error 9 · Runtime Error 92 · Runtime Error 381