excelVBA代码怎么在单元格中输入数组公式

~

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

---

永新县17373752490： excel如何添加VBA代码? - ？
少俊奥丽： 1、在excel中可以添加VBA代码,通过打开“开发工具”,点击打开VB编辑器,进入VBA编写代码.1开发工具2VB编辑器3选择工作,双击4在代码窗中输入代码2、VBA,Visual Basic for Applications(VBA)是Visual Basic的一种宏语言,是微软...

永新县17373752490： 如何在Excel单元格中实现VBA代码？
少俊奥丽： 呵呵 我来回答咯~ 记得给我加分哦 for i=1 to 3 for j=1 to i cells(j,i)='*' next next 应该是这样的吧

永新县17373752490： Excel 请问:用VBA在单元格中如何写入公式 - ？
少俊奥丽： 两种方法,比如你要在单元格A1当中输入公式=SUM(B1:C3) 可以使用以下代码 第一种: Range(＂A1＂).FormulaR1C1 = ＂=sum(RC[1]:R[2]C[2])＂ 第二种: Range(＂A1＂) = ＂=sum(B1:C3)＂

永新县17373752490： 在EXCEL中,如何使用VBA设置单元格内指定字符串格式 - ？
少俊奥丽： Sub ss()Dim j As Longj = InStr(Range(＂A1＂), ＂格式＂)With Range(＂A1＂).Font.FontStyle = ＂正常＂.Characters(Start:=j, Length:=2).Font.FontStyle = ＂加粗＂End With End Sub 这个代码可以让你的格式两个字在任意位置 如果你A1是固定的这几个字,那么一句话搞定 Range(＂A1＂) .Characters(Start:=4, Length:=2).Font.FontStyle = ＂加粗＂

永新县17373752490： 在excel的vba中如何给单元格输入公式 - ？
少俊奥丽： Sub LF() Range(＂B12＂).Formula = ＂=if(H2=C2,＂＂＂＂,＂＂数据不一致＂＂)＂'Cells(2, 12).Value = ＂=if(H2=C2,＂＂＂＂,＂＂数据不一致＂＂)＂ End Sub

永新县17373752490： 如何用VB代码往EXCEL单元格里输入公式? - ？
少俊奥丽： 楼上的是计算,不是公式.VBA设置公式可以 Range(＂A1＂).Value = ＂=B1+B2+B3＂ 依此类推:For i = 1 To 30 Range(＂A＂ & i).Value = ＂=B＂ & 3 * i - 2 & ＂+B＂ & 3 * i - 1 & ＂+B＂ & 3 * i Next i

永新县17373752490： excel中的公式如何用VBA代码实现 - ？
少俊奥丽： 用VBA的思路如下: 1、首先可以用一个循环遍历C3、E3两列数据 2、按照你选定的公式做计算(数值或逻辑计算) 3、如需要可添加按钮、文本、用户窗体

永新县17373752490： excel vba如何用代码在单元格内插入复选框(activeX控件),求代码 - ？
少俊奥丽： 软件版本:Office2007 方法如下:1.可以通过录制宏,插入一个复选框,然后查看代码如下:2.然后修改代码成为我们想要的,ActiveSheet即工作表,我们改成Sheet1 四个数据,分别是距离顶端位置,距离左边位置,复选框长度,复选框高度(可以更改数值,看看复选框的变化,就能知道每个数据的意思),改成我们需要的,如果是在某个单元格中,可以利用range的top和left选项获得单元格的位置,如下图所示代码:3.F5执行代码,返回Excel,就可以看到插入的复选框:

永新县17373752490： EXCEL中使用VBA编程时想要把数据填入选定的单元格 - ？
少俊奥丽： Private Sub CommandButton1_Click() Selection = ComboBox1 End Sub ComboBox1是 ComboBox1.text的简化 Selection是当前选定区域

永新县17373752490： VBA在excel 表中如何将两个单元格内容写入到一个单元格 - ？
少俊奥丽： 假设要把A1/A2单元格的内容合并到B1单元格,那么输入如下代码: sub aa() range(＂B1＂)=range(＂A1＂)&range(＂A2＂) end sub