Excel VBA Notes

提高工作效率吧打工人

---

基础

入门方法

录制宏

视图→宏→录制宏 / 开发工具

Alt+F11查看录制内容

参考教程

懒人Excel - VBA教程

基本概念

VBE编辑器 Visual Basic Editor

Alt+F11进入编辑器

注释

以英文单引号‘开头 。

用途：

• 提供过程或函数的基本信息、用途
• 说明变量的用途
• 解释为什么使用当前的方法
• 区分开不同代码块
• 在开发调试过程中，临时注释一段代码，使其不被执行，检查代码其余部分是否有错误

宏

可运行的VBA代码块。

对象

对象是一个物，它可以是一个事、一个物体、一个概念、一个名词。对象包含描述静态信息的属性和对对象可以操作的方法。

以生活中的对象为例子，汽车是一个对象。汽车的车牌号、油量、里程等是汽车的属性；开车、加油、换车牌等是汽车的方法。

常用Excel对象

• Application 对象，表示 Excel 应用程序。
• Workbook 对象，表示工作簿对象。
• Worksheet 对象，表示工作表对象
• Range 对象，表示单元格区域对象。

模块

模块是包含一个或多个过程或函数的内部组件。一个工作簿内包含的模块数量没有限制，一个模块内包含的过程或函数数量也没有限制。模块用来作为保存过程或函数的容器，这些过程和函数通常应用于整个工作簿。

通过把多个过程和函数，合理的放置在不同的模块，可以使整个 VBA 代码逻辑更清晰、更易于阅读和理解。

用户窗体

VBA和用户交互的界面，最基本的窗体控件包括：

• 文本控件
• 按钮控件
• 列表控件
• 输入控件

「开发工具」选项卡

功能区中右键→「自定义功能区」→√「开发工具」

Excel VBA设置宏安全性

默认情况下，为防止来源不明的工作簿自带宏自动运行，Excel 会禁用宏的运行。

宏安全性

「开发工具」选项卡→「宏安全性」→打开信任中心：

• 禁用所有宏，并且不通知：无法运行打开的工作簿内的 VBA 代码，Excel 也不会提示工作簿包含代码。
• 禁用所有宏，并发出通知：默认状态下，无法运行 VBA 代码。但是 Excel 在打开包含 VBA 代码的工作簿时，在编辑栏上方，显示安全警告，并且可以选择启用代码运行或不启用。如果选择启用，下次打开相同的工作簿，不会出现警告。
• 禁用无数字签署的所有宏：宏将被禁用，但如果存在宏，则会显示安全警告。但是，如果受信任发布者对宏进行了数字签名，并且您已经信任该发布者，则可运行该宏。如果您尚未信任该发布者，则会通知您启用签署的宏并信任该发布者。
• 启用所有宏(不推荐；可能会运行有潜在危险的代码)：可以运行所有宏。一般不推荐选择此选项。

安全起见，可以采取：

• 禁用所有宏，并发出通知。
• 在电脑上创建一个用于存放信任的包含代码的工作簿的文件夹，将此工作簿添加到受信任为位置。
  • 把一个文件夹添加到 Excel 受信任的位置后，该文件夹下的包含 VBA 代码的工作簿打开时，不会提示安全警告，也无需每次手动开启代码。
  • 在开发工具选项卡，点击「宏安全性」按钮，弹出信任中心窗口，在左侧列表中，选择「受信任位置」。点击下方的「添加新位置」按钮，添加自己信任的一个文件夹。

Excel 保存包含 VBA 代码的工作簿

2003 及之前的版本中，在 xls类型工作簿可以任意编写并保存 VBA 代码。

2007 版本开始，第一次保存包含 VBA 代码的工作簿时，Excel 会提示“无法保存工作簿”。

含 VBA 代码的工作簿，必须保存成启用宏的工作簿类型。Excel 为此提供了xlsm类型的工作簿，称之为「启用宏的工作簿」。

使用VBA 编辑器进行 Excel VBA 开发

打开方法

• 方法一：「开发工具」选项卡→Visual Basic
• 方法二：工作表右键菜单→查看代码(V)
• 方法三：快捷键Alt + F11

编辑器模块

