Excel-VBA学习总结

Excel VBA 中的基本概念

1. Excel 是一个对象，这个对象包含很多属性和子对象，而 VBA 是可以操作这些对象的工具，实现各种各样的效果。例如，Excel 包括 Range 对象，即单元格对象，使用 VBA 可以改变单元格对象的填充颜色属性。

2. VBA 中的注释以英文单引号 (‘) 开头，后面接注释的内容

3. 变量的声明与赋值

   '声明一个文本类型的变量
   Dim s As String
   '给 s 变量赋值，即 "Hello World"
   s = "Hello World"
   '在 A1 单元格写入 s 变量存储的数据
   Range("A1").Value = s
   

4. 程序结构：和许多编程语言类似，具备三种结构：顺序结构、条件(选择)结构、循环结构

5. 过程和函数：过程或函数包含多行代码，是组织代码的两种方式。一般情况下，一个过程或函数只包含实现一个功能的相关代码。过程和函数都可以执行一段代码，主要区别是，执行完代码后，函数能返回一个值，而过程不能返回。
   过程是 VBA 中，程序实际运行的最小结构。单独的一行或多行代码无法运行，必须把它们放置在一个过程里，才能运行。定义一个过程的方式是

   Sub MyCode()
   	...
   End Sub
   

6. 数组：数组表示一组同类型的数据的集合，是 VBA 中最重要的概念之一。

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

7. 对象：和其他编程语言也类似，VBA也有对象的概念，工作表(Worksheet)是一个对象，它具有名称、标签颜色等属性，有添加、删除等方法。
   声明对象时，一般有两种方式。一种是前期绑定，即一开始就指定对象的类型；一种是后期绑定，即声明时不指定对象类型，后期指定。

   '语法
   '前期绑定声明语法
   Dim [变量名] As [对象类型]
   '后期绑定声明语法
   Dim [变量名] As Object
   
   '实例
   Dim sh As Worksheet
   Dim car As Object
   

8. Excel 对象模型：在上面对象一段中，说到了工作表(Worksheet)对象。其实 Excel 本身是就是一个对象，是 Excel 中的最大的对象，使用 Application 表示。Application 对象又包含工作簿(Workbook)对象，工作簿(Workbook)对象又包含工作表(Worksheet)对象，而工作表(Worksheet)对象又包含其他的子对象。

9. 常用Excel对象：

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

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

11. 用户窗体：是 VBA 代码与使用者交互的用户界面。Excel VBA 提供很多基本的窗体控件，可以制作复杂的用户界面。最典型的，Excel 中设置单元格格式的窗口界面，就是一个用户窗体。

    最基本的窗体控件包括：

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

VBA变量定义

VBA中的变量声明类似如下语句：

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

比如：

Dim name As String
Dim age As Integer

和一般编程语言类似，变量的命名有一般的规则：

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

接下来看看VBA中有哪些数据类型。主要的数据类型分为三大类：数字类型、非数字类型、通用类型

1. 数字类型
   数字类型的变量顾名思义，均是以数字变量。不同类型其数据范围有区别。

   类型	说明	数据范围
   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

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

   类型	说明	数据范围
   String	文本类型	0 至 20亿字符，文本需要用英文双引号表示，可以简写为$
   Boolean	逻辑值	True 或 False
   Date	日期和时间	时间：00:00:00 至 23:59:59 日期： 100-1-1 至 9999-12-31
   Object	对象	VBA 和 Excel 对象

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

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

   如果定义变量时没有指定具体的数据类型，则默认情况就是Variant。

   Dim [变量名]
   Dim message
   

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

   VBA 提供一个选项，可以强制变量声明，即在模块头部写上以下语句：

   Option Explicit
   

VBA常量的使用

声明常量与声明变量类似，需要指明名称和数据类型。不同点在于，常量的值在声明时就需要指定。

声明常量的语法如下：

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

VBA运算符使用

使用 VBA 开发某项功能，本质上是，对变量进行基础的运算和操作，例如加减乘除比较等。为此，VBA 提供了很多运算符和操作符，利用它们可以实现复杂的运算。运算符对编程语言也是很重要的一块内容。

