首页 > Excel专区 > Excel教程 >

excel VBA代码怎么在单元格中输入数组公式?

Excel教程 2021-12-31 21:42:50

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型数组,数组中的每个元素表示区域中每个单元格的公式。


标签: Excel常用函数excel常见问题excel技巧excel教程

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.