• 工具栏：编辑器命令栏，与 Excel 功能区域类似，包含 Excel VBA 开发相关的命令。
• VBA 工程：显示当前 VBA 工程包含的所有对象。通常，一个工作簿就是一个 VBA 工程，其中包括 Excel 对象、工作表对象、模块等。
• 属性窗口：查看和设置选中对象的属性的窗口。
• 代码编辑窗口：实际编写代码的位置。编写、修改、保存代码，都在这里进行。
• 立即窗口：代码运行过程中，打印出的内容，在立即窗口中显示。一般用于调试代码

运行VBA代码

• 方法一：VBA编辑器工具栏「运行」→「运行子过程」
• 方法二：VBA编辑器快捷工具栏的播放图标
• 方法三：快捷键F5
• 方法四：给图形或者按钮指定宏，点击运行

---

VBA 变量、类型、运算符

变量

VBA变量时一个存储数据的VBA代码接口，可存储、改变、参与计算

• 变量名：代表变量的名称
• 变量类型：变量存储的数据的类型，例如数字、文本、逻辑值等

声明变量

Dim [变量名] As [数据类型]

变量命名

• 首字母必须以字母开头。
• 不能包含空格、.(英文句号)、!(感叹号)、@、&、$、# 等字符。
• 长度不能超过 255 个字符。
• 不能使用 VBA 中保存的关键词作为变量名。

驼峰命名法：当变量名或函数名是由一个或多个单词连结在一起，而构成的唯一识别字时，第一个单词以小写字母开始；从第二个单词开始以后的每个单词的首字母都采用大写字母。

例如：myFirstName、myLastName，这样的变量名看上去就像骆驼峰一样此起彼伏，故得名。

变量类型

三大类数据类型：数字类型、非数字类型、通用类型。

数字类型：

类型	说明	数据范围
Byte	字节	0 至 255
Integer	整数	-32,768 至 32,767
Long	长整数	-2,147,483,648 至 2,147,483,648
Single	单精度浮点数	在表示负数时： -3.402823E38 ~ -1.401298E-45 在表示正数时： 1.401298E-45 ~ 3.402823E38
Double	双精度浮点数	在表示负数时： -1.79769313486231E308 ~ -4.94065645841247E-324 在表示正数时： 4.94065645841247E-324 ~ 1.79769313486231E308
Currency	货币	-922,337,203,685,477.5808 至 922,337,203,685,477.5807
Decimal	定点数	未放置定点数： +/- 79,228,162,514,264,337,593,543,950,335 放置定点数： +/- 7.9228162514264337593543950335

非数字类型：

非数字变量通常不能直接参与算术运算。

类型	说明	数据范围
String	文本类型	0 至 20亿字符
Boolean	逻辑值	True 或 False
Date	日期和时间	时间：00:00:00 至 23:59:59 日期： 100-1-1 至 9999-12-31
Object	对象	VBA 和 Excel 对象

通用类型：

可存储任何类型的数据。在程序运行过程，VBA 可以自动识别数据类型，参与计算。

类型	说明	数据范围
Variant	任意类型	不限

Variant 类型虽然灵活，但是它会占用更多内存空间，执行效率也会受影响。因此建议，在明确知道数据是何种类型时，指定数据类型；如果数据类型是可变的或不明确，使用 Variant 类型。

给变量赋值

[变量名] = [数据]

常量

在声明时就要指定值。

Const [常量名] As [数据类型] = [值]

常量类型

与变量相同。

运算符

赋值运算符

运算符	说明	示例
=	给变量赋值	name = “Zhang San”

算术运算符

假设 a = 10，b = 3，-> 表示结果。

运算符	说明	示例
+	两数相加	a + b -> 13
–	两数相减	a – b -> 7
*	两数相乘	a * b -> 30
/	两数相除	a / b -> 2.5
\	两数相除，取整数部分	a \ b -> 3
Mod	两数相除，取余数	a Mod b -> 1
^	幂运算	a ^ b -> 1000
-（取负）	对数字取负	-a -> -10

比较运算符

假设 a = 10，b = 3，-> 表示结果。

运算符	说明	示例
=	比较两个值是否相等	a = b -> False
>	大于	a > b -> True
>=	大于等于	a >= b => False
<	小于	a < b -> False
<=	小于等于	a <= b -> False
<>	不等于	a <> b -> True

逻辑运算符

逻辑运算符对逻辑值，即 True 和 False，进行逻辑运算，返回运算结果，运算结果也是逻辑值。

假设 a = True，b = False，-> 表示结果。

