VBA运行时错误1004：_Worksheet的Range方法调用失败求助

问题场景 #

全局变量声明（独立模块）：

Public TargetWorkbook       As Workbook
Public SourceWorkbook       As Workbook
Public myWorksheet          As Worksheet
Public SourceWorksheet      As Worksheet
Public TargetWorksheet      As Worksheet
Public SettingsWorksheet    As Worksheet

函数内代码片段：

Dim FoundRange As Range

    'Fields Required (Import)
    Dim RowStartField       As Long
    Dim RowCountField       As Long
    Dim RowLastField        As Long
    Dim ColStartField       As Long
    Dim ColLastField        As Long
    Dim ColCountField       As Long

    Application.ScreenUpdating = False
    Set TargetWorksheet = TargetWorkbook.Worksheets("Fields Required")

Debug.Print TargetWorksheet.Name
Debug.Print TargetWorkbook.Name
    
    Set FoundRange = TargetWorksheet.Range(Cells(1, 1), Cells(1, 10)).Find("Column Number")
    If Not FoundRange Is Nothing Then ColumnNumberColumn = Range(FoundRange.Address).Column
        
    Set FoundRange = Range(Cells(1, 1), Cells(1, 10)).Find("Report Fields")
    If Not FoundRange Is Nothing Then FieldsColumn = Range(FoundRange.Address).Column
            
    Set FoundRange = Range(Cells(1, 1), Cells(1, 10)).Find("Qunote Fields")
    If Not FoundRange Is Nothing Then QunoteFieldColumn = Range(FoundRange.Address).Column

已确认TargetWorkbook在上级函数中定义，Debug.Print也验证了TargetWorksheet和TargetWorkbook为预期对象，但执行Set FoundRange = TargetWorksheet.Range(Cells(1, 1), Cells(1, 10)).Find("Column Number")时触发运行时错误1004。

错误原因 #

核心问题是未限定Cells对象的父工作表：

• Cells(1,1)和Cells(1,10)默认指向当前活动工作表，而非指定的TargetWorksheet。
• 当TargetWorksheet不是当前活动表时，TargetWorksheet.Range(活动表的Cells, 活动表的Cells)属于跨工作表非法引用，直接触发1004错误。
• 后续的Range(FoundRange.Address)同样存在该问题，未限定父工作表会默认使用活动表，即使找到结果也可能返回错误的列号。

修正方案 #

所有Cells和Range调用必须明确限定父对象为TargetWorksheet，推荐使用With语句简化代码：

With TargetWorksheet
        Set FoundRange = .Range(.Cells(1, 1), .Cells(1, 10)).Find("Column Number")
        If Not FoundRange Is Nothing Then ColumnNumberColumn = FoundRange.Column
        
        Set FoundRange = .Range(.Cells(1, 1), .Cells(1, 10)).Find("Report Fields")
        If Not FoundRange Is Nothing Then FieldsColumn = FoundRange.Column
                
        Set FoundRange = .Range(.Cells(1, 1), .Cells(1, 10)).Find("Qunote Fields")
        If Not FoundRange Is Nothing Then QunoteFieldColumn = FoundRange.Column
    End With

也可以直接显式指定父对象：

Set FoundRange = TargetWorksheet.Range(TargetWorksheet.Cells(1, 1), TargetWorksheet.Cells(1, 10)).Find("Column Number")
    If Not FoundRange Is Nothing Then ColumnNumberColumn = FoundRange.Column
        
    Set FoundRange = TargetWorksheet.Range(TargetWorksheet.Cells(1, 1), TargetWorksheet.Cells(1, 10)).Find("Report Fields")
    If Not FoundRange Is Nothing Then FieldsColumn = FoundRange.Column
            
    Set FoundRange = TargetWorksheet.Range(TargetWorksheet.Cells(1, 1), TargetWorksheet.Cells(1, 10)).Find("Qunote Fields")
    If Not FoundRange Is Nothing Then QunoteFieldColumn = FoundRange.Column

补充提示 #

VBA中，Cells、Range、Rows、Columns这类对象若不明确指定父对象，会默认绑定到当前活动工作表，这是跨表引用错误的常见诱因，养成显式限定父对象的习惯可避免绝大多数此类问题。

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