VBA运行时1004错误：基于变量与偏移设置Range对象失败排查

问题描述 #

• 需求：动态生成Range区域，A列固定，起始行号为动态值，区域右下角为起始单元格偏移n行、n列的单元格，预期效果同Range("A2:B94")，用于后续写入表格数据
• 故障现象：直接拼接Range字符串调用时触发运行时错误'1004'：对象_global的Range方法调用失败，改用Resize方法重写代码后仍然报错，原代码如下：

Sub comptst()
Dim line0 As Long, nrow0 As Long, ncol0 as long, diff0 As Long
Dim k As Integer
Dim rng0 As Range

Application.DisplayAlerts = False 'turns off alert display for deleting files sub-routine

For k = 6 To Sheets.Count 'tst_1 is indexed at position 5.
ThisWorkbook.Sheets(k).Activate
    Set fsobj = New Scripting.FileSystemObject
    If Not fsobj.FileExists(Range("A1")) Then MsgBox "File is missing on sheet index-" & k
Cells(1, 1).Select 'find starting row number
    Do
        If ActiveCell.Value = "Latticed" Then
            b0 = ActiveCell.row 'starting row position
            Exit Do
        Else
            ActiveCell.Offset(1, 0).Select
        End If
        DoEvents '
        
    Loop
    
    
Cells(4, 1).Select
Do Until ActiveCell.row = b0
line0 = ActiveCell.Value
nrow0 = ActiveCell.Offset(0, 1).Value
ncol0 = ActiveCell.Offset(0, 2).Value - 1
diff0 = b0 - 1

Set rng0 = ThisWorkbook.Sheets(k).Cells(line0 + diff0, 1).Resize(nrow0, ncol0)
diff0 = diff0 - 1
Debug.Print rng0.Address
ActiveCell.Offset(1, 0).Select
DoEvents
Loop

Next k
End Sub

核心错误点 #

• 变量未显式声明：代码中b0（标记"Latticed"所在行）、fsobj（文件系统对象）均未提前定义，默认作为变体类型赋值，若读取值为空/类型不匹配，会直接导致后续Range计算参数非法。
• Resize参数非法风险：Resize方法要求传入的行数、列数必须为正整数，代码中ncol0 = 单元格值 - 1，如果对应单元格为空、值为0或文本，会得到0、负数或者类型不匹配的值，直接触发1004错误；line0 + diff0的计算未做边界校验，若结果小于1，会引用不存在的行号，同样触发报错。
• 引用歧义问题：代码中Range("A1")未指定所属工作表，依赖当前激活的工作表作为父对象，一旦运行过程中焦点切换，会读取到错误的单元格值，导致文件路径判断、区域计算全部出错。
• 冗余Select逻辑：全程通过Select+ActiveCell移动读取值，运行效率低，且极易因为激活对象错位触发无规律报错。

修正后代码 #

' 强制所有变量必须声明，从根源避免漏定义问题
Option Explicit

Sub comptst()
    Dim line0 As Long, nrow0 As Long, ncol0 As Long, diff0 As Long
    Dim k As Integer, b0 As Long
    Dim rng0 As Range, ws As Worksheet, startCell As Range, currCell As Range
    Dim fsobj As Scripting.FileSystemObject
    
    Application.DisplayAlerts = False
    
    For k = 6 To ThisWorkbook.Sheets.Count
        Set ws = ThisWorkbook.Sheets(k)
        Set fsobj = New Scripting.FileSystemObject
        ' 明确指定A1所属工作表，避免引用歧义
        If Not fsobj.FileExists(ws.Range("A1").Value) Then
            MsgBox "File is missing on sheet index-" & k
        End If
        
        ' 直接定位"Latticed"所在行，不需要Select遍历
        Set startCell = ws.Columns(1).Find(What:="Latticed", LookIn:=xlValues, LookAt:=xlWhole)
        If startCell Is Nothing Then
            MsgBox "未找到Latticed标记，工作表：" & ws.Name
            GoTo NextSheet
        End If
        b0 = startCell.Row
        
        ' 从第4行开始遍历到b0行，直接操作单元格对象，不需要Select
        Set currCell = ws.Cells(4, 1)
        diff0 = b0 - 1
        Do Until currCell.Row = b0
            ' 读取值前先做合法性校验
            If IsNumeric(currCell.Value) Then line0 = CLng(currCell.Value) Else GoTo NextRow
            If IsNumeric(currCell.Offset(0, 1).Value) Then nrow0 = CLng(currCell.Offset(0, 1).Value) Else GoTo NextRow
            If IsNumeric(currCell.Offset(0, 2).Value) Then ncol0 = CLng(currCell.Offset(0, 2).Value) - 1 Else GoTo NextRow
            
            ' 校验Resize参数合法性
            If nrow0 <= 0 Or ncol0 <= 0 Or line0 + diff0 < 1 Then
                MsgBox "区域参数非法，行号：" & currCell.Row & "，工作表：" & ws.Name
                GoTo NextRow
            End If
            
            Set rng0 = ws.Cells(line0 + diff0, 1).Resize(nrow0, ncol0)
            Debug.Print ws.Name & " 区域地址：" & rng0.Address
            
NextRow:
            diff0 = diff0 - 1
            Set currCell = currCell.Offset(1, 0)
            DoEvents
        Loop
NextSheet:
    Next k
    
    Application.DisplayAlerts = True
    Set fsobj = Nothing
End Sub

关键修正说明 #

• 开头增加Option Explicit强制变量声明，避免漏定义导致的类型错误
• 所有单元格、Range对象都明确绑定到对应工作表ws，彻底消除依赖激活状态的引用歧义
• 用Find方法直接定位"Latticed"标记位置，替代逐行Select遍历的低效逻辑
• 所有传入Resize的参数都提前做数值类型、合法范围校验，从根源避免参数非法触发的1004错误
• 增加异常分支判断，当工作表不存在"Latticed"标记、单元格值非法时给出明确提示，不会直接中断运行
• 代码结束时恢复DisplayAlerts设置，释放文件系统对象，避免配置残留影响Excel后续操作

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