excel VBA代码如何在单元格中输入数组公式？

问：我想用VBA代码在单元格中输入数组公式。如何做到这一点？

答：A:Range对象提供了一个FormulaArray属性，可用于设置或返回单元格区域的数组公式，也就是在工作表单元格中输入后，只需按Ctrl Shift Enter即可最终完成的公式。

如下图所示，工作表2中列出的水果销售总额是必需的，即各种水果的单价乘以各自的销售量后的总和。

代码：

第2页。范围(“C7”)。FormulaArray===SUM(b 2: b5 * c 2: C5)"

在单元格C7中输入数组公式并计算结果，如下所示。

上面显示了VBA代码在单个单元格中输入数组公式。如果要在多个单元格中输入数组公式怎么办？如以下代码所示：

第3页。范围(“B1:B6”)。公式数组="=A1:A6=" " Excel " " "

确定工作表第3页的单元格区域A1:A6中的值是否为“Excel”，如果是，则返回真，否则返回假。运行代码后的结果如下。

如果每个单元格中要输入的数组公式不一样怎么办？以下是colinlegg.wordpress.com提供的一个例子。

当e列中的值等于其对应的a列单元格中的值或f列中的值等于其对应的b列单元格中的值时，在g列中输入最大值，如下所示。

可以实现以下四段代码。

代码1:

子测试()

像龙一样暗淡

对于r=2至5

第一张。细胞(r，3)。公式数组=_

"=MAX(IF((E $ 2: E $ 1=A " CStr(r)")($ F $ 2: F $ 1=B " CStr(r)")=1，$ G $ 2: $ G))"

下一个r

末端接头

使用循环完成单元格中数组公式的输入。

代码2:

子测试1()

带Sheet1。范围(“C2”)。公式数组=_

"=MAX(IF((($ E $ 2: $ E $ 1=A2)($ F $ 2: $ F $ 1=B2))=1，$G$2:$G$1))"。范围(“C2:C5”)。向下填充

以…结尾

末端接头

代码首先在第一个单元格中输入数组公式，然后向下复制并填充公式。

代码3:

子测试2()

带Sheet1。范围(“C2”)。公式数组=_

"=MAX(IF((($ E $ 2: $ E $ 1=A2)($ F $ 2: $ F $ 1=B2))=1，$G$2:$G$1))"。范围(“C2”)。复制。范围(“C3:C5”)。粘贴特殊XL粘贴公式

以…结尾

申请。剪切复制模式=假

末端接头

代码首先在第一个单元格中输入公式，然后复制它，然后粘贴复制的公式。

代码4:

子测试3()

带Sheet1。范围(“C2:C5”)。公式=_

"=MAX(IF((($ E $ 2: $ E $ 1=A2)($ F $ 2: $ F $ 1=B2))=1，$G$2:$G$1))"。公式数组=.公式1C1

以…结尾

末端接头

使用公式数组属性时，可能会出现以下所示的错误：

原因可能如下。

1:试图修改数组单元格区域中的一些单元格。

例如，以下代码：

第3页。范围(“B1:B6”)。公式数组="=A1:A6=" " Excel " " "

第3页。范围(“B1”)。Value="excelperfect "

会导致错误。可以先判断要修改的单元格是否在数组公式区域，例如：

带Sheet3。范围(“B1:B6”)。公式数组="=A1:A6=" " Excel " " "

和。范围(“B1”)

如果。那么哈萨里

MsgBox“单元格在数组公式区”。当前日期。地址

如果…就会结束

以…结尾

以…结尾

原因2:试图在合并单元格中输入数组公式。

如果在单元格中输入数组公式，然后将其与其他单元格合并，则没有问题。但是，不能在合并单元格中输入数组公式。例如，以下代码将失败：

带Sheet3。范围(“C1:C6”)。合并。范围(“C2”)。公式数组="=A1:A6=" " Excel " " "

以…结尾

您可以首先检查要输入数组公式的单元格：

带Sheet3。范围(“C1:C6”)。合并

和。范围(“C1”)

If .MergeArea.Address=。地址然后

MsgBox“无合并单元格”

其他

MsgBox“单元格合并了，地址是：”。mergearea.address。

如果…就会结束

以…结尾

以…结尾

原因3:数组公式有语法错误，如缺少参数或参数无效。

带座椅3。范围(“F1”)

缺少“SUM”函数的参数。FormulaArray="=SUM()"

' SUMIF函数的第一个和第三个参数不能接受数组。FormulaArray===SUMIF((a 1: a2=1)*(b 1: B2)，B1，C1:C2)"

以…结尾

原因4:数组公式超过255个字符。

VBA帮助指示公式数组属性值不能超过255个字符。如果公式的字符超过255个字符，可以使用DailyDoseOfExcel引入的技巧，并使用Replace方法：

Public SubLongArrayFormula()

将窗体部件1调暗为字符串

将窗体部件2调暗为字符串

formulapart 1==IF(MONTH(DATE(YEAR(NOW())、MONTH(NOW())、1))-" _

“月(日期(年(现在))、月(现在))、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())"

FormulaPart2=“日期(年(现在))、月(现在())、1)--_

"(WEEKDAY(DATE(YEAR(NOW())、MONTH(NOW())、1))-1) " _

"{ ;1;2;3;4;5}*7 {1,2,3,4,5,6,7}-1)"

用活性纸。范围(“E2:K7”)。公式数组=FOrmulapart 1。替换“X_X_X())”，格式第2部分。numberformat="m "月"日"。

以…结尾

末端接头

上述程序将在单元格区域E2:K7生成当月日历。

正如本文开头提到的，FormulaArray属性也可以返回单元格中的公式。

如果要从单个单元格返回公式，则无论单元格是否包含数组公式，“公式”属性和“公式数组”属性都将返回相同的结果。但是，当公式属性和公式数组属性应用于连续的多单元格区域时，它们会返回不同的结果。

如果单元格区域包含数组公式，即多个单元格中有一个数组公式，则FormulaArray属性返回该公式。

如果单元格区域不是数组区域，但所有单元格都包含相同的公式，则公式数组属性也会返回通用公式。

如果单元格区域不是数组区域，并且包含不同的公式，则公式数组属性返回空值。

在这三种情况下，“公式”属性都返回一个变量数组，数组中的每个元素代表区域中每个单元格的公式。