VBA 运算符可以分为以下 6 类：

• 赋值运算符
• 算术运算符
• 比较运算符
• 逻辑运算符
• 连接运算符
• 其他运算符

上面有一些基本的运算符应该是都懂的，主要挑一些不一样的讲解一下：

1. 除法 /是正常的相除，左除 \是取相除后的整数部分
2. Mod表示取余，如 a Mod b

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

4. 连接操作符：VBA 中的连接运算符用于连接 2 个或多个文本。其用法与 Excel 公式中的 & 符号相同。
5. 其他操作符| 运算符 | 说明 |
   | ------------- | -------------------- |
   | _ (下划线) | 将一行代码分解成两行 |
   | : ( 英文冒号) | 将两行代码放置在一行 |

程序结构和语句

1. For Next循环
   For ... Next 循环语法如下：

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

   Sub MyCode()
   
       Dim i As Integer
       Dim sum As Integer
   
       For i = 1 To 10 Step 1
           sum = sum + i
       Next
   
   End Sub
   

2. For Each 循环

   For Each 循环用于逐一遍历一个数据集合中的所有元素。数据集合包括数组、Excel 对象集合、字典等。

   For Each 循环不需要一个数字变量，但是需要与数据集合中的元素相同的数据类型变量。其基本语法如下：

   For Each [元素] In [元素集合]
       '循环执行的代码
   Next [元素]
   

   Sub MyCode()
   
       Dim sh As Worksheet
   
       For Each sh In Worksheets
           Debug.Print sh.Name
       Next sh
   
   End Sub
   

3. Exit For 语句

   Exit For 语句用于跳出循环过程，一般在提前结束循环时使用，均适用于 For Next 循环和 For Each 循环。

   看一个实际的例子，求 1 – 10 数字的和时，当和大于 30 就停止循环。

   Sub MyCode()
   
       Dim i As Integer
       Dim sum As Integer
   
       For i = 1 To 10
   
           sum = sum + i
   
           If sum > 30 Then
               Exit For
           End If
   
       Next
   
   End Sub
   

4. Do While 循环
   Do While 循环用于满足指定条件时循环执行一段代码的情形。循环的指定条件在 While 关键词后书写。
   Do While 循环也有两种形式：

   • Do While … Loop 循环
   • Do … Loop While 循环

   Do While … Loop 循环，根据 While 关键词后的条件表达式的值，真时执行，假时停止执行。基本语法如下：

   Do While [条件表达式]
       '循环执行的代码
   Loop
   

   其中，只要 [条件表达式] 为真，将一直循环执行。[条件表达式] 一旦为假，则停止循环，程序执行 Loop 关键词后的代码。
   看一个实际的例子，还是求 1- 10 累积和。

   Sub MyCode()
   
       Dim i As Integer
       Dim sum As Integer
   
       i = 1
       Do While i <= 10
           sum = sum + i
           i = i + 1
       Loop
   
   End Sub
   

   i 变量的初始值是 1，根据 While 后的条件，只要 i 变量小于等于 10，后续的代码就可以一直循环执行。
   这里为了演示使用了 Do While 循环，实际情况下，这种求和问题，使用 For 循环更简洁。
   Do … Loop While 循环，与上一种 Do 循环不同的是，Do ... Loop While循环至少循环执行代码一次后，再判断条件表达式的值。基本语法如下：

   Do
       '循环执行的代码
   Loop While [条件表达式]
   

   其中，While 和条件表达式写在 Loop 关键词后。
   Exit Do 语句，与 Exit For 语句类似，Exit Do 语句用于跳出 Do While 循环。

5. Do Until 循环

   Do Until 循环与 Do While 循环类似。不同点在于，Do While 在条件表达式为真时，继续执行循环；而 Do Until 在条件表达式为真时，停止执行循环。

   Do Until 循环也有两种形式：

   • Do Until … Loop 循环
   • Do … Loop Until 循环

   Do Until … Loop 循环

   循环开始前判断 Until 后条件表达式的值，如果是真，停止循环；如果是假，继续执行循环。基本语法如下：

   Do Until [条件表达式]
       '循环执行的代码
   Loop
   

   Do … Loop Until 循环

   先运行一次，再判断 Until 后条件表达式的值，如果是真，停止循环；如果是假，继续执行循环。基本语法如下：

   Do
       '循环执行的代码
   Loop Until [条件表达式]
   

   其他使用方法与 Do While 循环一致。

