VBA中调用excel的函数

 [原创]EXCEL函数应用之五：VBA中调用库函数

        熟悉EXCEL的同仁们都知道EXCEL带有强大的库函数，各种各样，而且每个函数组合起来使用可以实现很多统计、分析或计算的目标。更进一步，在VBA代码之中，也可以直接调用EXCEL的库函数，而且这种调用有时能够简化VBA代码编写过程，比如有些通过循环寻找某个数字的功能直接在VBA中，用VLOOKUP或Hlookup就可以实现。下面简要就VBA中调用库函数的功能进行介绍。

        VBA中使用库函数一般包括下面几个情况： 

一、在VBA代码中设置单元格的公式调用函数

      比如需要给C16单元格设置一个条件求和公式，可以按如下格式设置：Range("c16") = "=SUMIF(A2:A6,""b"",B2:B6)"    。此时，在公式字符串中遇到引号就把引号加倍。如果是给某个单元格设置数组公式则，需要启用单元格的formulaarray属性，如：Range("c9").FormulaArray = "=SUM(B2:B6*C2:C6)"

二、在VBA代码中通过库函数计算数据  

     直接通过固定的公式，将结算的结果赋给某个单元格，要启用函数evaluate（）。如将条件求和的结果赋给单元格D16的方式，如下：Range("d16") = Evaluate("=SUMIF(A2:A6,""b"",B2:B6)") '宏表函数直接把公式表达式转化为数值。如果是数组，类似处理，比如：Range("d9") = Evaluate("=SUM(B2:B6*C2:C6)")

三、在VBA代码中直接调用计算数据

       上述两种调用库函数的办法其实没有将函数真正作为VBA代码执行的一部分，而是将其放到单元各种由EXCEL自动运算。实际上EXCEL的表函数也可以直接在VBA中直接调用执行，具体调用格式如下：

调用方法1：变量对象= Application.WorksheetFunction.表函数（表函数参数）

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

调用方法2：变量对象=Applicaiton.表函数（表函数参数）

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

调用方法3：变量对象= WorksheetFunction.表函数（表函数参数）

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

       上述调用方法需要注意：

（1）并非所有表函数都可以在VBA中通过applicaition或worksheetfunction予以调用，比如trunc，numberstring。表函数if（）在VBA中，用if then或者iff（）替代，而表函数的ROW（）函数，通过单元格属性.row予以替代。

（2）表中使用函数和规则和VBA中使用函数的语法规则不一样。比如单元格中，求和函数sum（A1：A3),在VBA中，通过application或worksheetfunction的调用时就和函数是sum（range（“A1：A3")）。这里，"A1:A3"字符串变为range的一个参数，而range（）作为sum的参数。如果是某个一个单元格，也可以用cell（行号，列号）替代range（）。值得注意的是，使用cells（）时，其行号和列号全部可变为变量，而用range（）参数为字符串，如果涉及变量，就需要进行字符串组合的方式使之变为一个区域参数。当然，range（）参数的字符串也可以通过cells替换，比如range（"A1：B4"）通过range（cells（1,1），cells（4,2））

（3）调用函数可以逐级调用。首先是application，其次是worksheetfunction，最后是application.worksheetfunction方式。

（4）实际的操作中，可能发现，使用内置的工作表函数并不一定是最快，最高效的，但无疑是最直接，最省事的。可以在不精通VBA语言以及相关逻辑规则的前提下，迅速通过调用这些内置函数实现目标。

（5）上述调用方法返回的值可能是一个而错误值，比如vlookup调用返回来没找到结果，如果在VBA中将这个调用函数的值直接返回给一个变量可能报错。在赋给某个变量之前，要判断是否为错误值可以用application.isna（）。

 

注明：在使用countif函数时，如果满足的条件中需要引用变量，则应该使用“&”，=countif(range(B2:B4),"="&i)(函数参数中的i是循环变量)