[原創(chuàng)]EXCEL函數(shù)應(yīng)用之五：VBA中調(diào)用庫函數(shù)

        熟悉EXCEL的同仁們都知道EXCEL帶有強(qiáng)大的庫函數(shù)，各種各樣，而且每個(gè)函數(shù)組合起來使用可以實(shí)現(xiàn)很多統(tǒng)計(jì)、分析或計(jì)算的目標(biāo)。更進(jìn)一步，在VBA代碼之中，也可以直接調(diào)用EXCEL的庫函數(shù)，而且這種調(diào)用有時(shí)能夠簡化VBA代碼編寫過程，比如有些通過循環(huán)尋找某個(gè)數(shù)字的功能直接在VBA中，用VLOOKUP或Hlookup就可以實(shí)現(xiàn)。下面簡要就VBA中調(diào)用庫函數(shù)的功能進(jìn)行介紹。

        VBA中使用庫函數(shù)一般包括下面幾個(gè)情況： 

一、在VBA代碼中設(shè)置單元格的公式調(diào)用函數(shù)

      比如需要給C16單元格設(shè)置一個(gè)條件求和公式，可以按如下格式設(shè)置：Range("c16") = "=SUMIF(A2:A6,""b"",B2:B6)"    。此時(shí)，在公式字符串中遇到引號就把引號加倍。如果是給某個(gè)單元格設(shè)置數(shù)組公式則，需要啟用單元格的formulaarray屬性，如：Range("c9").FormulaArray = "=SUM(B2:B6*C2:C6)"

二、在VBA代碼中通過庫函數(shù)計(jì)算數(shù)據(jù)  

     直接通過固定的公式，將結(jié)算的結(jié)果賦給某個(gè)單元格，要啟用函數(shù)evaluate（）。如將條件求和的結(jié)果賦給單元格D16的方式，如下：Range("d16") = Evaluate("=SUMIF(A2:A6,""b"",B2:B6)") '宏表函數(shù)直接把公式表達(dá)式轉(zhuǎn)化為數(shù)值。如果是數(shù)組，類似處理，比如：Range("d9") = Evaluate("=SUM(B2:B6*C2:C6)")

三、在VBA代碼中直接調(diào)用計(jì)算數(shù)據(jù)

       上述兩種調(diào)用庫函數(shù)的辦法其實(shí)沒有將函數(shù)真正作為VBA代碼執(zhí)行的一部分，而是將其放到單元各種由EXCEL自動(dòng)運(yùn)算。實(shí)際上EXCEL的表函數(shù)也可以直接在VBA中直接調(diào)用執(zhí)行，具體調(diào)用格式如下：

調(diào)用方法1：變量對象= Application.WorksheetFunction.表函數(shù)（表函數(shù)參數(shù)）

                      實(shí)例：Range("d8") = Application.WorksheetFunction.CountIf(Range("A1:A10"), "B")

調(diào)用方法2：變量對象=Applicaiton.表函數(shù)（表函數(shù)參數(shù)）

                      實(shí)例：Range("d8") = Application.CountIf(Range("A1:A10"), "B")

調(diào)用方法3：變量對象= WorksheetFunction.表函數(shù)（表函數(shù)參數(shù)）

                     實(shí)例：Range("d8") = WorksheeFunction.CountIf(Range("A1:A10"), "B")

       上述調(diào)用方法需要注意：

（1）并非所有表函數(shù)都可以在VBA中通過applicaition或worksheetfunction予以調(diào)用，比如trunc，numberstring。表函數(shù)if（）在VBA中，用if then或者iff（）替代，而表函數(shù)的ROW（）函數(shù)，通過單元格屬性.row予以替代。

（2）表中使用函數(shù)和規(guī)則和VBA中使用函數(shù)的語法規(guī)則不一樣。比如單元格中，求和函數(shù)sum（A1：A3),在VBA中，通過application或worksheetfunction的調(diào)用時(shí)就和函數(shù)是sum（range（“A1：A3")）。這里，"A1:A3"字符串變?yōu)閞ange的一個(gè)參數(shù)，而range（）作為sum的參數(shù)。如果是某個(gè)一個(gè)單元格，也可以用cell（行號，列號）替代range（）。值得注意的是，使用cells（）時(shí)，其行號和列號全部可變?yōu)樽兞浚胷ange（）參數(shù)為字符串，如果涉及變量，就需要進(jìn)行字符串組合的方式使之變?yōu)橐粋€(gè)區(qū)域參數(shù)。當(dāng)然，range（）參數(shù)的字符串也可以通過cells替換，比如range（"A1：B4"）通過range（cells（1,1），cells（4,2））

（3）調(diào)用函數(shù)可以逐級調(diào)用。首先是application，其次是worksheetfunction，最后是application.worksheetfunction方式。

（4）實(shí)際的操作中，可能發(fā)現(xiàn)，使用內(nèi)置的工作表函數(shù)并不一定是最快，最高效的，但無疑是最直接，最省事的?？梢栽诓痪╒BA語言以及相關(guān)邏輯規(guī)則的前提下，迅速通過調(diào)用這些內(nèi)置函數(shù)實(shí)現(xiàn)目標(biāo)。

（5）上述調(diào)用方法返回的值可能是一個(gè)而錯(cuò)誤值，比如vlookup調(diào)用返回來沒找到結(jié)果，如果在VBA中將這個(gè)調(diào)用函數(shù)的值直接返回給一個(gè)變量可能報(bào)錯(cuò)。在賦給某個(gè)變量之前，要判斷是否為錯(cuò)誤值可以用application.isna（）。

注明：在使用countif函數(shù)時(shí)，如果滿足的條件中需要引用變量，則應(yīng)該使用“&”，=countif(range(B2:B4),"="&i)(函數(shù)參數(shù)中的i是循環(huán)變量)