跳转至

06 内置函数

我们都知道，EXCEL函数有自己的函数可以直接调用，其实VBA中也有内置的函数可以使用，下面我把常用的进行整理

VBA 常用内置函数一览表

一、数学与数值函数

函数名 功能说明 函数名 功能说明
Int 向下取整 Fix 向 0 取整
Round 四舍五入 Abs 绝对值
Sqr 平方根 Rnd 生成随机数
Sin 正弦值 Cos 余弦值
Tan 正切值 Atn 反正切
Log 自然对数 Exp e 的指数

补充一个生成最小值到最大值的写法：Int((Max - Min + 1) * Rnd + Min)

二、字符串处理函数

函数名 功能说明 函数名 功能说明
Len 字符串长度 Left 左截取
Right 右截取 Mid 中间截取
InStr 查找位置 Replace 替换字符串
UCase 转大写 LCase 转小写
Trim 去首尾空格 LTrim 去左空格
RTrim 去右空格 StrComp 字符串比较
Chr ASCII 转字符 Asc 字符转 ASCII

补充：strconv(字符串，方式) 也可以实现大小写转换，vbUpperCase（大写），vbLowerCase（小写），vbProperCase（首字母大写）

三、日期与时间函数

函数名 功能说明 函数名 功能说明
Now 当前日期时间 Date 当前日期
Time 当前时间 Year 年
Month 月 Day 日
Hour 小时 Minute 分
Second 秒 Weekday 星期
DateAdd 日期加减 DateDiff 日期差
DateSerial 生成日期 TimeSerial 生成时间
TimeValue 提取文本中的时间 DateValue 提取文本中的日期

这个里面有稍微复杂的两个内置函数，分别是 DateAdd 和 DateDiff 函数，下面详细写一下写法：

（1）DateAdd函数

在VBA中，DateAdd 函数用于在日期基础上添加或减去指定的时间间隔，基本用法为:

DateAdd(interval, number, date)

  • interval：字符串表达式，表示要添加的时间间隔类型
  • number：数值表达式，表示要添加的间隔数（可为负数表示减去）
  • date：要操作的日期

其中interval的表达形式有很多，具体可以参考下表：

参数 含义 示例
"yyyy" 年 DateAdd("yyyy", 1, Date)
"q" 季度 DateAdd("q", 1, Date)
"m" 月 DateAdd("m", 1, Date)
"y" 一年的天数 DateAdd("y", 1, Date)
"d" 日 DateAdd("d", 1, Date)
"w" 工作日 DateAdd("w", 1, Date)
"ww" 周 DateAdd("ww", 1, Date)
"h" 小时 DateAdd("h", 1, Now)
"n" 分钟 DateAdd("n", 30, Now)
"s" 秒 DateAdd("s", 60, Now)

具体应用可以参考如下代码： 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
Sub DateAdd_Examples()
    Dim originalDate As Date
    originalDate = #3/15/2024#

    ' 添加1年
    Debug.Print "加1年: " & DateAdd("yyyy", 1, originalDate)  ' 2025/3/15

    ' 减去2个月
    Debug.Print "减2个月: " & DateAdd("m", -2, originalDate)  ' 2024/1/15

    ' 添加45天
    Debug.Print "加45天: " & DateAdd("d", 45, originalDate)  ' 2024/4/29

    ' 添加3小时
    Debug.Print "加3小时: " & DateAdd("h", 3, Now)

    ' 添加90分钟
    Debug.Print "加90分钟: " & DateAdd("n", 90, Now)
End Sub

（2）DateDiff函数

在VBA中，DateDiff 函数用于计算两个日期之间的时间间隔。以下是其具体语法：

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

  • interval：时间间隔类型（字符串），其值的选取和DateAdd函数基本一致，也是“yyyy”这样类似的
  • date1：第一个日期（较早日期）
  • date2：第二个日期（较晚日期）
  • firstdayofweek：可选，指定一周的第一天（默认周日）
  • firstweekofyear：可选，指定一年的第一周（默认1月1日所在周）

具体应用可以参考如下代码： 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
Sub DateDiff_Examples()
    Dim date1 As Date, date2 As Date

    date1 = #3/15/2024#
    date2 = #6/20/2024#

    ' 计算天数差
    Debug.Print "天数差: " & DateDiff("d", date1, date2)  ' 97

    ' 计算月数差（整月）
    Debug.Print "月数差: " & DateDiff("m", date1, date2)  ' 3

    ' 计算年数差
    Debug.Print "年数差: " & DateDiff("yyyy", date1, date2)  ' 0

    ' 计算周数差
    Debug.Print "周数差: " & DateDiff("ww", date1, date2)  ' 14

    ' 计算季度差
    Debug.Print "季度差: " & DateDiff("q", date1, date2)  ' 1
End Sub

四、逻辑与判断函数

函数名 功能说明 函数名 功能说明
IIf 条件判断 IsNumeric 是否为数字
IsDate 是否为日期 IsEmpty 是否为空
IsNull 是否为 Null TypeName 变量类型
VarType 类型值

补充：IIf(2 > 0, "成立", "不成立") 类比EXCEL的IF函数，也是条件判断

五、类型转换函数

函数名 功能说明 函数名 功能说明
CInt 转 Integer CLng 转 Long
CSng 转 Single CDbl 转 Double
CStr 转 String CBool 转 Boolean
CDate 转 Date CByte 转 Byte

六、数组与其他常用函数

