1 VBA在單元格中輸入公式

在公式中一般會(huì)出現(xiàn)對(duì)單元格地址的引用，引用的方式有絕對(duì)引用（A1方式）和相對(duì)引用（R1C1）方式，同樣的，在用VBA輸入公式時(shí)，也會(huì)有兩種方式。

1.1 輸入A1格式的公式

向單元格輸入公式，實(shí)際上就是輸入公式的字符串。這時(shí)采用Range的value屬性或Formula屬性均可。

.Range('E11').Formula = '=sum(E2:E10)'

1.2 輸入R1C1公式

使用R1C1格式向單元格輸入公式，實(shí)際上是錄制宏的方式。是一種地址相對(duì)引用的方式，這里的相對(duì)引用的基準(zhǔn)地址就是公式所在的地址， 以此地址為基準(zhǔn)，偏移行R和列C得到相對(duì)引用位置。有些人可能喜歡這種方式，不過這種方式不像A1方式那樣直觀和容易理解。

(如果不是很熟悉VBA的語法格式，可以采取錄制宏的方式去得到代碼；如果覺得R1C1格式不習(xí)慣，可以在在Excel中設(shè)置公式的引用方式為A1（在Excel選項(xiàng)的公式項(xiàng)中設(shè)置），輸入公式后再復(fù)制到VBA代碼中。)

.Range('G11').FormulaR1C1 = '=SUM(R[-9]C:R[-1]C)';

[]中的數(shù)據(jù)代表單元格相對(duì)于當(dāng)前單元格的行列偏移。

1.3 輸入數(shù)組公式

向單元格或單元格區(qū)域輸入數(shù)組公式，需要使用FormulaArray屬性。

Range('E1:E11')FormulaArray = '=C2:C10*D2:D10'

2 使用函數(shù)

2.1 VBA引用Excel內(nèi)置函數(shù)

.Range('A16') = '=find(''.'',A13,1)'

.Range('A18') = Application.WorksheetFunction.Find('.', fname, 1)

(在VBE的代碼窗口中輸入Application.WorksheetFunction.可以得到引用Excel內(nèi)置函數(shù)的提示；)

2.2 VBA引用VBA內(nèi)置函數(shù)

.Range('A14') = InStr(ActiveWorkbook.Name, '.')

(在VBE的代碼窗口中輸入VBA.，可以得到內(nèi)置函數(shù)的提示；)

2.3 自定義函數(shù)

函數(shù)過程的標(biāo)志以Function開頭，定義好以后，可以像調(diào)用Excel已定義的函數(shù)一樣通過等于號(hào)去使用它。

如：

Function 及格率(cell As Range)

及格率=WorksheetFunction.CountIf(cell, '>=60') / WorksheetFunction.CountIf(cell, '>0')

及格率=Format(及格率, '0.00%')

End Function

自定義函數(shù)可以必須有返回值，所以有函數(shù)體中必須至少被賦值一次，也因此在Function后跟數(shù)據(jù)類型定義；

Function過程通常三種方式調(diào)用：

（1）在工作表中通過公式調(diào)用：像內(nèi)部函數(shù)一樣在工作表中使用，也可以與其它函數(shù)嵌套。使用方法如下：

公式→插入函數(shù)→類別：用戶定義→選擇函數(shù)；

（2）在VBA代碼中被其它過程調(diào)用。

（3）遞歸：Function過程和Sub一樣可以實(shí)現(xiàn)遞歸。如果不是刻意地、有計(jì)劃地進(jìn)入遞歸狀態(tài)，可以會(huì)造成資源耗盡或者溢出堆棧空間。例如下例函數(shù)的調(diào)用：

在VBA語言中，也有預(yù)定義一些函數(shù)，與Excel預(yù)定義的相同功能的函數(shù)有細(xì)微區(qū)別）。

3 綜合實(shí)例

有如下工作表：

運(yùn)行以下VBA：

Sub formulaTest()

With ActiveWorkbook.Sheets('使用公式和函數(shù)')

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]相當(dāng)于range('A13')

End With

'Instr([start,]string1,string2[,compare])

End Sub

效果如下：

對(duì)應(yīng)的公式如下：

－End－