excel VBA代碼怎么在單元格中輸入數(shù)組公式？

excel VBA代碼怎么在單元格中輸入數(shù)組公式？

Q：我想使用VBA代碼在單元格中輸入數(shù)組公式，如何實現(xiàn)？

A：Range對象提供了一個FormulaArray屬性，可以用來設(shè)置或者返回單元格區(qū)域中的數(shù)組公式，也就是說，在工作表單元格中輸入完后需要按Ctrl+Shift+Enter組合鍵才能最終完成的公式。

如下所示，要求工作表Sheet2中所列出的水果總的銷售金額，即分別使用各種水果的單價乘以各自的銷量后的和。

代碼：

Sheet2.Range(“C7”).FormulaArray= “=SUM(B2:B5*C2:C5)”

在單元格C7中輸入數(shù)組公式并計算結(jié)果，如下所示。

上面演示了VBA代碼在單個單元格中輸入數(shù)組公式，如果要在多個單元格中輸入數(shù)組公式呢？如下面的代碼所示：

Sheet3.Range(“B1:B6”).FormulaArray= “=A1:A6=”” Excel”””

判斷工作表Sheet3的單元格區(qū)域A1:A6中的值是否為“ Excel”，如果是則返回TRUE，否則為FALSE。運行代碼后的結(jié)果如下所示。

如果需要輸入的數(shù)組公式在每個單元格中都不同呢？下面以colinlegg.wordpress.com中提供的示例來說明。

如下所示，在列C中輸入列E中的值等于其對應(yīng)的列A單元格中的值或者列F中的值等于其對應(yīng)的列B單元格中的值時列G中的最大值。

下面的四段代碼均可實現(xiàn)。

代碼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

使用循環(huán)來完成數(shù)組公式在單元格中的輸入。

代碼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

代碼先在第一個單元格中輸入數(shù)組公式，然后復(fù)制并向下填充公式。

代碼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

代碼先在第一個單元格中輸入公式，然后將其復(fù)制，接著粘貼所復(fù)制的公式。

代碼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屬性時可能會發(fā)生下和所示的錯誤：

其原因可能是有以下幾種。

原因1：試圖修改數(shù)組單元格區(qū)域中的某些單元格

例如下面的代碼：

Sheet3.Range(“B1:B6”).FormulaArray= “=A1:A6=”” Excel”””

Sheet3.Range(“B1”).Value= “excelperfect”

會導(dǎo)致錯誤。可以先判斷要修改的單元格是否處于數(shù)組公式區(qū)域，例如：

With Sheet3

.Range(“B1:B6″).FormulaArray =”=A1:A6=”” Excel”””

With .Range(“B1”)

If .HasArray Then

MsgBox “單元格處于數(shù)組公式區(qū)域” & .CurrentArray.Address

End If

End With

End With

原因2：試圖在合并單元格中輸入數(shù)組公式

如果先在單元格中輸入數(shù)組公式然后再和其他單元格合并，這沒有問題。然而，不能夠在已經(jīng)合并的單元格中輸入數(shù)組公式。例如，下面的代碼將失敗：

With Sheet3

.Range(“C1:C6”).Merge

.Range(“C2″).FormulaArray =”=A1:A6=”” Excel”””

End With

可以先對要輸入數(shù)組公式的單元格進行檢查：

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：數(shù)組公式存在語法錯誤，例如參數(shù)缺失或無效參數(shù)

WithSheet3.Range(“F1”)

‘SUM函數(shù)參數(shù)缺失

.FormulaArray = “=SUM()”

‘SUMIF函數(shù)的第1個參數(shù)和第3個參數(shù)不能接受數(shù)組

.FormulaArray = “=SUMIF((A1:A2 =1)*(B1:B2 ),B1,C1:C2 )”

End With

原因4：數(shù)組公式超過了255個字符

VBA幫助中指出，F(xiàn)ormulaArray屬性的值不能超過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

上述程序?qū)⒃趩卧駞^(qū)域E2:K7中生成當月的日歷。

正如本文一開始所的，F(xiàn)ormulaArray屬性還可以返回單元格中的公式。

如果想要從單個單元格中返回公式，那么無論單元格中是否包含數(shù)組公式，F(xiàn)ormula屬性和FormulaArray屬性都會返回相同的結(jié)果。然而，F(xiàn)ormula屬性和FormulaArray屬性應(yīng)用于連續(xù)的、多單元格區(qū)域時返回不同的結(jié)果。

如果單元格區(qū)域中含有數(shù)組公式，即多個單元格中為一個數(shù)組公式，那么FormulaArray屬性返回該公式。

如果單元格區(qū)域不是數(shù)組區(qū)域但所有單元格都包含相同的公式，那么FormulaArray屬性也返回該通用公式。

如果單元格區(qū)域不是數(shù)組區(qū)域且包含的公式不相同，那么FormulaArray屬性返回Null。

在上述所有三種情形中，F(xiàn)ormula屬性返回Variant型數(shù)組，數(shù)組中的每個元素表示區(qū)域中每個單元格的公式。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔相應(yīng)法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔相應(yīng)法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。