Excel VBA按工作表位置引用Range.Value触发对象定义错误如何解决

错误根源 #

触发对象定义错误的核心原因如下：

• 语法拼写错误：VBA中工作表集合的正确名称是Worksheets，你所有引用位置工作表的代码都写成了单数Worksheet，系统找不到对应对象直接报错
• 错误捕获逻辑错误：你全局开启了On Error Resume Next没有关闭，前面移动工作表、激活工作簿等环节的错误被直接掩盖，无法提前定位问题
• 未明确指定对象归属：类似wbAll.Sheets(Sheets.Count)的写法中，Sheets.Count没有指定归属为wbAll，会默认取当前活动工作簿的工作表数量，导致工作表移动位置错误；循环结束后尝试激活的Workbooks("Book" & t)也不存在，t循环结束时的值是101，且原工作簿的工作表全部移走后会自动关闭，无法激活
• 硬编码逻辑风险：你固定引用第2到31位工作表，一旦移动过来的工作表总数不足31个，会直接触发下标越界错误

修正后的代码 #

Dim wb As Workbook, wbAll As Workbook
Dim ws As Worksheet
Dim t As Integer

Set wbAll = Workbooks.Add
' 仅在查找工作簿时临时关闭错误捕获
On Error Resume Next
For t = 1 To 100         
    Set wb = Workbooks("Book" & t)
    If Err.Number = 0 Then ' 仅当找到对应工作簿时才执行移动
        For Each ws In wb.Sheets
            ' 明确指定工作表归属为wbAll
            ws.Move after:=wbAll.Sheets(wbAll.Sheets.Count)
        Next
    End If
    Err.Clear
Next t
' 恢复错误捕获，方便后续调试
On Error GoTo 0

' 直接操作wbAll，不需要激活选中，避免不必要的报错
With wbAll.Sheets("Sheet1")
    .Range("c17:E46").ClearContents
    
    ' 遍历所有工作表填数据，不需要硬编码行数
    Dim x As Integer, i As Integer
    x = 17
    For i = 2 To wbAll.Worksheets.Count
        ' 工作表名称填在B列，避免和C列的成本数据冲突，不需要可删除下一行
        .Cells(x, 2) = wbAll.Worksheets(i).Name
        ' 拉取成本到C列
        .Cells(x, 3) = wbAll.Worksheets(i).Range("Q118").Value
        ' 拉取WBS到D列
        .Cells(x, 4) = wbAll.Worksheets(i).Range("D10").Value
        x = x + 1
        ' 最多填到46行，超出就停止
        If x > 46 Then Exit For
    Next i
    
    ' 填充公共字段，判断存在第2个工作表再取值，避免报错
    If wbAll.Worksheets.Count >= 2 Then
        .Range("C10").Value = wbAll.Worksheets(2).Range("D4").Value
        .Range("C12").Value = wbAll.Worksheets(2).Range("D5").Value
    End If
End With

优化说明 #

• 去掉了所有不必要的激活、选中操作，直接通过工作簿对象操作，稳定性更高
• 所有对象都明确指定了归属，避免默认活动对象带来的不确定错误
• 取消了硬编码的30行重复赋值逻辑，通过循环遍历自动拉取数据，自动适配实际移动的工作表数量
• 临时关闭错误捕获用完即恢复，方便定位后续代码的问题
• 增加了工作表数量判断，避免工作表不足时的下标越界错误

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