[原创]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是循环变量)