Excel VBA 在出错处理程序后仍然中断

Question

我有一段非常简单的代码，它循环访问一列数据并向集合添加唯一值。

它是 VBA，所以Collection当然缺少Exists函数（谁会想要它？），我宁愿避免为列中的每个单元格迭代整个集合，我决定采用错误处理方法 - 尝试从集合中检索项目，捕获如果它不存在时发生的错误并添加它：

'Trucated the code slightly, I know I should be checking the actual error code, but omitted that for brevity
Dim r As Range
Set r = MySheet.Range("B2") 'First cell in column
Dim uniqueValues As New Collection
Do While r.Value <> ""
    On Error GoTo ItemExists
    'If r.Value doesn't exist in the collection, throws an error
    uniqueValues.Add(Item:=r.Value, Key:=r.Value) 
ItemExists:
    r.Offset(1)
Loop

问题出在哪里？Excel似乎完全忽略了On Error行，破坏了代码并抛出了Continue/End/Debug对话框。

我已经检查了 VBA 中的选项，它已正确设置为 Break on Unhandled Errors.

知道为什么会这样吗？

Answer 1

您可以使用 On Error Resume Next，但最好将错误处理封装在其自己的 Sub 或 Function 中。 例如，像这样：

Private Sub AddIfNotPresent(Coll As Collection, Value As Variant, Key As Variant)
    On Error Resume Next
    Coll.Add Item:=Value, Key:=Key
End Sub

您可以按如下方式使用：

Do While r.Value <> ""
    AddIfNotPresent uniqueValues, r.Value, r.Value
    r = r.Offset(1)
Loop

问题的原因是在 VBA 文档中描述的 On Error：

"已启用"错误处理程序是由 On Error 语句打开的处理程序;"活动"错误处理程序是正在处理错误的已启用处理程序。如果在错误处理程序处于活动状态时发生错误（在错误发生与 Resume、Exit Sub、Exit 函数或 Exit 属性语句之间），则当前过程的错误处理程序无法处理该错误

在第一个错误之后，您尚未调用 Resume 或退出该过程，因此错误处理程序无法处理后续错误。

更新

来自评论：

我讨厌促进使用"错误恢复下一个"的想法......

我可以同情这个 POV，但 VBA 中的一些事情（例如检查集合中是否存在键）您只能通过处理错误来完成。 如果在专用的帮助程序方法（子/函数）中执行此操作，则这是一种合理的方法。 当然，您可以使用 On Error Goto 代替，例如以下内容（上述变体，用于测试集合是否包含给定键）：

Public Function ContainsKey(Coll As Collection, Key As Variant) As Boolean
    On Error GoTo ErrHandler
    Dim v As Variant
    v = Coll(Key)
    ContainsKey = True
    Exit Function
ErrHandler:
    ContainsKey = False
    Exit Function
End Function

Answer 2

改用 ArrayList，它有一个 .包含方法以及其他方便的东西，如.排序方法

With CreateObject("System.Collections.ArrayList")
    .Add "Item 1"
    .Add "Item 2"
If .Contains "Item 1" Then Msgbox "Found Item 1"
If .Contains "Item 3" Then Msgbox "Found Item 3"

End With

更多例子在这里