运算符	说明	示例
And	逻辑与，两个表达式都是真，返回 True。	a And b -> False
Or	逻辑或，两个表达式至少有一个为真，返回 True。	a Or b -> True
Not	逻辑否，对逻辑表达式取否	Not a -> False
Xor	逻辑异或，如果两个表达式不相同，返回 True	a Xor b -> True

连接运算符

VBA 中的连接运算符用于连接 2 个或多个文本。其用法与 Excel 公式中的 & 符号相同。

运算符	说明	示例
&	连接两个文本	“Zhang” & ” ” & “San” -> “Zhang San”

其他操作符

其他操作符

运算符	说明
_ (下划线)	将一行代码分解成两行
: ( 英文冒号)	将两行代码放置在一行

数据类型

文本类型

类型	说明	数据范围
String	文本类型	0 至 20亿字符

Dim name As String
name = "Zhang San"
name = "101"
name = Range("A1")

数字类型

选择合适的数字类型：如果小数字使用大范围数字类型存储，会浪费计算机内存；如果大数字使用小范围的数字类型存储，VBA 会自动转换成对应小范围数字，导致数字丢失精度。

类型	说明	数据范围
Byte	比特	0 至 255
Integer	整数	-32,768 至 32,767
Long	长整数	-2,147,483,648 至 2,147,483,648
Single	单精度浮点数	在表示负数时： -3.402823E38 ~ -1.401298E-45 在表示正数时： 1.401298E-45 ~ 3.402823E38
Double	双精度浮点数	在表示负数时： -1.79769313486231E308 ~ -4.94065645841247E-324 在表示正数时： 4.94065645841247E-324 ~ 1.79769313486231E308
Currency	货币	-922,337,203,685,477.5808 至 922,337,203,685,477.5807
Decimal	定点数	未放置定点数： +/- 79,228,162,514,264,337,593,543,950,335 放置定点数： +/- 7.9228162514264337593543950335

Dim age as Integer

逻辑类型

类型	说明	数据范围
Boolean	逻辑值	True 或 False

Dim isPass As Boolean
isPass = False
isPass = 70 >= 60

日期和时间类型

VBA 中的日期和时间使用数字表示，整数部分代表日期，小数部分代表时间。

• 日期从 100-1-1 开始到 9999-12-31。
• 时间从 00:00:00 到 23:59:59。

Dim birthday As Date
Dim time As Date

给日期变量赋值时，可以直接把日期放置在两个 # 之间赋值，也可以使用数字，还可以把日期作为文本赋值：

birthday = #2018-1-1#
birthday = 43101
birthday = "2018-1-1"

time = #12:00:00#
time = 0.5
time = "12:00:00"

Variant 类型

Variant 类型是一种通用类型，可以表示任何一种类型的数据。它也是声明变量未指定数据类型时的默认类型。

虽然 Variant 类型方便，但是相应的，占用更大的内存空间，也会影响程序运行效率。因此建议，在明确知道数据时何种类型时，指定数据类型；如果数据类型是可变的或不明确，使用 Variant 类型。

---

程序结构

基本概念

过程

过程是 VBA 中，程序实际运行的最小结构。单独的一行或多行代码无法运行，必须把它们放置在一个过程里，才能运行。

在示例中，Sub 过程名() 开头，End Sub 为结尾部分是一个过程的主题，其余代码需要放置在两者之间。

Sub 过程名()
    
End Sub

程序语句

语句，是表示一个完整意思的一行代码。

• 声明式语句，也就是声明变量、常量、过程或者函数。
• 执行式语句，执行指定动作。动作可以包括执行一个过程、开始一个循环、判断表达式等。
• 赋值语句，给变量赋值，是执行式语句的特殊形式。

VBA对象

程序运行结构

• 顺序结构
• 循环结构
• 判断结构

声明和赋值

声明变量，就是告诉 VBA，变量的名字和它所存储的值的数据类型。

有4种变量：

• 基本类型变量。基本类型变量是那些存储单个数据的变量，例如数字、文本、日期等。

  • '语法
    Dim [变量名] As [数据类型]
    
    '实例
    Dim name As String
    Dim age As Integer
    Dim height As Double
    Dim birthday As Date
    <!--9-->

• 数组。数组包含多个变量的集合。

  • '语法
    '固定长度数组声明
    Dim [变量名](开始序号 to 结束序号) As [数据类型]
    '动态数组声明
    Dim [变量名]() As [数据类型]
    
    '实例
    '声明包含10个文本类型元素的数组
    Dim names(1 to 10) As String
    '声明长度未知的文本类型数组
    Dim names() As String
    <!--10-->
    

