VBA错误：此键已与该集合的元素相关联 [英] VBA Error: This key is already associated with an element of this collection

问题描述

我使用下面的代码从Excel中的一个范围中创建一个集合，然后用于填充用户窗体上的列表框。代码已经在两个单独的宏中工作了好几个月，但突然两者都停止工作，并在标题中抛出错误。

I use the code below to make a collection from a range in excel that is then used to populate a list box on a userform. Code has been working fine for months in two separate macros but suddenly both stopped working and throw error in title.

Private Sub UserForm_Initialize()

    Dim ws As Worksheet
    Dim LR As Long
    Dim cell As Range
    Dim List As New Collection
    Dim Item As Variant
    Set ws = Worksheets("ExpenseCATs")

    With ws
       LR = .Cells(.Rows.Count, 1).End(xlUp).Row

       For Each cell In .Range("A2:A" & LR)
            With cell
                On Error Resume Next
                List.Add .Text, CStr(.Value)   <---------- Error
                On Error GoTo 0
            End With
        Next cell

        For Each Item In List
                        EXPListBox1.AddItem Item
        Next Item

    End With

由于所有条目均为文本，因此我已将错误行的一部分注释为以下

As all entries are text, I commented out part of the error line as below

List.Add .Text ', CStr(.Value)

这不会引发错误，但是e列表框不会填充唯一的值，而是列出单元格范围内的所有项目。

This does not throw an error, but the Listbox does not populate with unique values, instead it lists all items in the cell range.

我已经查看了有关此错误的其他帖子，但无法解析。任何人都可以建议如何修复，也有兴趣为什么可能停止工作。

I've looked at other posts on this error but not able to resolve. Can anyone advise how to fix, also interested in why may have stopped working.

推荐答案

在VBA项目中，选项 - >常规 - >错误陷阱，检查打破未处理的错误

In VBA project, Options-->General-->Error Trapping, check Break On Unhandled Errors

：）

这篇关于VBA错误：此键已与该集合的元素相关联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！