Excel VBA运行时错误‘424’：对象必需问题排查

错误根源及修复点： #

• 变量声明语法错误
  VBA中Dim s1, C As Collection的写法仅会将C声明为Collection类型，s1默认是Variant类型，这会导致后续调用s1.Count时出现对象引用问题。需修改为分别声明每个变量的类型：

  Dim s1 As Collection, C As Collection
  

• 集合对象未实例化
  声明集合变量后，必须通过Set关键字创建集合实例，否则变量指向Nothing，传递给子过程时就会触发“对象必需”错误。需在使用前添加初始化代码：

  Set s1 = New Collection
  Set C = New Collection
  

• 建议强制变量声明
  在模块顶部添加Option Explicit，强制所有变量必须显式声明，避免found、n这类隐式变量引发的潜在问题。修正后的子过程代码：

  Option Explicit
  
  Private Sub SetUnion(ByVal e As String, ByVal Coll As Collection)
      Dim found As Boolean
      Dim n As Integer
      found = False
      For n = 1 To Coll.Count
          If Coll(n) = e Then
              found = True
              Exit For
          End If
      Next n
      If Not found Then Coll.Add e
  End Sub
  

完整修复后的调用示例 #

Option Explicit

Sub TestSetUnion()
    Dim s1 As Collection, C As Collection
    Dim n As Integer
    
    ' 初始化集合并添加测试元素
    Set s1 = New Collection
    s1.Add "Apple"
    s1.Add "Banana"
    s1.Add "Apple" ' 重复元素
    
    Set C = New Collection
    
    For n = 1 To s1.Count
        SetUnion s1(n), C
    Next n
    
    ' 输出验证结果：C中仅包含不重复的Apple和Banana
    For n = 1 To C.Count
        Debug.Print C(n)
    Next n
End Sub

内容的提问来源于stack exchange，提问作者Bernard Cohen