声明变量的语句，必须写在使用它的语句前。

声明多个同类型变量

'第一种，按两行写
Dim i As Integer
Dim j As Integer

'第二种，使用 : 符号，在一行写
Dim i As Integer : Dim j As Integer

不声明变量就使用

也可以，但非常不推荐，弊端：

• 数据类型自动设置为 Variant 类型，效率低。
• 变量名写错，不会提示错误。
• 无法使用 VBA 代码自动补全。
• 数据类型不匹配时，不会提示错误。

强制声明变量：在模块头部写上：

Option Explicit

基本类型变量的赋值

'语法，两种写法相同
Let [变量名] = [数据]
[变量名] = [数据]

'实例
Dim name As String
Let name = "Zhang San"

Dim age As Integer
Let age = 30

Dim birthday As Date
Let birthday = #2000-1-1#

在实际开发中，给基本类型变量赋值时，Let关键词可以忽略不写，直接以变量开头写赋值语句。

数组类型变量赋值

'语法
[数组名](元素序号) = [数据]

'声明数组
Dim arr(1 to 5) As String
'数组赋值
arr(1) = "Zhang San"
arr(2) = "Li Si"
arr(3) = "Wang Wu"

对象类型变量的赋值

'语法
Set [变量名] = [对象类型数据]
    
'声明工作表类型的对象
Dim sheet As Worksheet
'将名称为“绩效表”的工作表，赋到 sheet 变量
Set sheet = Worksheets("绩效表")

对象使用 Set 关键词，并且Set关键词不能省略。

由于对象可以包含多个属性，因此 VBA 提供一种同时给多个属性赋值的简单方法。具体方法是对象多个属性赋值语句，放置在 With+对象和End With关键词中间。

Dim sheet As Worksheet
Set sheet = Worksheets("绩效表")

With sheet
    .Name = "旧绩效"
    .Visible = False
End With

选择结构

If Then 结构

If 条件表达式 Then
    '表达式为真时，执行的代码
End If

If Else 结构

If 条件表达式 Then
    '真时执行的代码
Else
    '假时执行的代码
End If

If ElseIf Else 结构

If 条件表达式1 Then
    '表达式1真时，执行的代码
ElseIf 条件表达式2 Then
    '表达式2真时，执行的代码
ElseIf 条件表达式3 Then
    '表达式3真时，执行的代码
    ...
ElseIf 条件表达式n Then
    '表达式n真时，执行的代码
Else
    '以上表达式都不为真时，执行的代码
End If

注意：

• 条件表达式是从第一个开始判断。
• 判断过程中，只要有一个表达式结果为真，那么执行对应的代码块，然后退出选择结构，不再继续判断剩下的表达式。
• 当所有的表达式都不为真时，执行 Else后的代码块。

Select Case 结构

Select Case 变量
	Case 判断条件 1
    	'条件 1 真时，执行的代码
	Case 判断条件 2
    	'条件 2 真时，执行的代码
	Case 判断条件 3
    	'条件 3 真时，执行的代码
    Case Else
    	'之前的所有条件都不为真时，执行的代码
End Select

举例：

Sub MyCode()

    Dim i As Integer
    
    For i = 2 To 10
    
        Select Case Cells(i, "B").Value
            Case Is >= 85
                Cells(i, "D") = "优"
            Case Is >= 75
                Cells(i, "D") = "良"
            Case Is >= 60
                Cells(i, "D") = "及格"
            Case Else
                Cells(i, "D") = "不及格"
        End Select
        
    Next i

End Sub

循环结构

For 循环

• For … Next 循环

  • For [变量] = [初始值] To [结束值] Step [步长]
        '这里是循环执行的语句
    Next
    <!--22-->

  • [元素] 是与集合中的元素相同类型的变量，该变量可在循环代码中使用。

  • [元素集合]是包括多个元素的集合。

  • 不需要数字变量

  • 用于遍历

• Exit For 语句

  • 用于跳出循环

Do While 循环

• Do While … Loop 循环

  • Do While [条件表达式]
        '循环执行的代码
    Loop
    <!--23-->

  • Do ... Loop While循环至少循环执行代码一次后，再判断条件表达式的值

  • While 和条件表达式写在 Loop 关键词后

