在公式中一般會出現對儲存格位址的引用, 引用的方式有絕對引用(A1方式)和相對參照(R1C1)方式, 同樣的, 在用VBA輸入公式時, 也會有兩種方式。
1.1 輸入A1格式的公式
向儲存格輸入公式, 實際上就是輸入公式的字串。 這時採用Range的value屬性或Formula屬性均可。
.Range("E11").Formula = "=sum(E2:E10)"
1.2 輸入R1C1公式
使用R1C1格式向儲存格輸入公式, 實際上是錄製巨集的方式。 是一種位址相對參照的方式, 這裡的相對參照的基準位址就是公式所在的位址, 以此地址為基準, 偏移行R和列C得到相對參照位置。 有些人可能喜歡這種方式, 不過這種方式不像A1方式那樣直觀和容易理解。
(如果不是很熟悉VBA的語法格式, 可以採取錄製巨集的方式去得到代碼;如果覺得R1C1格式不習慣, 可以在在Excel中設置公式的引用方式為A1(在Excel選項的公式項中設置), 輸入公式後再複製到VBA代碼中。 )
.Range("G11").FormulaR1C1 = "=SUM(R[-9]C:R[-1]C)";
[]中的資料代表儲存格相對于目前的儲存格的行列偏移。
1.3 輸入陣列公式
向儲存格或儲存格區域輸入陣列公式, 需要使用FormulaArray屬性。
Range("E1:E11")FormulaArray = "=C2:C10*D2:D10"
2 使用函數2.1 VBA引用Excel內置函數
.Range("A16") = "=find(""."",A13,1)"
.Range("A18") = Application.WorksheetFunction.Find(".", fname, 1)
(在VBE的代碼視窗中輸入Application.WorksheetFunction.可以得到引用Excel內置函數的提示;)
2.2 VBA引用VBA內置函數
.Range("A14") = InStr(ActiveWorkbook.Name, ".")
(在VBE的代碼視窗中輸入VBA., 可以得到內置函數的提示;)
2.3 自訂函數
函數過程的標誌以Function開頭, 定義好以後, 可以像調用Excel已定義的函數一樣通過等於號去使用它。
如:
Function 及格率(cell As Range)
及格率=WorksheetFunction.CountIf(cell, ">=60") / WorksheetFunction.CountIf(cell, ">0")
及格率=Format(及格率, "0.00%")
End Function
自訂函數可以必須有返回值,
Function過程通常三種方式調用:
(1)在工作表中通過公式調用:像內建函式一樣在工作表中使用, 也可以與其它函數嵌套。 使用方法如下:
公式→插入函數→類別:使用者定義→選擇函數;
(2)在VBA代碼中被其它程序呼叫。
(3)遞迴:Function過程和Sub一樣可以實現遞迴。 如果不是刻意地、有計劃地進入遞迴狀態, 可以會造成資源耗盡或者溢出堆疊空間。 例如下例函數的調用:
在VBA語言中, 也有預定義一些函數, 與Excel預定義的相同功能的函數有細微區別)。
3 綜合實例有如下工作表:
運行以下VBA:
Sub formulaTest()
With ActiveWorkbook.Sheets("使用公式和函數")
For i = 2 To 10
.Range("E" & i).Value = "=sum(A" & i & ":D" & i & "2)"
Next
.Range("E11").Formula = "=sum(E2:E10)"
.Range("G11").FormulaR1C1 = "=SUM(R[-9]C:R[-1]C)"
.Range("G2:G10").FormulaArray = "=E2:E10*F2:F10"
.Range("A13") = ActiveWorkbook.Name
Dim fname As String
fname = Range("A13").Value
.Range("A14") = InStr(ActiveWorkbook.Name, ".")
.Range("A15") = InStr([A13], ".")
.Range("A16") = "=find(""."",A13,1)"
.Range("A17") = InStr(fname, ".")
.Range("A18") = Application.WorksheetFunction.Find(".", fname, 1)
.Range("A19") = Application.WorksheetFunction.Find(".", [A13], 1)
'[A13]相當於range("A13")
End With
'Instr([start,]string1,string2[,compare])
End Sub
效果如下:
對應的公式如下:
-End-