VBA设置ListObject单元格引用其他表总计行公式时遇1004错误求助

我有多个Excel表格，其中一个是汇总表，需要从其他表格填充数据。当内容以“=”开头时，使用单元格的Formula属性赋值。我写了AddTblAccount函数（代码如下），里面加了Select语句只是用来验证选中的单元格是否正确。单元格公式是=Income[[#Totals];[AccountName]]，其中Income是工作簿里另一个工作表的表名，#Totals指表底部的汇总行，#AccountName是表中的列名。手动输入这个公式能成功，但运行代码时会出现1004错误，希望代码能让单元格正常显示汇总结果。

原代码 #

Public Function AddTblAccount(oLo As ListObject, index As Long, arr As Variant) As Boolean
  Dim listrow As listrow
  Dim cell As Variant
  Dim i As Long
  
  With oLo
    If .ShowTotals = False Then: .ShowTotals = True
    If index > 0 And index <= .Range.Rows.Count Then
      Set listrow = .ListRows.Add(index)
    ElseIf (index = 0) Or (index = .Range.Count + 1) Then
      'At start the table has a empty row, if position is 1, use this row
      Set listrow = .ListRows.Add
    Else
      MsgBox ("Index is out of range")
      AddTblAccount = False
      Exit Function
    End If
    With listrow
      For i = LBound(arr) To UBound(arr)
        cell = arr(i)
        'Check if the content is a string with a formula, start with '='
        If Left(CStr(cell), 1) = "=" Then
          .Range.Cells(1, i).Select
          .Range.Cells(1, i).Formula = cell
        Else
          .Range.Cells(1, i).Select
          .Range.Cells(1, i).Value = cell
        End If
      Next i
    End With
  End With
  AddTblAccount = True
End Function

错误原因与修正要点 #

1. 公式分隔符不匹配：中文Excel环境下，公式的区域分隔符是逗号（,），但代码中用的是分号（;）。手动输入时Excel会自动适配，但VBA直接赋值会触发语法错误，这是1004错误的核心原因。
2. 冗余的Select语句：VBA操作单元格无需先选中，Select语句不仅无用，还会降低代码效率、引发不必要的交互问题。
3. 索引判断逻辑错误：原代码用.Range.Count（表内总单元格数）判断行数，应该用.ListRows.Count（表的有效行数），避免索引越界。

修正后的代码 #

Public Function AddTblAccount(oLo As ListObject, index As Long, arr As Variant) As Boolean
    Dim listrow As ListRow
    Dim cell As Variant
    Dim i As Long
    
    With oLo
        ' 确保汇总行显示
        If Not .ShowTotals Then .ShowTotals = True
        
        ' 修正行插入索引判断逻辑
        If index > 0 And index <= .ListRows.Count + 1 Then
            Set listrow = .ListRows.Add(index)
        Else
            MsgBox "Index超出范围"
            AddTblAccount = False
            Exit Function
        End If
        
        With listrow
            For i = LBound(arr) To UBound(arr)
                cell = arr(i)
                ' 判断是否为公式并处理分隔符
                If Left(CStr(cell), 1) = "=" Then
                    .Range.Cells(1, i).Formula = Replace(CStr(cell), ";", ",")
                Else
                    .Range.Cells(1, i).Value = cell
                End If
            Next i
        End With
    End With
    AddTblAccount = True
End Function

关键说明 #

• 公式分隔符替换：通过Replace函数把公式中的;替换为,，适配中文Excel的语法要求。
• 移除Select语句：直接对单元格的Formula或Value属性赋值，代码更高效稳定。
• 索引逻辑修正：用.ListRows.Count准确判断表的行数，确保插入位置的正确性。

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