• Exit Do 语句

  • 用于跳出 Do While 循环

Do Until 循环

• Do Until 在条件表达式为真时，停止执行循环

• Do Until … Loop 循环

  • Do Until [条件表达式]
        '循环执行的代码
    Loop
    <!--24-->

  • 先运行一次，再判断 Until 后条件表达式的值，如果是真，停止循环；如果是假，继续执行循环

循环小结

循环结构	说明
For … Next 循环	按指定次数循环执行
For Each 循环	逐一遍历数据集合中的每一个元素
Do While … Loop 循环	当条件为真时，循环执行
Do … Loop While 循环	当条件为真时，循环执行。无论条件真假，至少运行一次
Do Until … Loop 循环	直到条件为真时，停止执行
Do … Loop Until 循环	直到条件为真时，停止执行。无论条件真假，至少运行一次

跳出语句	说明
Exit For	跳出 For 循环
Exit Do	跳出 Do While/Until 循环

With 结构

With 结构可以将同一个对象的多个属性和方法组合起来，避免重复写对象名。此外，With 结构还能嵌套使用，进一步提高编程效率和程序运行效率。

With 结构由 With 和 End With 两个语句构成，对象的属性和方法都写在两者之间。基本语法如下：

With [对象]
    .[属性] = [数据]
    .[方法]
    '其他属性和方法
End With

With 结构里，对象的属性和方法均有点 (.)符号开始，后接对象的属性名和方法名。

举例：

Sub MyCode()

    With Worksheets("Sheet1")
        .Name = "新名称"
        .Tab.ThemeColor = xlThemeColorLight1
        .Visible = xlSheetHidden
    End With
    
End Sub

嵌套 with 结构

如果父对象的属性是另一个对象，则针对这个子对象，继续使用 With 结构。

举例：

Sub MyCode()

    With Worksheets("Sheet1")
        .Name = "新名称"
        .Tab.ThemeColor = xlThemeColorLight1
        .Visible = xlSheetHidden
        
        With .Range("A1:A10")
            .Interior.ThemeColor = xlThemeColorAccent1
            .Font.Size = 12
            .Font.Name = "等线"
        End With
        
    End With
    
End Sub

GoTo 结构

GoTo [标签]
'被跳过的代码
...
[标签]:
'被执行的代码

需要注意的是，跳转处的标签，后接冒号 ( : ) 。

---

过程和函数（Sub | Function）

过程

通常一个过程，建议只完成一个特定的小目标。因此，我们的程序往往会包含多个过程。这就是 VBA 中过程概念存在的一个原因。

过程基本语法

VBA 过程以 Sub 语句开始，以 End Sub 语句结束，包含一个或多个语句，完成一个特定的目标。

无参数过程：

Sub [过程名]()
    语句1
    语句2
    ...
    语句n
End Sub

有参数过程：

过程还可以接受一个或多个参数，参数可以是常量、变量、表达式，并且每个参数指定其名称。在过程的语句中，接受的参数，以名称指定方式被使用。

Sub [过程名]([变量名1] As [数据类型1],...[变量名n] As [数据类型n])
    语句1
    语句2
    ...
    语句3
End Sub

举例：

'声明一个过程
Sub SayHello(name As String)
    Msgbox "Hello" & name
End Sub

'在另一个过程，调用上述过程，调用时，提供一个实际的 name 参数
Sub MyCode()
    SayHello "World 2"
End Sub

调用子过程（Sub）

在程序开发中，把代码拆分成多个子过程和函数，可以使项目更容易管理、测试和运行，VBA 中也不例外。

实际开发中，项目通常具备一个主入口过程，或称为父过程。父过程通过调用多个子过程和函数，完成一系列复杂的操作。其中子过程和函数一般只负责一个操作或动作。

直接调用：

直接写过程名，即可调用过程。

Sub Main()
    MySub
End Sub

Sub MySub()
    '代码
End Sub

Sub Main()
    MySub 2019,"年"
End Sub

Sub MySub(val1 As Integer, val2 As String)
    '代码
End Sub

使用关键词Call调用：

Call 后接过程名。

Sub Main()
    Call MySub
End Sub

Sub MySub()
    '代码
End Sub

如果子过程需要输入参数，则需要将参数放在括号内。

Sub Main()
    Call MySub(2019,"年")
End Sub