6. If ElseIf 结构

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

7. Select Case 结构
   Select Case结构是对同一个变量进行多次判断的另一种方式。

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

变量声明和赋值

1. 数组类型变量的赋值
   数组是可以存储多个同类型元素的数据类型。声明时一般指定其数据长度。给数组赋值时，一般使用每个元素的序号。
   数组赋值基本语法如下：

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

   下面看一下实际的实例。

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

2. 对象类型变量的赋值
   VBA 中，对象是程序的一个元素，不同于基本类型数据，它包括多个属性和多个方法。
   对象类型变量赋值时，不同于基本类型变量使用 Let（可以忽略）关键词，对象使用 Set 关键词，并且 Set关键词不能省略。
   如下是对象类型变量基本的赋值方法：

   Set [变量名] = [对象类型数据]
   

   下面看一下实际的用法。

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

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

   Dim sheet As Worksheet
   Set sheet = Worksheets("绩效表")
   
   With sheet
       .Name = "旧绩效"
       .Visible = False
   End With
   

3. 日期类型赋值

   birthday = #2018-1-1#
   birthday = 43101
   birthday = "2018-1-1"
   
   time = #12:00:00#
   time = 0.5
   time = "12:00:00"
   

VBA With 结构

VBA 中，With 结构用于组合同一个对象的多个属性和方法，避免重复写同一个对象名，提高编程和运行效率。

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

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

现在看一个实际的例子，需要将工作簿中 Sheet1 工作表设置新名称，然后设置标签颜色为黑色，最后隐藏工作表。

如果不用 With 结构，代码如下：

Sub MyCode()

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

可以看到，每个语句都重复写 Worksheets("工作表名称") 部分。

使用 With 结构，可以避免重复写同一个对象名，代码如下：

Sub MyCode()

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

With 结构还能嵌套编写，即一个 With 结构中，如果父对象的属性是另一个对象，则针对这个子对象，继续使用 With 结构。

在之前的例子中，如果需要将 Sheet1 工作表中，A1:A10 单元格区域设置背景颜色，调整字体和字体大小，可以使用如下代码：

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

VBA GoTo结构

在程序运行时，使用 GoTo 结构，跳转到指定标签处运行，从而不执行 GoTo 语句和指定标签之间的代码。

GoTo 结构由 GoTo 语句和标签语句组成。基本语法如下：

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

跳转的位置由 Goto 关键词后的 [标签] 告诉程序，VBA 会在代码中查找对应的 [标签]: 关键词，从标签下一行继续执行程序。

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

下面的例子说明 GoTo 结构的基本用法。使用 VBA 作除法，如果除数是零，则跳转到程序末尾，提示除数不符合规范。

Sub MyCode()

    Dim num1 As Double
    Dim num2 As Double
    Dim result As Double
  
    num1 = 100
    num2 = 0
  
    If num2 = 0 Then GoTo error
  
    result = num1 / num2
  
    Exit Sub
  
error:
    MsgBox "除数不能为零"

End Sub

VBA 过程(Sub) 使用

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

无参数的 VBA 过程的基本语法如下：

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

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

接受参数的过程基本语法如下：

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

与无参数过程相比，有参数过程在过程名后的括号 () 中，包含一个或多个参数。参数的写法与声明变量语句类似，不同点是在这里不用写 Dim。

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

我们看一个例子。

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

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

调用子过程有两种方法，直接调用和使用 Call 关键词调用。前面可以看到直接调用不需要括号把参数括起来,而下面用Call调用则要用括号。

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

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

VBA 提供 2 种提前退出过程的方法，Exit Sub 和 End 方法。Exit Sub 语句只作用于当前过程，不影响调用它的父过程，而当程序运行到 End 语句时，立即结束当前运行的所有 VBA 过程。

