5.5 常用内置函数

Excel VBA为用户内置了很多的函数，包括了数学函数、字符串函数、日期时间函数和转换函数。通过这些函数可以简化我们的编程过程。在VBA编程中还可以使用Excel 2007的内置函数，这为熟悉Excel函数但是不熟悉VBA函数的用户提供了很大的便利。

5.5.1 数学函数

数学函数通常来说比较简单，其功能和数学上的功能一致，常见的数学函数如表5-2所示。

表5-2 数学函数

有关这些函数的具体用法，用户可以参照VBA的帮助内容。

5.5.2 字符串函数

在VBA中经常会对某些单元格的文本进行处理，这些常见的处理包括了对空格的处理，字符串截取等。

1. 空格处理

在VBA中清除空格的函数包括了Trim、LTrim和RTrim。Trim函数的含义是清除指定文本的左右两端的空格，LTrim是清除指定文本左侧的空格，RTrim是清除指定文本最右侧的所有空格。但是字符串之间的空格不会被清除。添加空格用的就是Space函数，函数的使用方法就是Space(空格数)，表示要添加的空格数。和空格处理相关的函数如表5-3所示。

表5-3 和空格处理相关的函数

如果用户要消除的是全部所有的空格，那么用上述Trim类的函数是无法解决问题的，可以用Replace函数来完成。Replace函数有点类似于Excel中的查找替换功能。

Replace函数的计算结果是一个字符串，该字符串是指定的字符串被查找替换后的结果，不仅仅可以查找替换指定的字符串还可以指定替换发生的次数。语法结构如下所示：

Replace(expression, find, replace[, start[, count[, compare]]])

● Expression：是用户指定的字符串。

● Find：查找的内容。

● Replace：查找到指定内容后要替换成的内容。

● Start：表示从字符串的第几个字符位置开始查找。

● Count：替换的次数。

● Compare：比较的类型。

上述参数中expression、find和replace参数是必需的，其他的4个参数是可选的。如果不指定次数就类似于全部替换。例如，可以使用Replace来替换掉指定文本中所有的空格。

    newStr= Replace(" Hello   World  ", " ", "") '运行结果就是“HelloWorld”

2. 其他字符串相关函数

在VBA中通常可以使用Left、Right和Mid函数来截取字符串中的字符。这三个函数的使用方法和Excel中的函数是一致的，还可以通过函数来获得字符串的长度等信息，其他和字符串相关的函数如表5-4所示。

表5-4 其他和字符串相关的函数

Split函数是一个特别重要的函数，该函数用于提取带有分隔符的字符串。Split函数的语法结构如下所示：

Split(指定字符串[,分隔符[, 返回字符串数[, 比较方式]]])

例如有一个字符串“sheet1,sheet2,sheet3”，逗号将这个字符串分隔成了三个部分，只要指定字符串和分隔符就能通过Split函数能够提取到被逗号分隔的各个部分的信息。Split函数最终运行的结果是一个数组，该数组的下标从0开始。

例5-12：用代码能够将字符串“sheet1,sheet2,sheet3”中的工作表的名称提取出来，并放置到A列中。

    #001:  Sub Split函数演示()
    #002:     Dim myString As String
    #003:     Dim i As Integer
    #004:     Dim newString
    #005:     myString = "sheet1,sheet2,sheet3"
    #006:     newString = Split(myString, ",")
    #007:     For i = 0 To 2
    #008:         Cells(i + 1, 1) = newString(i)
    #009:     Next
    #010:  End Sub

第2行到第4行代码声明了几个变量，myString是处理前的字符串，变量i是字符串中被分隔符分隔的字符串数，newString被声明为Variant类型，是使用Split函数处理后的结果，该变量表示一个数组。第5行代码给出了处理前的字符串，该字符串实际上是三个工作表的名称，每个工作表名称前使用逗号隔开。第6行代码是使用Split函数将字符串进行分解处理，将分解处理后的结果放置到数组newString中。第7行到第9行代码是就是读取数组的内容，并将数组中的内容显示在工作表的A1到A3单元格内。由于处理前的字符串由两个逗号分隔成三个名称，因此数组newString包含了三个元素。上述代码的运行结果是在A1到A3单元格内显示Sheet1、Sheet2和Sheet3。

5.5.3 日期时间函数

VBA中日期时间函数和Excel 2007中的日期时间按函数类似。日期是指年月日的数值，而时间则表示的是时分秒的信息。常见的日期时间函数如表5-5所示。

表5-5 日期时间函数

在和日期时间有关的函数中，DateDiff函数是一个比较复杂的函数，该函数有点类似于Excel 2007中的Datedif函数，但是其含义和Datedif函数又有不同。DateDiff函数的语法结构是：

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