Sub MySub(val1 As Integer, val2 As String)
    '代码
End Sub

提前退出过程

Exit Sub 语句：

Sub Main()
    Call MySub
    Msgbox "父过程"
End Sub

Sub MySub()
    Exit Sub
    Msgbox "子过程"
End Sub

'运行 Main 过程，返回结果：
=> "父过程"

这里需要注意的是，Exit Sub 语句只作用于当前过程，不影响调用它的父过程。

End 语句：

Sub Main()
    Call MySub
    Msgbox "父过程"
End Sub

Sub MySub()
	End
    Msgbox "子过程"
End Sub

'运行 Main 过程，返回结果：
=> 无返回结果

在实际开发中，应谨慎使用 End 结束语句。End 语句的效果类似于电脑的强制关机命令，立即结束所有程序，不会保存任何值，于 VBA 有以下效果：

• 程序中对象的各类事件不会被触发；
• 任何在运行的 VBA 程序都会停止；
• 对象引用都会失效；
• 任何打开的窗体都被关闭。

函数（Function）

函数与过程很相似，除了使用的关键词外，主要区别是，函数可以返回值。

函数基础语法

无参数函数：

Function [函数名]() As [返回值类型]
    语句1
    语句2
    ...
    语句n
    [函数名] = [返回值]
End Function

相比过程，可以看到多一个 [函数名] = [返回值] 语句，这是函数的返回值语句。函数名后制定该函数返回值的类型，语法与声明变量类似。

举例：

'声明函数，该函数随机返回 true 或 false。函数需指定返回值类型。
Function RandomLogic() As Boolean
    RandomLogic = Rnd() > 0.5
End Function

有参数函数：

Function [函数名]([变量名1] As [数据类型1],...[变量名n] As [数据类型n]) As [返回值类型]
    语句1
    语句2
    ...
    语句3
    [函数名] = [返回值]
End Function

举例：

Function Add2Number(num1 As Double, num2 As Double) As Double
    Add2Number = num1 + num2
End Function

调用函数

如果一个函数不返回值，它与子过程并无区别，其中调用方式与子过程相同。

调用有返回值的函数时，一般有两种情形：

• 一是，使用一个变量存储函数返回的值
• 二是，函数返回的值参与其他计算

举例：

Sub Main()
    '使用变量存储函数返回的值
    Dim result As Double
    result = Add(12, 345)
    
    '函数返回值继续参与计算
    Dim result As Double
    result = RandNum + Add(12, 345)
End Sub

'函数：返回一个随机值
Function RandNum()
    RandNum = Rnd * 100
End Function
'函数：返回两数的和
Function Add(num1 As Double, num2 As Double) As Double
    Add = num1 + num2
End Function

提前退出函数

Exit Function 语句：

在一个函数中，当程序运行到 Exit Function 语句时，立即结束当前函数，提前退出。

这里需要注意的是，Exit Function 语句只作用于当前过程，不影响调用它的父过程或函数。

End 语句：

在一个函数，当程序运行到 End 语句时，立即结束当前运行的所有 VBA 过程和函数。

在实际开发中，应谨慎使用 End 结束语句。End 语句的效果类似于电脑的强制关机命令，立即结束所有程序，不会保存任何值，于 VBA 有以下效果：

• 程序中对象的各类事件不会被触发；
• 任何在运行的 VBA 程序都会停止；
• 对象引用都会失效；
• 任何打开的窗体都被关闭。

函数与过程的6个不同点

声明语句不同

函数的声明语句是 Function 和 End Function，而过程的声明语句是 Sub 和 End Sub。

函数可以返回值

函数相对子过程最大的不同点是，函数可以返回指定的值。调用函数时，使用一个变量存储函数返回的值，可以在后续的代码中使用。

这里需要指出的是，函数可以不返回值，这种情况其作用与子过程相同。因此建议，不需要返回值时，直接使用子过程代替函数。

函数需指定返回值类型

VBA 中数据有多种类型，准确使用数据类型可以是程序效率更高。

同样，函数声明时也需要指定其返回值的类型。其语法与变量声明类似，在函数名后指定数据类型。

函数主体代码中，返回值赋值到函数自己

函数返回一个值，是通过在函数主体代码中，将返回的值赋值到函数自己的方法来实现。

调用函数时，使用类型与函数返回值类型相同的变量获得返回值

