Q：我想使用VBA代碼在單元格中輸入數(shù)組公式，如何實現(xiàn)？
A：Range對象提供了一個FormulaArray屬性，可以用來設(shè)置或者返回單元格區(qū)域中的數(shù)組公式，也就是說，在工作表單元格中輸入完后需要按Ctrl+Shift+Enter組合鍵才能最終完成的公式。
如下所示，要求工作表Sheet2中所列出的水果總的銷售金額，即分別使用各種水果的單價乘以各自的銷量后的和。
其原因可能是有以下幾種。
原因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中生成當(dāng)月的日歷。
正如本文一開始所的，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ū)域中每個單元格的公式。