VBA 函数(Function)使用

与过程不一样的是，函数有返回值。

无参数函数的基本语法如下：

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

可以看到，函数使用 Function 和 End Function 语句作为函数的开始和结束。

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

该函数的名称是 RandomLogic，返回值类型时 Boolean 类型，运行调用后，随机返回一个 true 或 false 值。实现方法是，使用 VBA 内置函数 Rnd（随机产生0-1的数字），随机数与0.5对比大小，产生 true 或 false 值，并把值赋值给函数名。

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

上述函数接受2个 Double 类型的数字作为参数，两者相加，返回和，其类型也是 Double 类型。

调用函数时，无参数函数直接书写，有参数函数将参数放在括号内。

提前退出函数的方式和过程类似，也有两种方式 Exit Function 语句和 End。Exit Function 语句只作用于当前过程，不影响调用它的父过程或函数，而End语句立即结束当前运行的所有 VBA 过程和函数。

函数与过程的不同点

1. 声明语句不同
2. 函数可以返回值
3. 函数需指定返回值类型
4. 函数主体代码中，返回值赋值到函数自己
5. 调用函数时，使用类型与函数返回值类型相同的变量获得返回值
6. 函数可在单元格内公式中使用

过程和函数的参数传递

1. 参数传递可以按顺序传递，也可以不按顺序传递，不按顺序传递时需要指明参数名：[参数名]:=实际参数值
2. 有时参数不是必须的，可以指定可选参数：Optional [参数名] As [数据类型] = [默认值]，并且可选参数要写在参数列表末尾

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：传递变量时，将该变量的引用地址传入过程或函数。传入引用地址意味着，在过程或函数内部对其修改时，也会影响上一级过程（父过程）中的变量的值。

默认情况下，当省略传递类型时，默认值是 ByRef。

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

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

使用 ByVal 和 ByRef 传递数组时，只能以引用形式传递，即以 ByRef 形式。如果尝试用 ByVal 传递数组，VBA 会提示错误。详细的用法将在介绍数组时详细说明。

VBA 变量作用域

VBA变量的作用域有 4 种：过程作用域、模块作用域、工程作用域、全局作用域。

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

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

   Dim guest As String
   
   Sub Test()
   
       Dim message As String
   
       guest = "张三"
       message = "你好"
   
       MsgBox message & "！ " & guest
   
   End Sub
   

3. 工程作用域

   Excel VBA 中，一个 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
   

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

一些对象

1. 工作簿| 对象 | 含义 |
   | -------------------- | ---------------- |
   | Workbooks("工作簿") | 选定一个工作簿 |
   | ActiveWorkbook | 当前活动工作簿 |
   | ThisWorkBook | 代码所在的工作簿 |
   | Workbooks.Add | 新建一个工作簿 |
   | Workbooks.Open(path) | 打开路径的工作簿 |
   | ActiveWorkbook.close | |

2. 工作表
   Sheets(n)	按顺序第n个工作表
   Sheetn	名字是Sheetn的工作表
   Sheets("工作表名")	按名字取工作表
   ActivateSheet	活动工作表

3. 区域和单元格
   Range("单元格地址")
   Cells(行,列)	单个单元格
   [A1]	单元格简写
   Activatecell	活动单元格
   	选择的区域

一些内置函数

文本函数

函数
Format	格式化数据，并以文本类型返回
InStr	返回指定字符的位置
InStrRev	反方向返回指定字符位置
Left	返回左侧指定长度文本
Len	返回文本长度
LCase	大写字母转换成小写字母
LTrim	清除开头的空格
Mid	返回指定的开始和结束位置之间的文本
Replace	替换文本中的指定字符
Right	返回右侧指定长度文本
RTrim	清除末尾处的空格
Space	返回指定重复数的空格文本
StrComp	返回比较两个文本的结果
StrConv	将文本转换成指定格式
String	返回指定重复数的文本
StrReverse	逆转提供的字符串
Trim	清除开头和结尾处的文本
UCase	将小写字母转换成大写字母