函数名 功能说明 函数名 功能说明
UBound 数组上限 LBound 数组下限
Split 拆分字符串 Join 合并数组
Array 创建数组 Choose 按索引取值
MsgBox 消息框 InputBox 输入框
DoEvents 释放资源

VBA调用EXCEL函数

一、通过 WorksheetFunction 调用 Excel 函数

在 VBA 中，除了可以使用大量 VBA 内置函数 外，还可以直接调用 Excel 工作表函数。这使得 VBA 既具备编程能力，又能复用 Excel 强大的计算函数库。而通过WorksheetFunction 调用是 最标准、最常用 的方式，适用于绝大多数 Excel 函数，基本语法如下：

Application.WorksheetFunction.函数名(参数1, 参数2, ...)

或简写为：

WorksheetFunction.函数名(参数1, 参数2, ...)

二、WorksheetFunction函数调用示例

（1）求和 SUM

1
2
Dim result As Double
result = WorksheetFunction.Sum(Range("A1:A10"))
（2）平均值 AVERAGE

1
result = WorksheetFunction.Average(Range("A1:A10"))

（3）最大值 / 最小值

1
2
maxVal = WorksheetFunction.Max(Range("A1:A10"))
minVal = WorksheetFunction.Min(Range("A1:A10"))
（4）计数相关

1
2
cnt1 = WorksheetFunction.Count(Range("A1:A10"))      ' 数值个数
cnt2 = WorksheetFunction.CountA(Range("A1:A10"))     ' 非空个数
（5）条件类函数IF

1
result = WorksheetFunction.If(A1.Value > 60, "及格", "不及格")

（6）条件类函数 SUMIF

1
result = WorksheetFunction.SumIf(Range("A:A"), ">=60", Range("B:B"))

（7）条件类函数 COUNTIF

1
cnt = WorksheetFunction.CountIf(Range("A:A"), ">=60")

三、通过 Evaluate 调用 Excel 函数

Evaluate 允许你 像在单元格中一样写公式，非常适合动态公式或复杂表达式。例如：

1
result = Evaluate("SUM(A1:A10)")
也可以使用变量：

1
result = Evaluate("SUM(" & rng.Address & ")")

四、VBA 中常用 Excel 函数速查

Excel 函数 VBA 调用方式 Excel 函数 VBA 调用方式
SUM WorksheetFunction.Sum AVERAGE WorksheetFunction.Average
MAX WorksheetFunction.Max MIN WorksheetFunction.Min
IF WorksheetFunction.If COUNT WorksheetFunction.Count
COUNTA WorksheetFunction.CountA COUNTIF WorksheetFunction.CountIf
SUMIF WorksheetFunction.SumIf VLOOKUP Application.VLookup
HLOOKUP Application.HLookup MATCH WorksheetFunction.Match
INDEX WorksheetFunction.Index TEXT WorksheetFunction.Text

AutoFill方法让公式自动填充

如果VBA给EXCEL中的单元格填入公式，能不能像EXCEL里面，填好了一个单元格的公式，直接用填充柄就可以将其他部分的公式也填写完整呢？这里我们就需要用到AutoFill方法了，基本用法如下：

Range.AutoFill(Destination, Type)

  • Destination：要填充的目标区域（必须包含源区域）
  • Type：填充类型常量（见下表）
常量 值 说明 常量 值 说明
xlFillDefault 0 默认，Excel 自动判断 xlFillCopy 1 复制值
xlFillSeries 2 填充序列 xlFillFormats 3 仅填充格式
xlFillValues 4 仅填充值 xlFillDays 5 按日填充序列
xlFillWeekdays 6 按工作日填充 xlFillMonths 7 按月填充
xlFillYears 8 按年填充 xlLinearTrend 9 线性趋势
xlGrowthTrend 10 增长趋势 xlFlashFill 11 快速填充（Excel 2013+）

（1）基本序列填充 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
Sub BasicAutoFill()
    ' 在A列填充数字序列
    Range("A1").Value = 1
    Range("A1").AutoFill Destination:=Range("A1:A10"), Type:=xlFillSeries

    ' 在B列填充月份
    Range("B1").Value = "一月"
    Range("B1").AutoFill Destination:=Range("B1:B12"), Type:=xlFillMonths

    ' 在C列填充日期序列
    Range("C1").Value = Date
    Range("C1").AutoFill Destination:=Range("C1:C10"), Type:=xlFillDays
End Sub
（2）复制值和格式 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
Sub CopyWithAutoFill()
    ' 复制公式
    Range("A1").Formula = "=ROW()"
    Range("A1").AutoFill Destination:=Range("A1:A10"), Type:=xlFillCopy

    ' 复制值和格式
    Range("B1").Value = "示例"
    Range("B1").Interior.Color = vbYellow
    Range("B1").Font.Bold = True
    Range("B1").AutoFill Destination:=Range("B1:B5"), Type:=xlFillFormats

    ' 仅复制值（不复制公式）
    Range("C1").Formula = "=TODAY()"
    Range("C1").AutoFill Destination:=Range("C1:C5"), Type:=xlFillValues
End Sub
（3）公式填充

1
2
3
4
Sub FormulaWithAutoFill()
    range("C1") = "=SUM(A1:B1)"
    range("C1").autofill range("C1:C3"), type:=xlFillDefault
End Sub
本期重点带大伙了解了VBA中支持的各种函数，给大家整理了关键的几张对照表，忘记的时候可以回来查阅