如何用VBA将一维数组批量写入Excel ListObject表格新行

直接循环写入单个单元格是效率低下的核心原因——Excel对单元格的单次读写操作开销极大。最优方案是先把所有公式预处理到二维数组中，再一次性赋值给新行的单元格区域，将多次读写操作压缩为1次，大幅提升性能。

优化后的代码 #

Dim MyTable As ListObject
Set MyTable = Ws.ListObjects("TestTbl")

Dim formulaArr() As Variant
' 初始化1行N列的二维数组（N为表格总列数）
ReDim formulaArr(1 To 1, 1 To MyTable.ListColumns.Count)

Dim i As Integer
For i = 1 To MyTable.ListColumns.Count
    If Len(myArr(rowToCopy(s), i)) > 0 Then
        ' 生成目标公式
        Dim tempFormula As String
        tempFormula = "=" & Replace(myArr(rowToCopy(s), i), "xxx", "SheetName")
        
        ' 可选：提前验证公式有效性，避免写入后触发报错
        On Error Resume Next
        Dim testVal As Variant
        testVal = Application.Evaluate(tempFormula)
        If Err.Number = 0 Then
            formulaArr(1, i) = tempFormula
        Else
            formulaArr(1, i) = "=#N/A"
        End If
        On Error GoTo 0
    Else
        formulaArr(1, i) = "=#N/A"
    End If
Next i

' 添加新行并一次性写入数组内容
Dim NewRow As ListRow
Set NewRow = MyTable.ListRows.Add(AlwaysInsert:=True)
NewRow.Range.Formula = formulaArr

关键说明 #

• 二维数组的必要性：Excel的Range.Formula属性对一维数组默认按列填充，而我们需要单行数据，因此必须创建1行×N列的二维数组，确保数据按行写入目标区域。
• 效率提升逻辑：数组操作完全在内存中完成，仅最后一步与Excel交互，把原来的144次单元格读写压缩成1次，性能提升效果显著。
• 公式有效性保障：通过Application.Evaluate提前验证公式合法性，避免无效公式导致的"内存不足"或其他运行时错误；若不需要严格验证，可直接去掉Evaluate相关代码。
• 错误处理优化：把错误检查放在数组预处理阶段，避免在单元格写入时触发错误，进一步降低Excel的异常处理开销。

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