当主程序中调用函数获取其值时，需要使用类型与函数返回值类型相同的变量，否则程序会出错。

函数可在单元格内公式中使用

与 Excel 内置的函数一样，用户自定义编写的函数可在公式中直接使用，其用法与内置函数一样。

传递参数

带参数的子过程定义方法

子过程可以接受一个或多个参数，参数可以是常量、变量、表达式，并且每个参数指定其名称和数据类型。

看实际的例子，以下代码定义了带两个参数的一个过程，过程名是 CustomLog ，参数分别是 num 和 base。此过程的用途是计算任意底数的对数，num 是计算对数的值，base 是底数。

'声明一个带参数的子过程
Sub CustomLog(num As Double, base As Integer)
    Debug.Print Log(num) / Log(base)
End Sub

子过程按照这种方法定义后，调用时，VBA 会提示需要提供什么参数以及参数类型。

调用带参数的子过程

调用带参数的过程，只需将参数按定义顺序书写即可，多个参数使用逗号分开。

'主入口
Sub Main()
    CustomLog 100, 10
End Sub

除了按顺序书写参数外，也可以按任意顺序书写参数，但是这时需要给出参数名。带参数名的传递参数语法如下：

[参数名]:=[实际参数值]

参数名后写冒号等号(:=)，再写需传递的参数值。看实际的例子，以下三种方式是等效的。

'主入口
Sub Main()
    CustomLog 100, 10 '方式一
    CustomLog num:=100, base:=10 '方式二
    CustomLog base:=10, num:=100 '方式三
End Subxxxxxxxxxx '主入口'主入口Sub Main()    CustomLog 100, 10 '方式一    CustomLog num:=100, base:=10 '方式二    CustomLog base:=10, num:=100 '方式三End Sub

可选参数的用法

实际开发中，有时子过程的参数可能不是必须的，我们希望根据参数有无情况，执行不同的操作。针对这种情况，VBA 提供了可选参数机制。

可选参数语法：

可选参数在定义子过程时需要指定，方法是在参数名前添加 Optional 关键词。

Optional [参数名] As [数据类型]

还是以 CustomLog 子过程为例，我们把底数 base 设为可选参数。

'声明一个带可选参数的子过程
Sub CustomLog(num As Double, Optional base As Integer)
    '子过程代码
End Sub

调用时，VBA 会提示可选参数，参数放置在中括号中。

设置可选参数的默认值：

可选参数定以后，如果在子过程中使用，需要判断参数有无提供。否则未提供而直接使用时，程序会出错。

Optional [参数名] As [数据类型] = [默认值]

还是以 CustomLog 子过程为例，我们把底数 base 设为可选参数，并且默认值设为 10。

'声明一个带可选参数的子过程
Sub CustomLog(num As Double, Optional base As Integer = 10)
    Debug.Print Log(num) / Log(base)
End Sub

可选参数的位置：

当子过程有多个参数时，其中的可选参数需写在参数列表的末尾，否则 VBA 提示错误。

传参类型：ByVal 和 ByRef 的基础用法和区别

VBA 中定义过程或函数时，如果需要传递变量，需指定参数的传递类型，包括以下 2 类：

• ByVal：传递参数的值
• ByRef：传递参数的引用

基础

'ByVal 传递类型
Sub TestSub1(ByVal msg As String)

End Sub

'ByRef 传递类型
Sub TestSub2(ByRef msg As String)

End Sub

针对基础数据类型，例如数字、文本等，两种传递类型的说明和区别如下：

• ByVal：传递变量时，复制一份该变量，传入过程或函数。在过程和函数内部对该变量进行修改，只对该副本有效，对上一级过程（父过程）的变量没有影响。
• ByRef：传递变量时，将该变量的引用地址传入过程或函数。传入引用地址意味着，在过程或函数内部对其修改时，也会影响上一级过程（父过程）中的变量的值。

ByVal实例

Sub Test()

    Dim msg As String
    msg = "main"
    
    TestSub1 msg
    
    Msgbox msg

End Sub

'ByVal 传递类型
Sub TestSub1(ByVal msg As String)
    msg = "val"
End Sub

首先定义一个 msg 变量，赋值 main，然后调用 TestSub1 过程，传入 msg 变量，在过程内部对 msg 重新赋值 val。最后返回上一个过程，显示 msg 变量。结果如下，msg 变量的值没有改变。

ByRef实例

