VBA函数运行报'Object required'错误，子程序正常，求排查

我编写了一段VBA代码，用于对比两个独立工作簿的数据并删除其中一个工作簿的重复数据。该代码作为Sub（子程序）运行完全正常，但改为Function（函数）后调用时出现'Object required'错误。我仅将变量移至函数参数列表（原Sub中的变量定义和赋值已注释），以下为函数代码及调用代码，请求排查错误来源：

函数代码 #

Public Function removedupes(wb1 As Workbook, wb2 As Workbook, ws1 As Worksheet, ws2 As Worksheet, month As Range, cell As Range)
    
    On Error Resume Next
    'define variables
    'Dim wb1 As Workbook
    'Dim wb2 As Workbook
    'Dim ws1 As Worksheet
    'Dim ws2 As Worksheet
    Dim itemmatch As Range
    'Dim cell As Range
    'Dim month As Range
    Dim firstmatch As String
    Dim item As Long
     
    'Set wb1 = Workbooks("workbook 1")
    'Set wb2 = Workbooks("workbook 2")
    'Set ws1 = wb1.Worksheets("sheet 1")
    'Set ws2 = wb2.Worksheets("sheet 2")
    'Set month = ws1.Range("A:A")

    'finds matching item numbers
    With month
    'Set cell = ws2.Range("B3")
    Do Until cell.Value = 0
    item = cell.Value
    Set itemmatch = .Find(item, LookIn:=xlValues, LookAt:=xlPart)
        If Not itemmatch Is Nothing Then
    'deletes dupelicate cells
                itemmatch.Resize(1, 12).Delete shift:=xlUp
                Set cell = cell.Offset(1, 0)
                item = cell.Value
        Else
            Set cell = cell.Offset(1, 0)
        End If
    Loop
    End With
    
End Function

调用代码 #

removedupes Workbooks("workbook 1"), Workbooks("workbook 2"), wb1.Worksheets("worksheet 1"), wb2.Worksheets("worksheet 2"), ws1.Range("A:A"), ws2.Range("B3")

错误原因及解决方法 #

1. 核心错误：调用代码中未定义对象变量就直接引用
   调用代码里的wb1、wb2、ws1、ws2是函数的参数，在当前调用上下文里并没有被赋值定义，VBA无法识别这些对象，直接触发'Object required'错误。

2. 修正调用代码的两种方式

   • 方式一：直接传递完整的对象引用

     removedupes Workbooks("workbook 1"), Workbooks("workbook 2"), _
                 Workbooks("workbook 1").Worksheets("worksheet 1"), _
                 Workbooks("workbook 2").Worksheets("worksheet 2"), _
                 Workbooks("workbook 1").Worksheets("worksheet 1").Range("A:A"), _
                 Workbooks("workbook 2").Worksheets("worksheet 2").Range("B3")
     

   • 方式二：先定义对象变量再传递（更清晰易读）

     Dim wb1 As Workbook, wb2 As Workbook
     Dim ws1 As Worksheet, ws2 As Worksheet
     ' 确保工作簿已打开，否则会报错
     Set wb1 = Workbooks("workbook 1")
     Set wb2 = Workbooks("workbook 2")
     Set ws1 = wb1.Worksheets("worksheet 1")
     Set ws2 = wb2.Worksheets("worksheet 2")
     ' 调用函数
     removedupes wb1, wb2, ws1, ws2, ws1.Range("A:A"), ws2.Range("B3")
     

3. 其他优化建议

   • 去掉On Error Resume Next：调试阶段该语句会掩盖其他潜在错误（如工作簿未打开、工作表名称拼写错误），调试完成后再根据需要添加错误处理逻辑。
   • 明确函数返回值类型：如果不需要返回值，其实更适合用Sub；若坚持用Function，可定义为Public Function removedupes(...) As Boolean，并在函数末尾添加removedupes = True表示执行完成。

内容的提问来源于stack exchange，提问作者Ethan Brown