VBA用DataBodyRange.Formula填充表格时覆盖前一行问题求助

这是Excel结构化表（ListObject）的默认特性导致的：Excel默认开启了表列公式自动填充功能，当你为表中某列的任意一行写入公式时，Excel会自动将该列所有行的公式同步替换为当前写入的公式，这就是你每轮循环都会覆盖之前行公式的原因，写入普通文本不会触发这个特性，只有带等号的公式会触发。

方案1：临时关闭自动填充公式特性 #

在循环前后分别控制该开关的状态，运行结束后恢复默认值避免影响其他Excel操作，修正后的代码如下：

' 先记录原有设置，关闭表公式自动填充
Dim originalAutoFillState As Boolean
originalAutoFillState = Application.AutoCorrect.AutoFillFormulasInLists
Application.AutoCorrect.AutoFillFormulasInLists = False

For i = 1 To RowCount '遍历表1的行
    Dest_row = DestTbl.ListRows.Count
    ' 处理表为空的场景，先添加第一行
    If Dest_row = 0 Then
        DestTbl.ListRows.Add
        Dest_row = 1
    End If
    Dest_column = DestTbl.Range.Columns.Count
    Source_Column = 1
    For column = 1 To Dest_column
        Cell_addr = Cells(i, Source_Column).Address
        DestTbl.DataBodyRange(Dest_row, column).Formula = "='[" & File_path & "]Sheet1'!" & Cell_addr
        Source_Column = Source_Column + 1
    Next
    ' 最后一行不需要额外添加新行
    If i <> RowCount Then
        DestTbl.ListRows.Add
    End If
Next

' 恢复原来的自动填充设置
Application.AutoCorrect.AutoFillFormulasInLists = originalAutoFillState

方案2：批量写入公式（效率更高） #

先把所有要写入的公式存入二维数组，等所有公式生成完毕后一次性写入整个表的DataBodyRange，既不会触发自动填充，运行速度也比逐单元格写入快很多，适合数据量大的场景：

Dim formulaArr As Variant
Dim colCount As Long
colCount = DestTbl.Range.Columns.Count
ReDim formulaArr(1 To RowCount, 1 To colCount)

' 先生成所有公式存入数组
For i = 1 To RowCount
    Source_Column = 1
    For column = 1 To colCount
        Cell_addr = Cells(i, Source_Column).Address
        formulaArr(i, column) = "='[" & File_path & "]Sheet1'!" & Cell_addr
        Source_Column = Source_Column + 1
    Next
Next

' 一次性给表添加足够行数，再批量写入所有公式
DestTbl.ListRows.Add Count:=RowCount
DestTbl.DataBodyRange.Formula = formulaArr

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