excel 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中的最大值。

下面的4段代码都可实现。

代码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;2;3;4;5}*7+{1,2,3,4,5,6,7}⑴),””””,”& _

“X_X_X())”

theFormulaPart2 =”DATE(YEAR(NOW()),MONTH(NOW()),1)-” & _

“(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))⑴)+” & _

“{ ;1;2;3;4;5}*7+{1,2,3,4,5,6,7}⑴)”

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。

在上述所有3种情形中，Formula属性返回Variant型数组，数组中的每一个元素表示区域中每一个单元格的公式。