Excel VBA不同坐标等尺寸区域赋值公式报1004错误如何解决

我需要批量合并大量用户提交的Excel模板内容。
最初做数据合并时，我采用「粘贴公式」的方式复制内容，但该方式粘贴的公式会携带源工作簿引用，例如公式显示为='[Workbook1.xlsx]Sheet1'!A1，而非预期的=Sheet1!A1，无法满足需求。
之后我调整实现方案，尝试直接为命名为「target」的合并工作簿指定区域设置Formula属性，令其等于源工作簿中等尺寸对应区域的公式。
测试发现：当两个区域的坐标完全一致时，操作可正常执行；当两个区域尺寸相同但坐标不一致时，操作会抛出错误，示例代码如下：

• 坐标一致、无报错的代码：

Target.Range(Cells(1,1).address,Cells(1,10).address).formula = Source.Range(cells(1,1).address,Cells(1,10).address).formula    ' ---> 正常运行

• 坐标不一致、触发错误的代码：

Target.Range(Cells(2,1).address,Cells(2,10).address).formula = Source.Range(cells(1,1).address,Cells(1,10).address).formula    ' ---> 报错

我编写了实现该功能的完整函数，函数返回值为Double类型，用于记录目标工作簿中下一个空行的行号，代码如下：

Public Function copy_formulas(ByVal source As Worksheet, source_row As Integer, _ 
    source_start_col As Integer, data_width As Integer, ByVal target As Worksheet, target_start_row _
    As Integer, target_start_col As Integer) As Double
    
    '逐行复制源工作表的**公式**，直到源表首列内容为空时停止
    Do While source.Cells(source_row, source_start_col) <> ""
    
        target.Range(Cells(target_start_row, target_start_col).Address, Cells(target_start_row, _
        target_start_col + data_width).Address).Formula = source.Range(Cells(source_row, _
        source_start_col).Address, Cells(source_row, source_start_col + data_width).Address).Formula
    
        '源表、目标表行计数器同步递增
        source_row = source_row + 1
        target_start_row = target_start_row + 1
    
    Loop
    
    copy_formulas = target_start_row
    
End Function

运行代码时触发的错误提示如下：

Run-time error '1004': Application-defined or object-defined error
（运行时错误“1004”：应用程序定义或对象定义错误）

业务场景中一共用到两类公式：

• 数组公式：用于规避Excel表格不支持合并单元格的限制，可从Table4中拉取风险登记表中对应风险条目的所有关联缓解措施信息，适配单风险对应多缓解措施的场景，公式内容如下：

={IFERROR(LEFT(CONCAT(IF(Table4[@[Risk ID]]=[@[Risk ID]],"-"&Table4[@[Mitigating Action ID]]&" ","")),LEN(CONCAT(IF(Table4[@[Risk ID]]=[@[Risk ID]],"-"&Table4[@[Mitigating Action ID]]&" ","")))-1),"")}

• 普通计算公式：用于计算风险得分，通过VLOOKUP分别匹配可能性、严重度对应系数后相乘得到结果，公式内容如下：

=IFERROR(VLOOKUP([@[Likelihood ]],Table6,4,FALSE)*VLOOKUP([@[Severity ]],Table6,4,FALSE),"")

触发1004错误的核心原因有三点：

1. 代码中所有Cells对象没有显式指定所属工作表，默认指向当前激活的工作表，跨工作表操作时很容易出现Range所属对象不匹配的问题。
2. VBA中直接对多单元格区域批量赋值Formula属性时，要求源区域和目标区域的行列偏移量完全一致，否则公式相对引用、结构化引用的自动适配逻辑会失效，直接抛出错误。
3. 场景中用到了CSE数组公式，普通的Formula属性无法正确写入这类公式，必须使用FormulaArray属性赋值。

修复后的可运行代码如下：

Public Function copy_formulas(ByVal source As Worksheet, source_row As Integer, _
    source_start_col As Integer, data_width As Integer, ByVal target As Worksheet, target_start_row _
    As Integer, target_start_col As Integer) As Double
    
    Dim sourceRng As Range, targetRng As Range
    Dim cellIndex As Integer
    '关闭屏幕更新提升大区域复制速度
    Application.ScreenUpdating = False
    
    '逐行复制直到源表首列为空
    Do While source.Cells(source_row, source_start_col) <> ""
        '显式绑定源、目标区域所属的工作表，避免对象指向错误
        Set sourceRng = source.Range(source.Cells(source_row, source_start_col), _
            source.Cells(source_row, source_start_col + data_width))
        Set targetRng = target.Range(target.Cells(target_start_row, target_start_col), _
            target.Cells(target_start_row, target_start_col + data_width))
        
        '逐单元格写入公式，不受区域坐标偏移影响
        For cellIndex = 1 To sourceRng.Cells.Count
            '识别数组公式，调用对应属性写入
            If Left(sourceRng.Cells(1, cellIndex).FormulaArray, 2) = "={" Then
                targetRng.Cells(1, cellIndex).FormulaArray = sourceRng.Cells(1, cellIndex).FormulaArray
            Else
                targetRng.Cells(1, cellIndex).Formula = sourceRng.Cells(1, cellIndex).Formula
            End If
        Next cellIndex
        
        '行计数器递增
        source_row = source_row + 1
        target_start_row = target_start_row + 1
    Loop
    
    '恢复屏幕更新
    Application.ScreenUpdating = True
    copy_formulas = target_start_row
End Function

修复说明：

• 所有Range、Cells对象都显式绑定所属工作表，不会因为激活工作表变化触发对象不匹配错误。
• 改为逐单元格写入公式，不需要源、目标区域坐标完全一致，公式内的结构化引用（[@列名]、表名引用）会自动适配目标位置，不会携带源工作簿的外部链接。
• 自动识别数组公式，通过FormulaArray属性写入，保证数组公式逻辑正常生效。
• 增加屏幕更新开关，大幅降低逐单元格写入时的界面刷新卡顿，提升大批量数据复制的运行效率。

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