VBA设置Excel数组公式时FormulaArray自动转为相对引用问题

嘿，我之前也碰到过这个坑！你的问题核心是VBA的FormulaArray属性在处理引用时的自动转换问题——本来你写的是A1样式的绝对引用，结果它自动转成了R1C1的相对引用，导致公式范围跟着单元格位置变化了。

问题原因 #

通常这种情况要么是你最近不小心把Excel的默认引用样式改成了R1C1（在「文件>选项>公式」里能看到），要么是FormulaArray在赋值时，会根据当前工作表的引用设置自动转换格式，而且如果你的公式里没加绝对引用符号$，哪怕是A1样式也会变成相对引用。

两种解决办法 #

方法1：强制用A1样式+绝对引用

先临时切换到A1引用样式，给公式加上绝对引用符号，用完再恢复原来的设置，这样能保证公式完全按照你想要的A1格式写入：

' 先保存用户原来的引用样式，避免改动用户设置
Dim originalRefStyle As XlReferenceStyle
originalRefStyle = Application.ReferenceStyle

' 切换到A1引用样式
Application.ReferenceStyle = xlA1

For u = 1 To Row2
    ' 给引用加上$变成绝对引用，防止相对偏移
    Sheets("Testa").Cells(u + 1, 14).FormulaArray = "=SUM(IF($B$2:$B$2000=" & CStr(u) & ",$F$2:$H$2000,0))"
Next
For v = 1 To Row
    Sheets("Testa").Cells(v + 1, 18).FormulaArray = "=SUM(IF($A$2:$A$2000=" & CStr(v) & ",$F$2:$H$2000,0))"
Next

' 恢复原来的引用样式
Application.ReferenceStyle = originalRefStyle

方法2：直接用R1C1格式写绝对引用

如果不想折腾引用样式切换，直接用R1C1的绝对引用格式写公式，这样不管Excel设置是什么，引用范围都是固定的：

For u = 1 To Row2
    ' R2C2:R2000C2 对应A1的$B$2:$B$2000，R2C6:R2000C8对应$F$2:$H$2000
    Sheets("Testa").Cells(u + 1, 14).FormulaArray = "=SUM(IF(R2C2:R2000C2=" & CStr(u) & ",R2C6:R2000C8,0))"
Next
For v = 1 To Row
    Sheets("Testa").Cells(v + 1, 18).FormulaArray = "=SUM(IF(R2C1:R2000C1=" & CStr(v) & ",R2C6:R2000C8,0))"
Next

为什么之前正常现在异常？ #

大概率是最近Excel的引用样式设置被改动了（可能是误操作，或者打开了其他用R1C1格式的工作簿），导致FormulaArray的转换逻辑变了。两种方法都能解决这个问题，选你顺手的就行～

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