Q：我想使用VBA代码在单元格中输入数组公式，如何实现？
Q：我想使用VBA代码在单元格中输入数组公式，如何实现？
A：Range对象提供了一个FormulaArray属性，可以用来设置或者返回单元格区域中的数组公式，也就是说，在工作表单元格中输入完后需要按Ctrl+Shift+Enter组合键才能最终完成的公式。
如下所示，要求工作表Sheet2中所列出的水果总的销售金额，即分别使用各种水果的单价乘以各自的销量后的和。
代码：
Sheet2.Range(“C7”).FormulaArray= “=SUM(B2:B5*C2:C5)”
在单元格C7中输入数组公式并计算结果，如下所示。
上面演示了VBA代码在单个单元格中输入数组公式，如果要在多个单元格中输入数组公式呢？如下面的代码所示：
Sheet3.Range(“B1:B6”).FormulaArray= “=A1:A6=”” Excel”””
判断工作表Sheet3的单元格区域A1:A6中的值是否为“ Excel”，如果是则返回TRUE，否则为FALSE。运行代码后的结果如下所示。
如果需要输入的数组公式在每个单元格中都不同呢？下面以colinlegg.wordpress.com中提供的示例来说明。
如下所示，在列C中输入列E中的值等于其对应的列A单元格中的值或者列F中的值等于其对应的列B单元格中的值时列G中的最大值。
下面的四段代码均可实现。
代码1：
Sub test()
Dim r As Long
For r = 2 To 5
Sheet1.Cells(r, 3).FormulaArray = _
“=MAX(IF((($E$2:$E$1 =A”& CStr(r) & “)+($F$2:$F$1 =B” & CStr(r) &”))=1,$G$2:$G$1 ))”
Next r
End Sub
使用循环来完成数组公式在单元格中的输入。
代码2：
Sub test1()
With Sheet1
.Range(“C2”).FormulaArray = _
“=MAX(IF((($E$2:$E$1 =A2)+($F$2:$F$1 =B2))=1,$G$2:$G$1 ))”
.Range(“C2:C5”).FillDown
End With
End Sub
代码先在第一个单元格中输入数组公式，然后复制并向下填充公式。
代码3：
Sub test2()
With Sheet1
.Range(“C2”).FormulaArray = _
“=MAX(IF((($E$2:$E$1 =A2)+($F$2:$F$1 =B2))=1,$G$2:$G$1 ))”
.Range(“C2”).Copy
.Range(“C3:C5”).PasteSpecialxlPasteFormulas
End With
Application.CutCopyMode = False
End Sub
代码先在第一个单元格中输入公式，然后将其复制，接着粘贴所复制的公式。
代码4：
Sub test3()
With Sheet1.Range(“C2:C5”)
.Formula = _
“=MAX(IF((($E$2:$E$1 =A2)+($F$2:$F$1 =B2))=1,$G$2:$G$1 ))”
.FormulaArray = .FormulaR1C1
End With
End Sub
在使用FormulaArray属性时可能会发生下和所示的错误：
其原因可能是有以下几种。
原因1：试图修改数组单元格区域中的某些单元格
例如下面的代码：
Sheet3.Range(“B1:B6”).FormulaArray= “=A1:A6=”” Excel”””
Sheet3.Range(“B1”).Value= “excelperfect”
会导致错误。可以先判断要修改的单元格是否处于数组公式区域，例如：
With Sheet3
.Range(“B1:B6″).FormulaArray =”=A1:A6=”” Excel”””
With .Range(“B1”)
If .HasArray Then
MsgBox “单元格处于数组公式区域” & .CurrentArray.Address
End If
End With
End With
原因2：试图在合并单元格中输入数组公式
如果先在单元格中输入数组公式然后再和其他单元格合并，这没有问题。然而，不能够在已经合并的单元格中输入数组公式。例如，下面的代码将失败：
With Sheet3
.Range(“C1:C6”).Merge
.Range(“C2″).FormulaArray =”=A1:A6=”” Excel”””
End With
可以先对要输入数组公式的单元格进行检查：
With Sheet3
.Range(“C1:C6”).Merge
With .Range(“C1”)
If .MergeArea.Address = .Address Then
MsgBox “没有合并单元格”
Else
MsgBox “单元格已合并,地址为: ” & .MergeArea.Address
End If
End With
End With
原因3：数组公式存在语法错误，例如参数缺失或无效参数
WithSheet3.Range(“F1”)
‘SUM函数参数缺失
.FormulaArray = “=SUM()”
‘SUMIF函数的第1个参数和第3个参数不能接受数组
.FormulaArray = “=SUMIF((A1:A2 =1)*(B1:B2 ),B1,C1:C2 )”
End With
原因4：数组公式超过了255个字符
VBA帮助中指出，FormulaArray属性的值不能超过255个字符。若公式的字符超过255个字符，可以使用DailyDoseOfExcel介绍的技巧，使用Replace方法：
Public SubLongArrayFormula()
Dim theFormulaPart1 As String
Dim theFormulaPart2 As String
theFormulaPart1 =”=IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))-” & _
“MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)-” & _
“(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+” & _
“{ ;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),””””,”& _
“X_X_X())”
theFormulaPart2 =”DATE(YEAR(NOW()),MONTH(NOW()),1)-” & _
“(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+” & _
“{ ;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)”
With ActiveSheet.Range(“E2:K7”)
.FormulaArray = theFormulaPart1
.Replace “X_X_X())”,theFormulaPart2
.NumberFormat = “m””月””d””日”””
End With
End Sub
上述程序将在单元格区域E2:K7中生成当月的日历。
正如本文一开始所的，FormulaArray属性还可以返回单元格中的公式。
如果想要从单个单元格中返回公式，那么无论单元格中是否包含数组公式，Formula属性和FormulaArray属性都会返回相同的结果。然而，Formula属性和FormulaArray属性应用于连续的、多单元格区域时返回不同的结果。
如果单元格区域中含有数组公式，即多个单元格中为一个数组公式，那么FormulaArray属性返回该公式。
如果单元格区域不是数组区域但所有单元格都包含相同的公式，那么FormulaArray属性也返回该通用公式。
如果单元格区域不是数组区域且包含的公式不相同，那么FormulaArray属性返回Null。
在上述所有三种情形中，Formula属性返回Variant型数组，数组中的每个元素表示区域中每个单元格的公式。
