VBA宏无法识别工作簿中部分工作表的问题排查与解决

错误原因 #

运行时错误-2147319767 (80028029)对应无效对象引用或类型不匹配，结合你的场景，核心诱因有两点：

• 冗余对象引用的潜在冲突：你已经通过For Each w In ThisWorkbook.Worksheets拿到了合法的Worksheet对象，却额外通过ThisWorkbook.Sheets(w.Name)重新定位工作表。若目标工作表名称包含特殊字符（如空格、非西欧字符），或Excel内部对象索引出现临时异常，会导致无法正确匹配到目标工作表。
• 工作表类型异常：尽管Worksheets集合仅包含普通工作表，但极少数情况下，工作表的内部类型标记会出现错误（比如曾被转换为图表工作表后恢复，却残留了错误标记），导致通过Sheets引用时触发类型不匹配。

解决方案 #

1. 简化代码（优先推荐） #

直接使用遍历得到的w对象操作单元格，彻底避免冗余引用带来的问题：

Sub Test()
    Dim w As Worksheet
    For Each w In ThisWorkbook.Worksheets
        w.Cells(1, 1).Value = "TEST"
    Next
End Sub

2. 排查目标工作表异常 #

如果修改代码后仍报错，针对第3个工作表做以下检查：

• 右键工作表标签 → 「查看代码」，检查是否存在工作表级事件代码（如Worksheet_Change），这类代码可能在写入单元格时触发冲突，可临时注释事件代码后重试。
• 将该工作表复制到新空白工作簿，测试是否仍出现错误，判断是否是工作表本身损坏。

3. 修复Excel对象模型异常 #

关闭所有Excel文件，重启软件；若问题依旧，按以下步骤操作：

1. 打开Excel，点击「文件」→「选项」→「信任中心」→「信任中心设置」
2. 选择「宏设置」，勾选「信任对VBA项目对象模型的访问」
3. 重启Excel后重试代码

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