Excel VBA：循环跨数组操作表格时遇运行时1004错误

问题背景 #

创建了两个数组，一个存储需修改的表格名称，另一个存储表格所在的工作表名称（这些工作表处于隐藏状态）。需求是循环遍历表格列表：

• 将对应工作表设为可见
• 把表格重置为仅表头加一行带公式的数据行
• 重新隐藏工作表

但使用循环变量item指代表格名称时触发运行时1004应用程序定义或对象定义错误，硬编码具体表名时代码可正常运行。

错误原因 #

Application.Range(item)是基于当前活动工作表查找指定名称的范围，即使已经将目标工作表设为可见，若未切换到该工作表，Excel无法在当前活动表中找到目标表格，从而触发错误。硬编码时可能刚好当前活动工作表是目标表，所以能正常执行。

另外，重复调用Application.Range(item).ListObject不仅效率低，还会增加出错概率。

修复后的代码 #

Sub ReturnTableToOneRow()
    Dim tblNames(1 To 16) As String
    Dim shNames(1 To 16) As String
    Dim i As Integer
    Dim ws As Worksheet
    Dim targetTbl As ListObject
    Dim rowCount As Integer
         
    ' 初始化表格名称数组
    tblNames(1) = "Calculator"
    ' ... 省略其他表名初始化
    tblNames(16) = "PC"
    
    ' 初始化工作表名称数组
    shNames(1) = "Calculator"
    ' ... 省略其他工作表名初始化
    shNames(16) = "Reimbursements"

    ' 用索引循环，避免shNum变量维护的麻烦
    For i = 1 To UBound(tblNames)
        Set ws = ThisWorkbook.Worksheets(shNames(i))
        ws.Visible = xlSheetVisible
        
        ' 直接通过工作表对象获取目标表格，上下文明确
        Set targetTbl = ws.ListObjects(tblNames(i))
        
        ' 处理表格数据行：如果数据行数量大于1，删除多余行
        If Not targetTbl.DataBodyRange Is Nothing Then
            rowCount = targetTbl.DataBodyRange.Rows.Count
            If rowCount > 1 Then
                ' 从第2行数据开始删除，保留第1行
                targetTbl.DataBodyRange.Offset(1).Resize(rowCount - 1).Delete
            End If
        End If
        
        ws.Visible = xlSheetHidden
    Next i
End Sub

关键优化点 #

• 明确上下文：通过ws.ListObjects(tblNames(i))直接从目标工作表获取表格对象，避免依赖当前活动工作表，彻底解决查找不到表格的问题。
• 空值判断：增加If Not targetTbl.DataBodyRange Is Nothing判断，防止表格无数据行时触发错误。
• 简化代码：用索引循环替代For Each+shNum的方式，逻辑更清晰，避免变量维护错误。
• 精简操作：直接对DataBodyRange进行偏移和Resize，减少重复调用，提升代码效率。

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