Excel VBA中ListRows.Add方法报错崩溃及表格写入问题求解

这个报错90%以上都是表格范围扩展受阻导致的，常见诱因按出现概率排序：

• 表格正下方紧邻的第一行（也就是表格插入新行必须占用的行）存在非空值、合并单元格、锁定单元格（工作表开启保护时）、残留的单元格格式/批注/数据验证规则。手动插入行时Excel会弹出扩展确认提示，但VBA调用时没有交互兜底，直接触发底层内存错误导致程序崩溃。
• 表格绑定了只读/同步类外部数据源，比如设置了加载保护的Power Query查询、SharePoint在线列表同步连接、和数据透视表缓存强绑定的源表。这类ListObject的行结构修改会被数据源拦截，而ListColumns.Add不触发数据源行级校验，所以可以正常运行。
• 表格设置了公式自动填充，但某列的公式存在循环引用、#VALUE!/#REF!类错误，插入新行时Excel自动向下填充公式触发未捕获错误，直接崩溃。
• 工作表事件冲突：如果Worksheet_Change等事件里写了操作当前表格的逻辑，插入行的动作会触发事件递归，没有加事件开关的话会直接抛出方法调用失败错误。
• 工作簿开启了共享编辑、IRM权限保护，行结构修改权限被全局拦截。

快速排查方式：手动选中表格最后一个数据单元格按Tab键，尝试手动在末尾新增行，如果手动操作也报错，优先检查表格下方相邻行的内容/格式冲突；如果手动操作正常，优先检查事件递归、对象残留类代码问题。

首先纠正误区：DataBodyRange完全支持写入操作，你看到的文档描述是指不能把该属性直接赋值为其他Range对象，对其覆盖范围内的单元格赋值、批量写数组都是原生支持的。
常用的三种写入方案，按需选用：

方案1：绑定新行对象按列写入（最稳妥，推荐日常使用） #

ListRows.Add本身会返回新增行的ListRow对象，直接调用该对象的Range属性即可写入，不需要自己计算行号：

Dim MyTable As ListObject
Dim newRow As ListRow

Set MyTable = Sheets("Sheet1").ListObjects("Table1")
' 在表格末尾新增行，同时拿到新行对象
Set newRow = MyTable.ListRows.Add(Position:=MyTable.ListRows.Count + 1)

' 按列索引写入（列号从1开始，对应表格第一列）
newRow.Range(1) = "第一列内容"
newRow.Range(2) = "第二列内容"
newRow.Range(3) = 200

' 更稳妥的写法：按表头名写入，调整列顺序也不会写错
newRow.Range(MyTable.ListColumns("姓名").Index) = "张三"
newRow.Range(MyTable.ListColumns("年龄").Index) = 28

方案2：通过DataBodyRange批量写入（适合大数据量场景） #

新增行后直接定位到DataBodyRange的最后一行，支持数组一次性写入整行，速度比逐个单元格写快10~100倍：

Dim MyTable As ListObject
Dim lastRowIdx As Long
Set MyTable = Sheets("Sheet1").ListObjects("Table1")

MyTable.ListRows.Add
lastRowIdx = MyTable.DataBodyRange.Rows.Count

' 单个单元格写入
MyTable.DataBodyRange.Cells(lastRowIdx, 1) = "批量写入值1"
MyTable.DataBodyRange.Cells(lastRowIdx, 2) = "批量写入值2"

' 一维数组一次性写入整行
Dim writeData(1 To 3) As Variant
writeData(1) = "数组值1"
writeData(2) = "数组值2"
writeData(3) = 999
MyTable.DataBodyRange.Rows(lastRowIdx).Value = writeData

方案3：直接写单元格绕过ListRows.Add报错（兼容特殊场景） #

如果遇到绑定数据源等场景导致ListRows.Add完全无法使用，可以直接在表格数据区下方的空白行写入内容，Excel会自动将该行纳入表格范围：

Dim MyTable As ListObject
Set MyTable = Sheets("Sheet1").ListObjects("Table1")

' 定位到表格末尾下一行，一次性写入整行数据
With MyTable.DataBodyRange
    .Offset(.Rows.Count, 0).Resize(1, MyTable.ListColumns.Count).Value = Array("绕过方法值1", "绕过方法值2", 300)
End With

注意：如果写入时触发工作表事件导致递归报错，在写入代码前加Application.EnableEvents = False，写入完成后再改回True即可。

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