各个参数的含义如下。

● interval：表示计算时间间隔的类型。例如是计算两个时间之间的整年数还是整月数等。

● date1, date2：表示开始的时间和结束的时间。

● Firstdayofweek：表示以哪一天作为一个星期的开始，默认情况下以星期日作为一个星期的开始。

● Firstweekofyear：表示哪一周作为新年的第一周，默认情况下是包含1月1日的那个星期作为新年的第一周。

Interval参数可以选择的数值如表5-6所示。

表5-6 Interval参数可以选择的数值

下述代码计算了两个指定日期之间的月份间隔。

    #001:  Sub计算月份间隔()
    #002:     MsgBox DateDiff("m", "2008-1-31", "2008-2-1")
    #003:  End Sub

第2行代码计算了月份间隔，但是该函数并不是表示两个日期之间相差的整月数，它描述的仅仅是两个月份之间间隔的月份数，虽然代码中给出的日期只相差一天，但是最终的结算结果却是1，表示跨月份了。

5.5.4 和转换有关的函数

不同的数据类型可以进行转换的，在Excel中，最常见的例子就是将身份证号码或者电话号码从数字类型转换为字符串类型。VBA中不同数据类型的转换使用的是转换函数，不同数据类型之间的转换所使用的函数通常用C开头，常见的转换函数如表5-7所示。

表5-7 常见的转换函数

5.5.5 使用工作表函数

VBA中可以使用的函数毕竟不多，而且用户通常对VBA函数并不熟悉，而是对Excel的内置工作表函数比较熟悉，此时可以使用这些工作表函数来为用户的代码服务，简化代码长度，提高编程的效率。

1. 使用WorkSheetFunction

使用Excel的内置函数其语法结构为：

    Application.WorkSheetFunction.函数名称(参数)或Application.函数名称(参数）

但不是所有的工作表函数都能这样使用。WorkSheetFunction使用规则是：

● 如果某项功能只有工作表函数有，而VBA中没有类似功能的函数，那么此时就可以使用Excel的内置工作表函数。

● 如果在工作表中和VBA中都有了具有同样功能的函数，那么此时只能使用VBA函数，而不能使用Excel内置工作表函数。

例如在工作表中可以使用IsBlank函数用来检查是否引用了空单元格，在VBA中也有一个具有相同功能的函数IsEmpty，那么在VBA代码中就不能使用IsBlank函数而应该使用IsEmpty函数。

例5-13：在代码中使用CountA函数的统计A列中非空单元格的数目。

    #001:  Sub统计非空单元格()
    #002:     Dim i As Long
    #003:     Dim rng As Range
    #004:     Set rng = ActiveSheet.Range("a:a")
    #005:     i = Application.WorksheetFunction.CountA(rng)
    #006:     MsgBox "在A列中非空单元格数目为" & i & "个"
    #007:  End Sub

第2行代码声明了变量i用来放置未来的统计结果。第3行代码设置了一个对象变量rng，表示一个单元格区域。第4行代码使用Set为对象变量赋值。第5行代码利用了CountA函数来统计非空单元格个数。第6行代码利用消息框给出了统计结果。

2. 使用Evaluate

不过我们有时候还是很难知道VBA中哪些函数的功能和Excel工作表中的函数功能是相同的，为此可以使用Evaluate来强制使用工作表函数。其语法结构为：

Application.Evaluate（函数表达式）

此时的函数表达式用引号标示，函数表达式要通过等号引出。通过Evaluate，用户就可以使用任意的工作表函数，而不需要考虑在VBA中是否具有功能的函数。例如通过Evaluate就可以在代码中使用IsBlank函数。

    Application.Evaluate("=isblank(a1)")

例5-14：在VBA代码中可以用Evaluate来统计A列中的非空单元格。

    #001:  Sub统计非空单元格()
    #002:     Dim i As Long
    #003:     i = Application.Evaluate("=counta(a:a)")
    #004:     MsgBox "在A列中非空单元格数目为" & i & "个"
    #005:  End Sub

第3行代码通过Evaluate来使用工作表中的函数。上述代码的运行结果和例5-13中的运行结果一致。

3. 为单元格指定公式内容

如果要将结果显示在单元格内，可以直接为单元格指定公式内容。公式的内容是字符串。

例5-15：在VBA代码中统计A列中的非空单元格，并将最终结果放置在B1单元格内。

    #001:  Sub统计非空单元格()
    #002:     Range("b1").Formula = "=counta(a:a)"
    #003:  End Sub

第2行代码中，直接将B1单元格中的内容通过字符串的形式指定出来。在这种方式下也无需考虑工作表函数在VBA代码中是否能够直接使用。