Sub Test()

    Dim msg As String
    msg = "main"
    
    TestSub2 msg
    
    MsgBox msg

End Sub

'ByRef 传递类型
Sub TestSub2(ByRef msg As String)
    msg = "ref"
End Sub

首先定义一个 msg 变量，赋值 main，然后调用 TestSub2 过程，传入 msg 变量，在过程内部对 msg 重新赋值 ref。最后返回上一个过程，显示 msg 变量。结果如下，msg 变量的值已改变。

省略传递类型：默认ByVal

默认情况下，当省略传递类型时，默认值是 ByVal，因此以下两种写法是等效的。

'指定 ByVal 传递类型
Sub TestSub1(ByVal msg As String)

End Sub

'省略传递类型
Sub TestSub1(msg As String)

End Sub

使用 ByVal 和 ByRef 传递对象

以上机制适用于传递基础类型变量，例如数字、文本、逻辑值等。

使用 ByVal 和 ByRef 传递对象时，情况有些不同。

使用 ByVal 和 ByRef 传递数组

过程或函数传递数组时，只能以引用形式传递，即以 ByRef 形式。如果尝试用 ByVal 传递数组，VBA 会提示错误。

---

作用域

变量作用域

过程作用域

在过程或函数内部声明的变量，只有在当前过程或函数内被使用。

Sub Test()

    Dim name As String
    Dim age As Integer
    
    name = "张三"
    age = 35

End Sub

以上代码中，变量 name 和 age 在 Test 过程声明，因此它们只能在该过程中内使用，包括赋值和读取。如果尝试在外部和其他过程中直接使用它们，VBA 会提示变量未定义错误。

模块作用域

一个模块中，在任何一个过程和函数外面，使用关键词 Private 或 Dim 声明的变量，称之为模块变量，其作用域是当前模块。例如，

Dim guest As String

Sub Test()

    Dim message As String
    
    guest = "张三"
    message = "你好"
    
    MsgBox message & "！ " & guest

End Sub

以上代码中，变量 guest 是在过程 Test 外面，使用 Dim 关键词声明的，称之为模块变量。模块变量的作用域是当前模块，在模块里面任何过程和函数内均可以使用。

如前文所述，使用关键词 Private 或 Dim 声明的变量，都是模块变量，因此以下两种声明方式是等效的。

Dim guest As String
Private guest As String

工程作用域

一个 Excel 工作簿是一个 VBA 工程。与之对应，工程作用域表示变量在当前工程中的模块、Excel 对象、用户窗体、类模块中均可以被使用。

工程级别变量，在所在模块顶部声明 Option Private Module 修饰语句前提下，在过程或函数外面，使用关键词 Public 声明的变量，其作用域是当前工程。例如，

Option Private Module

Public guest As String

Sub Test()

    Dim message As String
    
    guest = "张三"
    message = "你好"
    
    MsgBox message & "！ " & guest

End Sub

以上例子中，变量 guest 是使用 Public 关键词声明，是工程级别变量。它在当前工程中其他的模块中也能被使用。

全局作用域

全局作用域表示，全局变量在打开的任何一个工作簿都可以被使用。全局变量的声明方式与工程变量相似，不同点是不使用模块顶部的 Option Private Module 修饰语句。

作用域冲突

当相同名称的变量，多次以不同的作用域声明时，出现作用域冲突。这种情况，VBA 会自动以就近原则使用变量，即优先使用最近定义的变量。例如，

过程或函数作用域

模块作用域

在模块中，使用 Private 关键词声明的过程或函数，具备模块作用域，只能在当前模块中使用。

Private Sub Test()

End Sub

工程作用域

在模块中，顶部声明 Option Private Module 修饰语句，并且直接声明或使用 Public 关键词声明的过程或函数，具备工程作用域，在当前工程的所有模块中使用。

Option Private Module

Sub Test1()

End Sub

Public Sub Test2()

End Sub

以上例子中，Test1 过程和 Test2 过程均具备工程作用域。由于直接声明和使用关键词 Public 是等效的，因此可以省略 Public 关键词。

全局作用域

在模块中，直接声明或使用 Public 关键词声明的过程或函数，具备全局作用域。例如，

Sub Test1()

End Sub

Public Sub Test2()

End Sub

以上例子中，Test1 过程和 Test2 过程均具备全局作用域，可以在打开的任何一个工作簿中使用。

此外，它们还能直接在工作簿宏列表中执行。