第6節(jié) 函數(shù)與公式

我們在使用Excel的時候經(jīng)常會用到很多自帶函數(shù)或自定義函數(shù)，那么當在編寫VBA的時候怎樣使用這些函數(shù)呢？

一、在單元格中輸入公式

1、用VBA在單元格中輸入普通公式

Range('d2')= '=b2*c2'

這一句就是將B2和C2的乘積賦值給D2，寫的時候不要忘記等號和雙引號！引號里面的內(nèi)容跟我們在單元格里輸入公式的方法是一樣的。

當然這種方式可以用循環(huán)來寫很多單元格。注意我們前面提到的變量和非變量要用&符號連起來，非變量部分要加雙引號。這兩個例子有兩種單元格的書寫方式，Range('A1')用于沒有變量的單元格，不能有變量，需要加雙引號；Cells(a,b)兩種方式都可以，不需要雙引號。

總體來說，這種方式就是我們在單元格中手動輸入公式，我們可以在單元格里看到公式。

2、用VBA在單元格輸入帶引號的公式

如果我們需要輸入的公式中本身就帶有引號，那么，不管是單引號還是雙引號，都要加倍。

Range('c16') = '=SUMIF(A2:A6,''b'',B2:B6)'

SumIf是我們在Excel中常用的一個函數(shù)，有條件的求和，即把符合條件的加起來，不符合的不加。

3、用VBA在單元格中輸入數(shù)組公式

Range('c9').FormulaArray= '=SUM(B2:B6*C2:C6)'

Formula是公式的意思，Array是數(shù)組的意思，.FormulaArray就是數(shù)組公式。這句代碼是B2*C2+B3*C3+……+B6*C6。關于數(shù)組，后續(xù)有專門的介紹。

以上幾個方法都是相當于在單元格中輸入各種公式的操作，我們可以在表格中看到公式，如果不想讓別人看到你的計算過程，那么就要用下面幾種方法了。

二、利用單元格公式返回值

在第一類方法的基礎上加上Evaluate()函數(shù)即可，這個單詞就是計算的意思。同樣，不要忘記引號加倍！

Range('d16') = Evaluate('=SUMIF(A2:A6,''b'',B2:B6)')

Range('d9') = Evaluate('=SUM(B2:B6*C2:C6)')

三、借用工作表函數(shù)

Excel內(nèi)置的函數(shù)就是工作表函數(shù)WorksheetFunction，它們就是我們在操作表格的時候常用到函數(shù)，在VBA中利用這些函數(shù)的時候需要用Application.WorksheetFunction。

Range('d8') = Application.WorksheetFunction.CountIf(Range('A1:A10'), 'B')

CountIf是把符合條件的計數(shù)，不符合的不計數(shù)，最終結果返回的是個數(shù)。

四、利用VBA函數(shù)

跟工作表函數(shù)對應的是VBA函數(shù)，這是VBA環(huán)境自帶的函數(shù)，在使用的時候需要加上VBA.。

Range('C20') =VBA.InStr(Range('a20'), 'E')

InStr函數(shù)返回的是指定一字符串在另一字符串中最先出現(xiàn)的位置，具體用法可以參考幫助文件。

五、編寫自定義函數(shù)

當我們遇到工作表函數(shù)和VBA函數(shù)都沒有的工作時，可以自定義自己的函數(shù)，具體的寫法跟Sub過程一樣，只不過它用的Function，下面就是個例子，它用來獲得當前工作表的名稱。

到這里，你一定會非常困惑，哪些是工作表函數(shù)，哪些是VBA函數(shù)？如果有寫好的當然執(zhí)行效率高了，也省的我們費勁去寫自己的了，這個需要我們參考幫助文件和MSDN幫助。

全部的函數(shù)說明參見下面的MSDN鏈接：

http://msdn.microsoft.com/en-us/library/bb259450(v=office.12).aspx

或者是

http://msdn.microsoft.com/en-us/library/bb225774(v=office.12).aspx

相關筆記請參閱：

Excel VBA 學習筆記4：VBA變量

Excel VBA 學習筆記3：循環(huán)語句

Excel VBA 學習筆記2：條件語句

Excel VBA 學習筆記1：語句、對象、方法、屬性