【Chapter 1】1宏录制方法与技巧

什么是宏和VBA

1.VBA（vb for Applications），一种编程语言。

2.通过VBA编写的一系列指令组成的程序，称之为宏。

3.通过学习VBA编写宏，有利于我们高效完成重复性的数据任务。

宏安全设置

文件-选项-信任中心-信任中心设置-受信任位置，只要是在这些路径下的Excel文档，我们都认为它是安全的。

【Chapter 2】VBA编程基础语法

绝对引用和相对引用

Excel 录制宏时绝对引用和相对引用的区别

首先，我们先认识使用相对引用命令。该命令位于【开发工具】选项卡、【代码】命令组，在录制宏命令下方。该命令具有以下两种状态：

  • 默认状态下，该命令在未启用状态，即背景色为浅色。这个时候，录制宏，将采用绝对引用的方式。
  • 点击后，命令会处于启用状态，背景色为深色。这个时候，录制宏，将采用相对引用的方式。

不使用相对引用，就是每一次执行宏都是对同一个区域进行操作；使用了相对引用，就是对一开始选定的“活动单元格”后面的相对区域，即进行一定的位置偏移，进行后续操作。

绝对引用和相对引用的区别

录制宏时，根据实际情况，工作表单元格区域将会出现以下几种位置：

  • 初始位置。录制宏开始时，当前选中的单元格。初始位置可以是单元格，也可以是单元格区域。
  • 过程位置。录制宏过程中，使用鼠标或键盘方向键选中的单元格。与初始位置类似，可以是单元格，也可以是单元格区域。

绝对引用和相对引用区别将体现在初始位置和过程位置的记录方式上。

首先，两者的相同点是，针对初始位置，两者的初始位置是相同的。Excel 只记录一个「选中的区域」的参数，不会记录选中的单元格地址信息。

两者的不同点就在于，两者对过程位置的记录方式不同；绝对引用“只知道”绝对地址；而相对引用既知道初始位置，又知道离初始位置的偏移量。

  • 绝对引用，录制宏过程中，记录选中的单元格的绝对地址信息。例如，从初始位置 A1，再选中 A10，绝对引用只记录 A10。
  • 相对引用，录制宏过程中，记录初始位置单元格和最新选中单元格离初始位置的偏移量。例如，从初始位置 A1，再选中 A10，相对引用记录 A1 和偏移量 [9, 0]。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 绝对引用
Sub AbsoluteReferenceMacro()
    ' 选中A1单元格
    Range("A1").Select
    ' 选中A10单元格
    Range("A10").Select
End Sub
        
# 相对引用
Sub RelativeReferenceMacro()
    ' 选中当前活动单元格
    ActiveCell.Select
    ' 向下移动9行，列不变（即从A1移动到A10）
    ActiveCell.Offset(9, 0).Select
End Sub

绝对引用和相对引用的使用场景

如果你的目标单元格或单元格区域，在工作表的固定位置处，不随初始位置变化而变化，那么使用绝对引用方式。例如，在固定位置冻结窗格，这种情况，初始位置无论在哪里，冻结位置始终在固定位置。

如果你的目标单元格或单元格区域，在工作表上的位置不固定，随初始位置变化而变化，那么使用相对引用方式。

运行宏的方法

  1. 自定义的快捷键
  2. 表单控件：一个按钮
  3. 快速访问工具栏

自己编写VBA代码

Sub 表示一个子程序，可以理解为一串代码的集合

MsgBox就是弹出一个弹窗，显示相应的信息

点击三角形，或者按F5，就是运行这个VBA代码

多个VBA程序及引用单元格内容

在同一个模块中，有多个子程序：

1
MsgBox "B2单元格内容是:"&Range(B2).Value

就是引用了第二个单元格的内容，会出现一个弹窗

上下有两个Sub，也叫子程序，当鼠标点在第一个子程序时，就会运行第一个；鼠标点在第二个子程序时，就会运行第二个子程序。

假如有多个模块，可能在运行的时候，需要手动选择，要运行的是哪一个代码。

运行VBA代码的另一种方法：

  1. 视图-立即窗口，在下面的小窗口中，输入“Call ＋宏的名字”，再按回车，也能运行。

    call：调用的意思

  2. 开发工具-宏-运行

变量的含义

1
2
3
Dim 变量名 As 数据类型
变量名=变量值（赋值）
MsgBox 变量名（通过窗口展示变量数据）

数据类型

数据类型 存储空间 数据范围 说明
Byte 1 字节 0 到 255 无符号整数，适用于小范围正整数。
Boolean 2 字节 True 或 False 逻辑值，只能存储 True 或 False。
Integer 2 字节 -32,768 到 32,767 有符号整数，适用于中等范围的整数。
Long 4 字节 -2,147,483,648 到 2,147,483,647 有符号长整数，适用于较大范围的整数。
Single 4 字节 -3.402823E38 到 3.402823E38 单精度浮点数，适用于带小数点的数值。
Double 8 字节 -1.79769313486232E308 到 1.79769313486232E308 双精度浮点数，适用于高精度的带小数点的数值。
Currency 8 字节 -922,337,203,685,477.5808 到 922,337,203,685,477.5807 定点数，适用于货币计算，精度高。
Date 8 字节 100 年 1 月 1 日 到 9999 年 12 月 31 日 存储日期和时间，范围从 100 年到 9999 年。
String 可变长度 0 到约 20 亿个字符（定长字符串）或 0 到约 2GB（变长字符串） 存储文本数据，分为定长字符串和变长字符串。
Object 4 字节 任何对象引用 存储对对象的引用，如 Excel 工作表、单元格等。
Variant 可变长度 根据存储的数据类型而定 可以存储任何类型的数据，但会占用更多内存。
LongLong 8 字节 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807 64 位有符号整数，仅适用于 64 位 VBA 环境。
LongPtr 4 或 8 字节 根据系统架构而定（32 位或 64 位） 指针类型，用于兼容 32 位和 64 位系统。

说明：

  1. Byte：适用于存储小范围的正整数，无符号。
  2. Boolean：只能存储 True 或 False，用于逻辑判断。
  3. Integer 和 Long：用于存储整数，Long 的范围更大。
  4. Single 和 Double：用于存储带小数点的数值，Double 的精度更高。
  5. Currency：专门用于货币计算，避免浮点数精度问题。
  6. Date：用于存储日期和时间。
  7. String：用于存储文本数据，分为定长和变长两种。
  8. Object：用于存储对对象的引用，如 Excel 中的工作表、单元格等。
  9. Variant：可以存储任何类型的数据，但效率较低。
  10. LongLong 和 LongPtr：主要用于 64 位 VBA 环境或指针操作。

注意事项：

  • 选择合适的数据类型可以提高代码的效率和内存使用率。
  • 避免过度使用 Variant 类型，因为它会占用更多内存并降低性能。
  • 在 64 位 VBA 环境中，LongLong 和 LongPtr 是常用的数据类型。

不同数据类型的变量

数据类型 说明
整数类型 当定义了一个变量为Integer，先后定义它为100，200，50000，就会弹窗为100，200，溢出，因为Integer最大值只有30000多。
小数类型代码 赋值的时候要写小数
日期类型代码 注意，日期要用两个#包围起来；弹窗显示的日期格式，会是和电脑本身日期显示的格式一样，所以如果只显示了一部分内容，就去调整本机的日期格式。
文本类型代码 要用英文的双引号
布尔类型变量 赋值的时候，会有个自动出现的框，让你自己选是true，还是false 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Sub DataTypesExample()
    ' 整数类型
    Dim num As Integer
    num = 100
    MsgBox "整数类型: " & num
    
    ' 小数类型
    Dim decimalNum As Double
    decimalNum = 3.14159
    MsgBox "小数类型: " & decimalNum
    
    ' 日期类型
    Dim myDate As Date
    myDate = #2023-10-05#
    MsgBox "日期类型: " & myDate
    
    ' 文本类型
    Dim myText As String
    myText = "Hello, VBA!"
    MsgBox "文本类型: " & myText
    
    ' 布尔类型
    Dim isTrue As Boolean
    isTrue = True
    MsgBox "布尔类型: " & isTrue
End Sub

把代码注释掉的方法：
选中代码，选择“编辑”，“编辑”块里面有个功能“设置注释块”。
同理，选择“解除注释块”就能取消注释。

多变量声明

不用每个都Dim，只需要Dim一次，后面加个逗号，新变量

1
2
3
4
5
6
7
8
9
10
11
12
Sub 多变量声明()
    Dim Score As Integer, price As Double, birthday As Date
    
    Score = 100
    price = 9.8
    birthday = #12/8/1998#
    
    MsgBox Score
    MsgBox price
    MsgBox birthday
    
End Sub

Variant可变数据类型

1
2
3
4
5
6
7
8
Sub 测试variant()
    Dim Var
    Var = 100
    MsgBox Var
    
    Var = #12/8/1998#
    MsgBox Var
End Sub

可以存储整数，也可以存储日期等

可以用，但不推荐，会浪费内存空间

强制变量声明

Option Explicit

1
2
3
4
5
6
7
8
Option Explicit

Sub OptionTest()
    Dim Score As Integer
    Score = 100
    MsgBox Scor
'变量书写错误
End Sub

若是无Option Explicit这一句，那么在输错变量名的时候，只会弹出一个“空”的弹窗，不利于纠错；加上后，就会提示，变量未定义。

若是记不住这行代码，也可以在“工具-选项-编辑器-要求变量声明”，选中“要求变量声明”，即可在新建模块时，自动加上这句代码。

变量的生命周期

变量，实际上是占用了电脑的内存空间，所以需要有“死”的时机。

变量的生命周期，只存在于这个子程序内；在其他的子程序Sub时，就获取不到这个变量了。

（Sub和End Sub之间，就是一个子程序。）

1
2
3
4
5
6
7
8
9
10
11
Option Explicit
Sub Moudle1()
    Dim Score As Integer
    Score = 100
    MsgBox Score
End Sub

Sub Moudle2()
    MsgBox Score
End Sub
'会提示“变量未定义”

若是想要让一个变量的生命周期，在多个子程序中都能使用：就在开头就定义这个变量

先运行Moudle1()，显示100；再运行Moudle2()，也显示100。

1
2
3
4
5
6
7
8
9
10
11
12
13
Option Explicit

'生命周期的范围只能是当前模块内
Dim Score As Integer

Sub Moudle1()
    Score = 100
    MsgBox Score
End Sub

Sub Moudle2()
    MsgBox Score
End Sub

若是想要让一个变量的生命周期，超过当前模块，即在多个模块中都能使用：就在开头，把Dim改成Public，这样定义这个变量。

如果Moudle1运行过再运行Moudle3，那么Moudle3的结果就是100；如果Moudle1没有运行过再运行Moudle3，那么Moudle3的结果就是0。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Option Explicit

'生命周期的范围只能是当前模块内
Public Score As Integer

Sub Moudle1()
    Score = 100
    MsgBox Score
End Sub

Sub Moudle2()
    MsgBox Score
End Sub

'=====另一个模块内
Sub Moudle3()
    MsgBox Score
End Sub

static修饰符

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Sub TestVariables()
    ' 使用 Dim 声明变量
    Dim dimCounter As Integer
    dimCounter = dimCounter + 1
    MsgBox "Dim Counter: " & dimCounter ' 每次调用都会显示 1
    
    ' 使用 Static 声明变量
    Static staticCounter As Integer
    staticCounter = staticCounter + 1
    MsgBox "Static Counter: " & staticCounter ' 每次调用会递增
End Sub

' 运行结果
' 第一次运行 TestVariables：
' Dim Counter 显示 1。
' Static Counter 显示 1。

' 第二次运行 TestVariables：
' Dim Counter 仍然显示 1（因为 dimCounter 每次都会重新初始化）。
' Static Counter 显示 2（因为 staticCounter 保留了上一次的值）。

' 第三次运行 TestVariables：
' Dim Counter 仍然显示 1。
' Static Counter 显示 3。

在VBA中，Dim 和 Static 都用于声明变量，但它们的行为有显著区别：

  • **Dim**：声明的变量是局部变量，当过程（Sub 或 Function）结束时，变量的值会被销毁。下次调用过程时，变量会重新初始化。
  • **Static**：声明的变量是静态变量，当过程结束时，变量的值会被保留。下次调用过程时，变量的值会从上一次调用结束时的值继续。

static表示的意思是，当这个程序运行完毕之后，这个变量占据的内存空间并不会消失，所以第一次执行之后初始值就还是0，结果count为1；第二次执行时count的初始值就是1，结果就是2。

如果用的是Dim，那么每次执行结束，这个变量占据的内存空间就会消失，第二次执行时，count的初始值就还是0，结果还是1。

如果担心占用内存，有两种方法可以解决：

方法①：可以单击这个“重新设置”，那么这个程序就彻底结束，即使是static修饰的变量，那么这个内存也会重新释放，再运行就是从初始值0开始；

方法②：关闭掉该工作簿，因为变量占据的内存空间，实际上是依附于这个工作簿，工作簿关闭后，内存空间肯定会被释放。

用static修饰的变量，它的生命周期会超出这个子程序的范围，所以可以用来统计这个程序用了多少次；但是在第二个子程序中仍然无法使用。

常量的使用

变量：在内存空间中，存储的数据会发生变化；常量：存储的数据不会发生变化。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Option Explicit

 '如果想要在多个模块中使用这个常量，可以加个public
'Public Const PI As Double = 3.1415926

 '如果想要让这个常量在多个子程序中都能使用，可以把这个常量的声明定义放在这个子程序外面；
'Const PI As Double = 3.1415926

Sub constant()
    '定义常量使用Const而不是Dim
    '定义常量的时候就要进行赋值
    '常量不能修改数值
    Const PI As Double = 3.1415926

Dim Price As Double
MsgBox Price
'变量的话，是有一个初始值0的
'赋值之后才会有新的值
Price = 4.5
MsgBox Price

Dim Area As Double
'计算半径为2的圆的面积
Area = PI * 2 * 2
MsgBox "半径为2的圆的面积是：" & Area

'使用常量的好处在于非常直观
'常量也有生命周期，在这个子程序中定义了这个常量，在另一个子程序中就无法使用；
'如果想要让这个常量在多个子程序中都能使用，可以把这个常量的声明定义放在这个子程序外面；
'如果想要在多个模块中使用这个常量，可以加个public

   ' VBA内置常量
    ActiveCell.Interior.Color = vbGreen
    '把活动单元格的颜色变成红色
    'vbRed和vbGreen都是常量
    '也就是内部有这么一句：Const vbRed As Integer = 255

End Sub

数学运算符

数学运算符：+加、-减、*乘、/除、^幂运算、Mod取余

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Sub OperationTest()
    Dim num1 As Integer, num2 As Integer
    
    num1 = 10
    num2 = 20
    
    '数学运算符+、-、*、/、^幂运算、Mod取余
    Dim result1 As Integer
    
    result1 = num1 + num2
    MsgBox result1
    '结果为30
    
    result1 = num1 / num2
    MsgBox result1
    '结果为0,原因是result1是整数类型，就忽略了小数部分
    '如果把result1设置为double小数类型，就能显示为0.5
    
    Dim result2 As Double
    result2 = num1 ^ num2
    MsgBox result2
    '用double的数据类型，结果是1E+20，即10的20次方，其他数据类型会显示溢出
    
    Dim result3 As Integer
    result3 = num1 Mod num2
    '10除以20，取余，还是10
    MsgBox result3
    
    
End Sub

比较运算符

用法：主要用于判断，走不同的分支（分支判断）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
Option Explicit

Sub bijiaotest()
    Dim num1 As Integer, num2 As Integer
    num1 = 10
    num2 = 20
    
    '比较运算符> < >= <= = <>
    Dim result As Boolean
    
    
    result = num1 > num2
    MsgBox result
    '结果是0，根据布尔值和integer，False就是0，True是1
    '如果Dim result As Boolean,那么就会显示为False
    
    result = num1 < num2
    MsgBox result
    '结果是-1 / TRUE
    
    
    
    '>=，大于或等于，都能成立
    result = num1 <= num2
    MsgBox result
    '结果是-1 / TRUE
    
    '=，等于，为了方便区分，可以在判断的式子里面加上括号进行区分
    result = (num1 * 2 = num2)
    MsgBox result
    '结果是-1 / TRUE
    
    '<>，不等于
    result = num1 <> num2
    MsgBox result
    '结果是-1 / TRUE
    

End Sub

补充：为什么在vba中True=-1?

当在vba中执行语句 MsgBox -1 = True 时，将弹出True。也就是在vba中True=-1，这个是为什么呢。

因为Boolean类型的变量以两个字节存储，而值只能是True 或者 False，它表示的是两个相反的状态。

其中0的二进制形式为“0000 0000 0000 0000”，与0相反的二进制形式为“1111 1111 1111 1111”，这个二进制形式刚好是-1在内存中的存储形式，所以用这两种相反的状态来分别表示False 或者 True，这样True=-1。

逻辑运算符

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
Option Explicit

Sub luojiyunsuanfu()
    Dim num1 As Integer, num2 As Integer, num3 As Integer
    
    num1 = 2
    num2 = 10
    num3 = 20
    
    '逻辑运算符And, Not, Or
    '逻辑运算符主要是针对True和False来运算的
    Dim result As Boolean
    
    'num1 > num2
    'False
    
    'num1 > num3
    'False

    'num3 > num2
    'True
    
    'num2 > num1
    'True
    
    
    'And，左右都是True，结果才是True
    result = (num2 > num1) And (num1 > num3)
    MsgBox result
    '一个True,一个False，用And，结果是False
    
    'Or，左边或右边有一个为True，结果就是True
    result = (num2 > num1) Or (num1 > num3)
    MsgBox result
    '一个True,一个False，用Or，结果是True
    
    'Not，Not False = True
    result = Not ((num2 > num1) And (num1 > num3))
    MsgBox result
    
End Sub

VBA命名规范

1. 变量命名规范

  • 使用有意义的名称：变量名应清晰描述其用途。
  • 使用驼峰命名法（CamelCase）：首个单词小写，后续单词首字母大写。
    • 例如：userName, totalCount
  • 前缀标识数据类型：使用前缀表示变量的数据类型（可选，但推荐）。
    • 例如：
      • str 表示字符串：strName
      • int 表示整数：intAge
      • dbl 表示双精度浮点数：dblSalary
      • bln 表示布尔值：blnIsActive
      • dt 表示日期：dtBirthDate
      • obj 表示对象：objWorkbook

2. 常量命名规范

  • 全部大写：常量名应全部大写，单词间用下划线分隔。
    • 例如：MAX_VALUE, MIN_VALUE
  • 使用前缀标识作用域：
    • 全局常量：g_ 前缀，例如 g_MAX_USERS
    • 模块级常量：m_ 前缀，例如 m_DEFAULT_NAME

3. 过程（Sub/Function）命名规范

  • 使用有意义的名称：过程名应清晰描述其功能。
  • 使用帕斯卡命名法（PascalCase）：每个单词首字母大写。
    • 例如：CalculateTotal, SaveDataToFile
  • 前缀标识类型：
    • Sub 过程：无需特殊前缀，例如 UpdateRecord
    • Function 过程：可以加 Get 前缀，例如 GetUserName

4. 对象命名规范

  • 使用有意义的名称：对象变量名应清晰描述其用途。
  • 前缀标识对象类型：
    • 例如：
      • ws 表示工作表：wsData
      • wb 表示工作簿：wbReport
      • rng 表示单元格区域：rngTarget
      • lst 表示列表对象：lstItems

5. 模块命名规范

  • 使用有意义的名称：模块名应清晰描述其功能。
  • 使用帕斯卡命名法（PascalCase）：每个单词首字母大写。
    • 例如：DataProcessing, ReportGenerator

6. 控件命名规范

  • 使用有意义的名称：控件名应清晰描述其用途。
  • 前缀标识控件类型：
    • 例如：
      • btn 表示按钮：btnSubmit
      • txt 表示文本框：txtUserName
      • lbl 表示标签：lblTitle
      • cmb 表示组合框：cmbCountry

7. 注释规范

  • 模块注释：在模块顶部添加注释，描述模块的功能和作者信息。

    1
    2
    3
    4
    ' 模块名称: DataProcessing
    ' 功能: 处理数据并生成报告
    ' 作者: John Doe
    ' 日期: 2023-10-05

  • 过程注释：在过程顶部添加注释，描述过程的功能、参数和返回值。

    1
    2
    3
    4
    5
    6
    ' 功能: 计算两个数的和
    ' 参数: num1 - 第一个数, num2 - 第二个数
    ' 返回值: 两个数的和
    Function AddNumbers(num1 As Double, num2 As Double) As Double
        AddNumbers = num1 + num2
    End Function

  • 行内注释：在关键代码行旁添加注释，解释代码的作用。

    1
    total = total + 1 ' 累加计数器

8. 其他规范

  • 避免使用缩写：除非是广泛接受的缩写（如 num 表示 number），否则尽量使用完整单词。
  • 避免使用保留字：不要使用 VBA 关键字（如 Dim, Sub, End）作为变量名或过程名。
  • 保持一致性：在整个项目中保持命名风格一致。

9.错误类型

  1. 以数字开头，如：Dim 1goods as string
  2. 使用特殊符号，如：dim ‘*** as string
  3. 使用空格进行分隔，如：Dim good name as string
  4. 使用特殊单词，如：Dim Dim as integer

If选择结构

有3种if语句判断的结构：1个分支；2个分支；多个分支

多个分支时注意判断的顺序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
Option Explicit
Sub IfTest()
    Dim score As Integer
    score = 95
   
   
    '第一种If语句：一个分支
    'score >=60,一个判断条件
    'then，如果符合条件要做什么
    'If开头，End If结尾
    If score >= 60 Then
        MsgBox "PASS"
    End If
    
    
    
    '第二种If语句：两个分支
    'else表示，如果不满足条件，要做什么
    If score >= 60 Then
        MsgBox "PASS"
    Else
        MsgBox "NO PASS"
    End If
    
    
    
    '第三种If语句：多个分支
    '>=90,优秀；>=80,中上；>=70,普通；>=60,及格；<60,不及格
    '注意判断的顺序，如果先判断是否大于80，再判断是否大于90，就会只显示大于80的结果
    If score >= 90 Then
        MsgBox "优秀"
    ElseIf score >= 80 Then
        MsgBox "中上"
    ElseIf score >= 70 Then
        MsgBox "普通"
    ElseIf score >= 60 Then
        MsgBox "及格"
    Else
        MsgBox "不及格"
    End If
    
    
End Sub

SelectCase选择结构

if 的好处是可以灵活设置判断条件；
selcet case 就只能对上面设置的变量值做判断

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Option Explicit
Sub SelectCaseTest()
    Dim score As Integer
    score = 7
    
    'Select Case表示基于某个变量的不同情况，进行不同的处理措施
    'Case Is：表示针对特定的情况进行处理
    'Case Else：表示如果上方的条件都不满足，该如何处理
    '只能针对某一个变量进行处理，所以应用场景比if少很多
    Select Case score
        Case Is >= 90
            MsgBox "优秀"
        Case Is >= 80
            MsgBox "中上"
        Case Is >= 70
            MsgBox "普通"
        Case Is >= 60
            MsgBox "及格"
        Case Else
            MsgBox "不及格"
    
    
    End Select

End Sub

For-Next循环结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
Option Explicit

Sub ForNextTest()
    Dim num As Integer
    
    '用法一：连续展示某个信息5次
    'num=1表示先设置num变量的值为1，只有在第一次的时候才会进行赋值，后面只会判断是否超出条件(>5)
    'to 5 表示，当num > 5 的时候，循环停止
    
    For num = 1 To 5
        MsgBox num & "：循环结构"
        'next num表示每次循环num的值加1
        'num = num + 1
        Next num
    
    
'第一次循环，Num＝1；弹出窗口，显示：1:循环结构；Num＝2
'第二次循环，Num＝2；弹出窗口，显示：2:循环结构；Num＝3
'第三次循环，Num＝3；弹出窗口，显示：3:循环结构；Num＝4
'第四次循环，Num＝4；弹出窗口，显示：4:循环结构；Num＝5
'第五次循环，Num＝5；弹出窗口，显示：5:循环结构；Num＝6；6 > 5，循环结束
    
    

    '用法二：计算1~100的求和结果
    '如果求和结果过大，可以把total改成long
    Dim total As Integer
    total = 0
        For num = 1 To 3
            total = total + num
            Next num
            MsgBox total

End Sub

Step的应用场景

控制变量在每次循环的时候，加多少的值，不加默认加1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Option Explicit
Sub StepTest()
    Dim num As Integer, total As Integer
    
    '计算1~100里面的奇数求和结果
    '1+3+5+7+……+99
    'step 2 ，就是每次next num，每次加2的意思
    '不写step 2 ，就是默认每次＋1
    For num = 1 To 100 Step 2
        total = total + num
        Next num
    
    MsgBox total
    
End Sub

如何中断For循环

Exit For：这句话作用就是满足条件时退出for循环，一般搭配 If 来用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Option Explicit
Sub StepTest()
    Dim num As Integer, total As Integer
    
    
    '让For循环中途退出
    '计算1~50相加的结果，但是前面还是写的1到100
    For num = 1 To 100
        If num > 50 Then
            'Exit For：这句话作用就是满足条件时退出for循环，一般搭配if来用
            Exit For
        End If
        total = total + num
        Next num
    
    MsgBox num
    MsgBox total
    
End Sub

嵌套for循环

大循环执行一次，小循环可能执行好几次

不建议嵌套3次for循环，一般只嵌套两次for循环

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Option Explicit

Sub ForForNextTest()
    Dim num1 As Integer, num2 As Integer
    Dim total As Long
    
  
    For num1 = 1 To 3
        For num2 = 1 To 3
            total = total + num1 * num2
        Next num2
    Next num1

MsgBox total

End Sub

DoUntil循环结构

满足条件，中断循环

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Option Explicit

Sub DoUntilTest()
    Dim num As Integer
    num = 1
    
    
    'num > 10，如果满足条件，则中断循环
    '执行10次
'    Do Until num > 10
'        MsgBox num & "：循环结构"
'        num = num + 1
'    Loop

    Dim total As Integer
    total = 0
    
    '计算1~100的数据相加的结果
    Do Until num > 100
        total = total + num
        num = num + 1
    Loop

    MsgBox total

End Sub

编写无参数子过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Option Explicit

'无参数子过程，就是指“Sub SubTest()”，括号里面没有任何的参数的子过程
'无参数子过程，可以把固定功能的代码放在一个子程序中

'子过程（子函数、子程序）
Sub SubTest()
    '包裹了一系列的指令代码，
    Dim num As Integer, total As Integer
    
    For num = 1 To 10
        total = total + num
        Next num
      
    MsgBox total
    
    Exit Sub
    '表示中断这个子程序的执行
    '后面的代码不会再执行了
  
    MsgBox total
End Sub

'比如我们想写另一个功能代码，就可以再写一个子程序，
'通过子程序/子过程，可以把不同的功能分开
'而在执行的时候，可以单击这个子程序(就是鼠标点在这个子程序里面)，就可以了

有参数子过程的编写和调用

一个参数

上面的代码，For num = 1 To 10，若是想要这个10变成一个可变的数字，可以在Sub SubTest()的括号里，声明一个参数，这个参数就是变量。所以可以改成Sub SubTest(ToNum As Integer)，此时就可以把下面的改成For num = 1 To ToNum，这时就获得了一个有参数的子过程，也就可以调用这个子过程，并且传入一个参数，那执行的时候，传入的值就会替换掉ToNum。

如何调用这种有参数的子过程？——可以再去写一个子过程Sub Test()（无参数），来进行调用。

可以多次计算，1到10,1到100,1到1000，的结果。

这样就无需修改第一部分Sub SubTest(ToNum As Integer)的代码，因为有时含有参数的子程序会非常长，若是每次都要去代码内部修改，很有可能会出错（误删代码or修改错误），风险较高；所以我们就把固定功能的代码，保持不动，只需要把参数进行调整、传入进去。

如果传入的是10，那么程序在执行第一部分的子程序时，ToNum就接收了10这个值，执行时它就是10；100和1000同理。

如果传入的是10，那么程序在执行第一部分的子程序时，ToNum就接收了10这个值，执行时它就是10；100和1000同理。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Option Explicit

Sub SubTest(ToNum As Integer)
    Dim num As Integer, total As Long
    
    For num = 1 To ToNum
        total = total + num
        Next num
      
    MsgBox total   
    Exit Sub
    MsgBox total
End Sub

Sub Test()
    SubTest (10)
    SubTest (100)
    SubTest (1000)
End Sub

有参数的子过程，什么时候去使用？

当我们写了某一个功能后，可能针对其中的内容会经常进行调整，这时可以把它设置成一个变量，把这个变量作为一个参数放到前面的sub()的括号中。

如果有不止一个参数呢？——在前面的sub()的括号中，用逗号进行分隔……

多个参数

用逗号进行分隔

比如可以更改每次增长的数值为变量AddStep，在调用这种有多个参数的子过程的时候，注意：若有多个参数，就不再用左右的括号，改用英文逗号。第一个参数的值表示ToNum的值，第二个参数表示 AddStep的值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Option Explicit

Sub SubTest(ToNum As Integer, AddStep As Integer)
    Dim num As Integer, total As Long
    
    For num = 1 To ToNum Step AddStep
        total = total + num
        Next num
      
    MsgBox total
    
    Exit Sub

    MsgBox total
    

End Sub

Sub Test()
    'SubTest (10)
    SubTest 100, 2
    SubTest 1000, 2
End Sub

子程序的调用？

无参数的子程序调用方法：

  1. 在编写程序的界面，直接单击运行；
  2. 在“开发工具”-“宏”，选择对应的宏程序，单击运行；
  3. 设置快捷键运行；
  4. 添加表单，即一个按钮来运行；
  5. 快速访问工具栏；
  6. 在编写程序的界面，“立即窗口”。
  7. 单独再去写一个（无参数）子程序，去调用无参数的子程序；

有参数的子程序调用方法：

  1. 单独再去写一个（无参数）子程序，去调用有参数的子程序；

宏录制？

宏录制时编写的程序，都属于无参数的子程序；有参数的子程序，必须手动编写，无法录制宏。

编写自定义函数：Function表示函数过程

函数和子过程的区别——函数过程有返回值，子过程没有返回值。

function过程类似于Excel内置的工作表函数，主要有2个用途：

①在工作表公式中使用，弥补 Excel 内置西数无法实现的计算功能，简化公式的复杂度。

②在VBA 中被其他过程调用，或者作为表达式的一部分参与运算。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Option Explicit

'Function表示函数过程，而不是子过程
'这里的功能是计算两个数的立方值的和，返回给CubeSum
Function CubeSum(x As Double, y As Double)
    CubeSum = x * x * x + y * y * y
End Function

Sub Test()
'方法一：赋值给一个新变量
    Dim value As Double
    value = CubeSum(1.5, 2.5)
    MsgBox value

'方法二：不用定义新变量，直接弹窗出结果
    MsgBox CubeSum(1.5, 2.5)
End Sub

'对三个值进行求和
Function MySum(x As Integer, y As Integer, z As Integer)
    MySum = x + y + z
End Function

上面我们自定义的函数，如何在Excel中使用？

  1. 在单元格内输入=CubeSum(A1,A2)，就可以求出 A1单元格值的立方＋A2单元格值的立方的和。
  2. “插入函数”-“选择分类”-“用户定义”-“确定”，就可以去自定义设定函数的参数了，再单击“确定”。

还可以设置返回值的类型

1
2
3
4
Function CubeSum(x As Double, y As Double) As Double
    CubeSum = x * x * x + y * y * y
End Function
'这里是让返回值的数据类型是Double

一维数组

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Option Explicit

Sub ArrayTest()
    '声明定义一维数组
    Dim MyArray(0 To 2) As Integer
    
    '为一维数组赋值
    MyArray(0) = 0
    MyArray(1) = 100
    MyArray(2) = 200
    
    '访问某一个模块中的数据
    MsgBox MyArray(2)
    
    '遍历方式访问一维数组
    Dim index As Integer
    For index = 0 To 2
        MsgBox MyArray(index)
        Next index
        
    'LBound表示数组的最小的角标
    'UBound表示数组的最大的角标
    'lower,upper
    '这种方法的优点在于，如果后期数组增级了个数，那么遍历数组的时候，就不需要修改循环的条件了
    For index = LBound(MyArray) To UBound(MyArray)
        MsgBox MyArray(index)
        Next index
    
    
End Sub

Option Base

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Option Base 1
Option Explicit

Sub ArrayTest2()
    '声明定义一维数组
    '若没有指定最小的角标，就默认最小角标为0;我们设置的数字，就是最大的角标
    'Dim MyArray(3) As Integer就相当于Dim MyArray(0,3) As Integer
    '========
    '如果加上了Option Base 1，就表示Dim MyArray(3) As Integer是从1开始，相当于Dim MyArray(1,3) As Integer
    '这时那么如果有MyArray(0) = 100在，就会报错“下标越界”，必须删去or注释掉这一行

    Dim MyArray(3) As Integer
    
    'MyArray(0) = 100
    MyArray(1) = 200
    MyArray(2) = 300
    MyArray(3) = 400
    
    Dim index As Integer
    For index = LBound(MyArray) To UBound(MyArray)
        MsgBox MyArray(index)
        Next index
               
End Sub

二维数组

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
Option Explicit

Sub ArrayTest()
    '定义二维数组
    '前面是行数，后面是列数
    Dim MyArray(1 To 2, 1 To 3) As Integer
    
    '为二维数组赋值
    MyArray(1, 1) = 100
    MyArray(1, 2) = 200
    MyArray(1, 3) = 300
    MyArray(2, 1) = 400
    MyArray(2, 2) = 500
    MyArray(2, 3) = 600

    '访问二维数组中某一个空间中的数据
    MsgBox MyArray(1, 3)
    
		'遍历二维数组中的数据，方法：大循环嵌套小循环
    Dim rowno As Integer, cono As Integer
    For rowno = 1 To 2
        For cono = 1 To 3
            MsgBox MyArray(rowno, cono)
            Next cono
        Next rowno
    
    
    'LBound(MyArray, 1):行的最小角标
    'UBound(MyArray, 1):行的最大角标
    '这里的1和2，与角标从几开始没关系（所以如果角标是从0开始，这里也还是1和2）
    Dim rowno As Integer, cono As Integer
    For rowno = LBound(MyArray, 1) To UBound(MyArray, 1)
        'LBound(MyArray, 2):列的最小角标
        'UBound(MyArray, 2):列的最大角标
        For cono = LBound(MyArray, 2) To UBound(MyArray, 2)
            MsgBox MyArray(rowno, cono)
            Next cono
        Next rowno
    

End Sub

动态数组

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
Option Explicit

Sub MyDynArray()
    '定义一个一维动态数组，括号空的
    Dim MyDynArray() As Integer
    
    'redim:明确动态数组的最小角标和最大角标
    ReDim MyDynArray(1 To 3)
    
    '为一维动态数组进行赋值
    MyDynArray(1) = 100
    MyDynArray(2) = 200
    MyDynArray(3) = 300
    
    'for循环，遍历数组
    Dim index As Integer
    
'    For index = LBound(MyDynArray) To UBound(MyDynArray)
'        MsgBox MyDynArray(index)
'        Next index
    
    '可以通过redim多次调整角标
    '又一次使用了redim，对这个数组的角标进行了调整，调整成了1-4;
    '注意！！！redim调整动态数组的时候，之前的赋值都会自动抹除，即为0
    '

    ReDim MyDynArray(1 To 4)
    
    '如果需要保留之前的赋值，就在redim后面加一个Preserve
    '加一个Preserve，就可以保留历史的赋值数据
    '如果要运行查看效果，需要将上面未加preserve的代码注释掉
    
    ReDim Preserve MyDynArray(1 To 4)
    
    
    
    
    '如果只对4位置进行赋值400，那么位置1-3的值就会是0
    MyDynArray(4) = 400
    
    For index = LBound(MyDynArray) To UBound(MyDynArray)
        MsgBox MyDynArray(index)
        Next index
    
    
End Sub

【Chapter 3】面向对象和三

理解面向对象（入门）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Option Explicit

Sub ObjectTest()
    '内容
    '设置A1单元格的数值
    'Range("A1"):表示的就是A1单元格（就是一个对象，这个对象有相关的一些属性，比如Value）
    'Value就是A1单元格的值
    '可以理解成Value就是一个变量，下面就是给这个变量赋值，只不过这个变量归属于A1单元格
    '通过设置Range对象属性的值，从而设置了A1单元格的内容
    Range("A1").Value = 200
    
    '展示A1单元格的值
    '展示时可以把上面的代码注释掉
    MsgBox Range("A1").Value
    
    '方法
    '运行时可以把上面的代码注释掉
    '选中+清除内容
    Range("A1").Select
    Range("A1").Clear
    
    
    
End Sub

对象，包含属性和方法；
属性：对于这个对象的描述信息；
方法：可以对这个对象做什么

但是实际上我们很少进行区分，可以这么理解：对于属性来说，可以理解为一个变量，但是实际情况可能会复杂一些，比如Font和Interior实际上还是一个对象，也就是说对象可以包含对象；而Font对象又有一些其他的属性和方法来进行操作。

Range对象，单元格区域（不止一个单元格）

属性：

  1. Value——单元格区域内的值
  2. Count——单元格区域内，有多少个单元格
  3. Font——单元格区域内，对字体进行设置
  4. Interior——单元格区域内，对填充颜色进行设置

方法：

  1. Select——通过代码，选中指定的单元格区域
  2. Copy、Paste——把指定区域的内容，复制粘贴到另一块地方
  3. Clear——把指定区域的内容清除掉
  4. Delete——把区域单元格内容删除掉

clear和delete的区别：

Range.Delete实际上删除了相应的范围并相应地移动/移动了单元格，从而改变了工作表的结构。如果您选择单元格，然后单击鼠标右键以删除所选内容，则情况相同。系统会询问您希望如何移动单元格。

Range.Clear将仅清除内容和格式，但不会删除单元格，也不会更改工作表的结构。

还有仅清除内容但保留格式的Range.ClearContents、仅清除单元格格式的Range.ClearFormats以及Range.ClearComments、Range.ClearHyperlinks、Range.ClearNotes和Range.ClearOutline

1
2
3
4
5
6
7
8
9
10
Sub ObjectTest()
		'选中
    Range("A1").Select

		'仅清除内容和格式，但不会删除单元格，也不会更改工作表的结构。
    Range("A1").Clear

		'清除内容和格式，同时删除单元格
    Range("A1").Delete
End Sub

对象层级框架

万物即对象（橙色的对象更常用）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
Option Explicit

Sub ObjectHierarchy()
    '对象层次访问
    Application.Workbooks("练习VBA用.xlsm").Worksheets("Sheet1").Range("A1").Value = 1000
    '执行后，对应单元格就会有1000的值


    '上面的写法较为繁琐
    '
    '简略写法
    '可以省略Application：因为我们现在正在Excel的环境中进行编程；如果上面的代码只省略Application，也是可以正常运行的
    '可以省略Workbooks：因为我们当前就是对这个工作簿进行操作的
    '可以省略Worksheets：因为当前活动的工作表就是sheet1

    '省略哪个就认为这个内容是活动的，比如工作簿和工作表
    '
    '如果切换了工作簿/工作表，就默认在当期的工作簿/工作表中运行

    '如果要指定工作簿/工作表，就不能忽略

    Range("A2").Value = 300


    'Workbooks和Worksheets，都有s，万物皆对象，表示有多张工作簿/工作表，是集合对象；
    '类似数组，可以把多个变量统一进行管理
    '集合同理，把多个工作簿/工作表统一进行管理，是对象，就有属性和方法

    '属性
    Range("C2").Value = 999
    '方法
    Range("A1").Clear
    
    '集合也是对象，也有属性和方法
    '属性:显示sheet1的名字
    MsgBox Worksheets("Sheet1").Name
    '方法：新增一个工作表
    Worksheets.Add
    
    
    
End Sub

单元格引用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
Option Explicit

Sub CeilRefTest()
    '单元格引用

    '引用某个单元格
    '如果没有加“.Value”，也可以对它进行赋值；
    '说明“.Value”是它默认的属性，不加上实际上也就是加了“.Value”，可以对这个单元格进行赋值
    Range("A1").Value = 100
    Range("A2") = 500

    '引用多个单元格
    '同时引用了这3个单元格，并进行了数据的传输：赋值
    Range("A3,C3,E3").Value = 666


    '引用某个单元格区域（连续）
    Range("A5:C8").Value = 456

    '引用某些单元格区域（不连续）
    Range("A10:C11,E12:G14").Value = 400

    '引用单元格区域，带有名称的
    '先选中一个区域，然后在显示位置如“C18”的地方，输入一个名称，如“TEST123”，即可完成对某个单元格区域的名称
    Range("TEST123").Value = 999

    '---------------分割线------------------
    '---------------分割线------------------

    '行和列的引用

    Range("26:26").Value = 600 '整个26行赋值600
    Range("J:J").Value = 800 '整个J列赋值800

    '引用多行(不相连的行)
    Range("27:27,29:29,33:33").Value = 900

    '引用多列(不相连的列)
    Range("L:L,G:G,P:P").Value = 35
    
    '引用多行(相连的行)
    Range("32:35").Value = 77
    
    '引用多列(相连的列)
    Range("F:H").Value = 35
    
    '引用多行(相连的行)
    Rows("41:43") = 123
    
    '引用多列(相连的列)
    Columns("A:C").Value = 555
    
End Sub

Cells和Offset属性

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Option Explicit

Sub CellRefTest()
    'Cells属性
    '可以通过数字来决定我们访问的是哪个单元格，可以结合变量相关知识，灵活引用单元格
    
    '引用工作表中的某个单元格
    'Cells(5, 2):表示第五行，第二列，列用数字表示而不是字母
    Worksheets("Sheet2").Cells(5, 2) = 100
    
    '引用单元格区域
    'Range(,)，里面加上左上角的单元格位置，和右下角的单元格位置
    Range(Cells(8, 2), Cells(13, 5)) = 200
    
    '右上角和左下角的也可以
    Range(Cells(18, 5), Cells(25, 2)) = 300

    '引用单元格区域中的某个单元格
    'Cells(3, 2)表示的是"B8,E13"这个区域中的第3行第2列，给它赋值400
    Range("B8,E13").Cells(3, 2) = 400

    'Offset属性
    '宏录制，相对引用的时候遇到过
    'offset可以控制选择的单元格的“偏移量”,移动了多少位置
    '往右和往下是正数，往左往上是负数
    
    Range("B15").Offset(3, 2).Value = 500
    Range("H15").Offset(-3, -2).Value = 500

    
    
End Sub

Select和Activate方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
Option Explicit

Sub CellRefTest()
    'Range
    'Range("G9").Select   '选中了G9这个单元格
    'Range("G10:I15").Select  '选中了G10到I15这个区域的单元格
    
    'Activate
    '按Tab键可以在输入.之后，输入几个字母，不用全部输完，快速选中你想要的那个；enter会换行，Tab不会
    'Range("G9").Activate
    
    'Range("G10:I15").Activate
    '好像是只选中了G9这个单元格，和G10到I15这个区域；好像和select一样
    
    
    
    'Select和Activate的区别：
    '活动单元格，就是选中了很多个单元格之后，这些单元格会变成灰色；活动单元格就是其中白色的那个
    '某个区域的活动单元格，可以用Tab, enter, shift + tab, shift + enter进行上下左右的移动
    '下面的代码运行后，就是先选中了G9:J14的这个区域，然后将H10作为了活动单元格
    
    Range("G9:J14").Select
    Range("H10").Activate
    
    '引用选择的单元格/单元格区域
    Selection.Value = 100
    
    '引用选择的单元格
    ActiveCell.Value = 200
    
    '就是把上面选中的单元格区域，赋值为100；除了活动单元格H10，这个赋值为200
    
    
    
    '可以通过在上方工具栏右击出现想“调试”，点击“逐语句”，再点击“逐过程”（或者快捷键shift + F8），查看代码按每一句执行的过程，观察每一行代码到底产生了什么影响
    '这里的代码就是：先选中G9:J14这个区域，再选中了H10这个活动单元格；
    '然后把G9:J14这个区域里面每个单元格（包括H10）赋值100，再对H10这个活动单元格赋值200
    
End Sub

Range对象的End属性

假设有一块区域的数据，我们点击其中的某一个有数据的单元格，然后快捷键：Ctrl + ↓ ，到达了这块有数值区域最下方的单元格；同理，Ctrl + ↑，Ctrl + ←，Ctrl + →，可以到达这块单元格区域的边缘 or 最上/左/右的单元格。

如果想要选中当前单元格到最下面的单元格，Ctrl + Shift + ↓，可以选中；可以先Ctrl + Shift + ↓，紧接着就Ctrl + Shift + →，可以连续选中多行多列的内容，可以灵活选择目前这块区域中的某一块区域。

这种操作在代码中如何实现？

1
2
3
4
5
6
7
8
9
10
11
12
Option Explicit

Sub CellRefTest()
'End属性模拟"Ctrl＋方向键"的效果
'选中H10最往下再最往右的单元格
Range("H10").End(xlDown).End(xlToRight).Select

'End属性模拟"Ctrl＋Shift + 方向键"的效果
'Range(A,B),表示从A到B中间所有的单元格
'选中H10开始，到H10最往下再最往右的单元格，这一块区域的单元格
Range("H10", Range("H10").End(xlDown).End(xlToRight)).Select
End Sub

Range对象_剪切复制粘贴

Range对象的常用操作很重要，因为我们在Excel里面基本上都是在对单元格进行一些操作，要讲这些操作如何通过VBA实现。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Option Explicit

Sub RangeTest()
    Range("A1:A10").Value = 100
    
    '剪切+粘贴
    '.Cut之后写一个空格，会有一个destination，意思表示我们剪切后粘贴到哪里
    Range("A1:A5").Cut Range("C2")
    '将A1到A5单元格里面的数字剪切后，粘贴到C2去了；因为有5个单元格，所以C2-C6都有了100的值
    
    '复制+粘贴
    '将A6到A10单元格里面的数字复制后，粘贴到E3去了；因为有5个单元格，所以E3-E7都有了100的值
    Range("A6:A10").Copy Range("E3")
    
End Sub

Range对象_设置单元格公式

1
2
3
4
5
6
7
8
9
10
11
12
Option Explicit

Sub RangeTest()
    '在G列，计算出计算1-10行的A列-E列对应单元格的求和
    'G1是A1到E1的求和
    'G2是A2到E2的求和
    'G3是A3到E3的求和
    'G4是A4到E4的求和
    '………………
    Range("G1:G10").Formula = "=SUM(A1:E1)"
    
End Sub

Range对象_选择性粘贴

选择性粘贴：粘贴的时候先右键，可以选择“粘贴为值”，“粘贴为公式”……

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Option Explicit

Sub RangeTest()
    Range("G1:G10").Copy
    Range("J1").PasteSpecial xlPasteValues '粘贴为值
    '运行后在J列的单元格内就有粘贴的值，但是G列还是一个被选中的状态
    '想要去除掉选中的状态，可以进行下面的操作
    
    Application.CutCopyMode = False
    'CutCopyMode，就是剪切或者复制的模式，设置成FALSE，也就是取消这种效果
    '因为这种选中的状态（虚线/蚂蚁线），只有在剪切或者复制的时候才会出现
    
    Range("J10").Select
    '选中J10的单元格，这样就不是选中上方的单元格了

End Sub

Range对象_设置字体效果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Option Explicit

Sub ZiTiTest()
    '把A1到A10写上字“文本”
    Range("A1:A10").Value = "文本"
    
    'font，字体，也是对象
    '运行下面的代码时，推荐用“逐语句/逐行”观察效果
    
    '设置字体
    Range("A1:A10").Font.Name = "黑体"
    
    '设置大小
    Range("A1:A10").Font.Size = 15
    
    '加粗
    Range("A1:A10").Font.Bold = True
    
    '倾斜
    Range("A1:A10").Font.Italic = True
    
    '颜色
    '方法一：(输入vb两个字母后，可以用快捷键【Ctrl + J】辅助
    Range("A1:A10").Font.Color = vbRed
    
    '方法二：用RGB值，3个值，每个值都可以设置0-255之间
    Range("A1:A10").Font.Color = RGB(156, 191, 72)
    
    
    
End Sub

通过文档查看对象属性和方法

不用死记硬背。工具栏这里有个“对象浏览器”，也可以在“视图”下方找到，快捷键是F2；

Range 对象 (Excel)

【所有库】这里选择“Excel”；

【类】就是“对象”的意思，在“类”下面，找到Range：右侧是灰色手指图标的，表示这是“属性”；绿色图标，表示这是“方法”。（可以在这里找到所有的对象和方法，但是不需要全部完全掌握；如果英文看不懂，可以用“帮助”，按下F1，如果可以的话，会出现一个微软的网站，会有写教程；如果打不开，有一个微软的帮助文档，找到Obejct model，单击再点键盘上的“R”，找到Range Object，单击打开并单击Range Members，就能找到找到每一个属性和方法的介绍，都是英文的，每一个点进去都有示例代码方便理解）

如果不知道该用什么属性和方法，可以用“宏录制”来辅助，或者去参考文档Excel 2013 Developer Documentation.chm，或者去微软官网，看下应该使用什么属性和方法。

With语法使用方法

之前学过，设置字体属性的时候，可以用Range("A1:A10").Font.****来设置相应属性，但是若有多个属性需要同时设置，多行写Range("A1:A10").Font.很浪费时间，可以通过With语句，把这个操作变得更加简单。

优点：不用每一次都在前面写上相应的对象，而是把对象固定好之后，紧接着再编写相应的属性。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Option Explicit

Sub ZiTiTest()
    Range("A1:A10").Value = "文本"
'不用With语句
'    Range("A1:A10").Font.Name = "黑体"
'    Range("A1:A10").Font.Size = 15
'    Range("A1:A10").Font.Bold = True
'    Range("A1:A10").Font.Italic = True
'
'    Range("A1:A10").Font.Color = vbRed
'    Range("A1:A10").Font.Color = RGB(156, 191, 72)
    
'用With语句
'逐语句执行比较直观
    With Range("A1:A10").Font
        .Name = "微软雅黑"
        .Size = 15
        .Bold = True
        .Italic = True
        .Color = RGB(156, 191, 72)
        
    
    End With
    '别忘了End With
    
End Sub

Range对象_设置边框线

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Option Explicit

Sub rangetest()

    Range("A1:A10").Value = "文本"
    
    '对象：Borders——边框线
    With Range("A1:A10").Borders
        '按下xl之后，要按快捷键Ctrl + J
        .LineStyle = xlDouble   '边框线样式：双线条边框线
        .Color = vbRed          '边框线颜色：红色
        .Weight = xlThick       '边框线粗细：粗
        
    End With
    
End Sub

Range对象_设置填充颜色

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Option Explicit

Sub rangetest()
    '对象/属性：Interior
      
    '使用VB内部的颜色
    Range("A1:A10").Value = "文本"
    Range("A1:A10").Interior.Color = vbGreen
    
    '使用RGB自定义颜色
    Range("A11:A20").Value = "填充颜色"
    Range("A11:A20").Interior.Color = RGB(0, 177, 236)
    
End Sub

Range对象_设置对齐方式

可以设置两种对齐方式：水平方向上的 AND 竖直方向上的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Option Explicit

Sub rangetest()
    Range("A1:A10").Value = "文本"
    '逐行运行的时候，可以拉高行号辅助观察效果
    '输入xl后，可以快捷键【Ctrl + J】辅助输入
    
    With Range("A1:A10")
        .Font.Size = 13
        
        '在水平方向上的对齐方式：靠右
        .HorizontalAlignment = xlRight
        
        '在竖直方向上的对齐方式：靠底部
        .VerticalAlignment = xlBottom
        
    End With
End Sub

Range对象_设置缩进大小

1
2
3
4
5
6
7
8
9
10
11
12
13
Option Explicit

Sub RangeTest08()
    Range("A1:A10").Value = "文本"
    
    '左对齐，缩进值为2
    With Range("A1:A10")
        .HorizontalAlignment = xlLeft
        .InsertIndent 2
    End With

End Sub
'如何检查缩进值？看“单元格格式”里面的“对齐”，就是靠左缩进，缩进值为2。

Range对象_设置自动换行

1
2
3
4
5
6
7
8
9
10
Option Explicit

Sub RangeTest56()
    '在E1单元格，输入一些较长的文本
    Range("E1").Value = "学习VBA设置565656自动换行"
    
    '在E1单元格，实现换行的效果
    Range("E1").WrapText = True
    
End Sub

Range对象_合并单元格

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
Option Explicit

Sub RangeTest45()
    '合并单元格A1-A10，到A1中，那么A2-A10的内容就消失了
    'Merge是合并的意思，合并的时候会弹窗说“合并单元格时，仅保留左上角的值，而放弃其他值。”
    'Range("A1:A10").Merge
    
    'UnMerge表示：取消合并单元格，执行时可注释掉上面一行的代码
    'Range("A1:A10").UnMerge
    
    
    
    '如果想要合并，并且保留其他单元格的内容
    '就用一个for循环，把A1-A10的内容全部连接到一个字符串上
    '再把这个字符串输出到A1里面
    '再合并单元格，舍弃其他单元格的值
    Dim Num As Integer
    Dim txt As String
    
    '为了方便观察，可以在“视图”里面，打开监视窗口，并且把变量txt【添加监视】
    
    
    For Num = 1 To 10
    
        'txt = txt & Cells(Num, 1) & "-"
        '这样出来，字符串最后会有一个短横线，比如【文本-文本-文本-文本-文本-文本-文本-文本-文本-文本-】
        
        '如果不想要这个短横线
        '就可以在循环的时候，加一个判断的语句
        '如果是第10个单元格，末尾不加短横线
        '如果不是第10个单元格，通过短横线，进行连接分隔
        If Num = 10 Then
            txt = txt & Cells(Num, 1)
        Else
            txt = txt & Cells(Num, 1) & "-"
        End If
        
    Next Num
    
    
    
    
    Range("A1") = txt
    
    Range("A1:A10").Merge
    
    
End Sub

Range对象_阻止Excel提醒弹窗出现

比如执行上面一节的代码的时候，会出现一个弹窗【合并单元格时，仅保留左上角的值，而放弃其他值。】此时单击“确定”，就会只保留左上角的值。

如果不想要弹窗出现，可以用Application这个对象

1
2
3
4
5
6
7
8
Application.DisplayAlerts = False
'把这一句写在代码前半部分
'就是不要弹窗出现的意思
'即“阻止Excel通过弹窗进行提醒”

Application.DisplayAlerts = True
'再把这句写在End Sub之前，就代表
'在这个Sub以内，就是只是在这个程序中阻止弹窗出现

Range对象_设置数字格式

1
2
3
4
5
Sub RangeTest12()
    Range("G1:G10").Value = 100000
    Range("G1:G10").NumberFormat = "￥#,##0.00"
    '如果不知道数字格式怎么用代码写，就去查吧
End Sub

Range对象_插入删除行和列+删除/清除单元格

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
Sub RangeTest13()
    Range("A1:N30").Value = 100

    '插入单行
    '方法一：在第3行的上方，插入一行
    'Range("3:3").Insert
    '方法二：在A5单元格上方，插入1行
    '就是在A5单元格对应的这一行的上方，插入一行
    'Range("A5").EntireRow.Insert

    '插入单列
    '方法一：在C列的左侧，插入了一列
    'Range("C:C").Insert
    '方法二：在E1单元格左侧，插入1列
    '就是在E1单元格对应的这一列的左侧，插入1列
    'Range("E1").EntireColumn.Insert
    
    '删除单行
    '删除第3行
    'Range("3:3").Delete
    '删除A5这一行
    'Range("A5").EntireRow.Delete
    
    '删除单列
    'Range("C:C").Delete
    'Range("E1").EntireColumn.Delete
    
    '==============分割线=======================
    '插入多行
    'Range("A3:A5").EntireRow.Insert
    '效果就是3.4.5行新增了3行空白行
    'Rows("9:11").Insert
    '效果就是9.10.11行新增了3行空白行
    
    '插入多列
    'Range("C1:E1").EntireColumn.Insert
    'Columns("G:I").Insert
    
    '删除多行(下面的行会自动补上)
    'Range("A3:A5").Delete '这样只会删除这3个单元格
    'Range("A3:A5").EntireRow.Delete
    'Rows("6:8").Delete
    
    
    '删除多列(右边的列会自动补上)
    'Range("C1:E1").EntireColumn.Delete
    'Columns("D:F").Delete
    
     
     '==============分割线=======================
     '删除单元格(删除后右侧内容会自动补上)
     'Range("B3:D10").Delete
    
     '删除单元格(删除后，不希望其他内容移动，也就是清除内容，可以用clear)
     Range("F5:I10").Clear
    
    
End Sub

Range对象_设置行高与列宽

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sub RangeTest14()
    '设置单行行高
    Range("A1").RowHeight = 30
    
    '设置多行行高
    Range("A3:A5").EntireRow.RowHeight = 40
    Rows("7:12").RowHeight = 50
    
    
    '设置单列列宽
    Range("C1").ColumnWidth = 20
    
    '设置多列列宽
    Range("D1:F1").EntireColumn.ColumnWidth = 30
    Columns("H:J").ColumnWidth = 20
    

    '设置列宽行高自适应
    Rows("1:10").AutoFit
    Columns("A:J").AutoFit
  
End Sub

Range对象_隐藏和取消隐藏 行和列

1
2
3
4
5
6
7
8
9
10
Sub RangeTest15()
    '隐藏多行
    Range("A3:A5").EntireRow.Hidden = True  
    '隐藏多列
    Range("G1:I1").EntireColumn.Hidden = True
    
    '显示行和列，即取消隐藏行和列  
    Range("A3:A5").EntireRow.Hidden = False
    Range("G1:I1").EntireColumn.Hidden = False
End Sub

Range对象_清除单元格格式及内容

方法 功能描述
ClearContents 清除单元格内容，保留格式、注释等。
ClearFormats 清除单元格格式，保留内容。
Clear 清除单元格的所有内容、格式、注释等。
ClearComments 清除单元格的注释，保留内容和格式。
ClearHyperlinks 清除单元格的超链接，保留内容和格式。 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Sub ClearRangeExample()
    ' 选中 A1 到 B2 的单元格区域
    Dim rng As Range
    Set rng = Range("A1:B2")
    
    ' 清除内容
    rng.ClearContents
    MsgBox "内容已清除。"
    
    ' 清除格式
    rng.ClearFormats
    MsgBox "格式已清除。"
    
    ' 清除注释
    rng.ClearComments
    MsgBox "注释已清除。"
    
    ' 清除超链接
    rng.ClearHyperlinks
    MsgBox "超链接已清除。"
    
    ' 清除所有
    rng.Clear
    MsgBox "所有内容、格式、注释、超链接等已清除。"
End Sub

Sheets对象_Sheets对象的基本使用

针对工作表的操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Sub SheetsTest()
    '写名称：Sheet2是工作表的名称
    '【更推荐写名称这个，具有唯一性，不会因为sheet表的移动而发生改变】
    'Worksheets("Sheet2").Range("A1") = 100

    '按顺序：第3张工作表
    'Worksheets(3).Range("B2").Value = 1000
    
    
    Sheets("Sheet2").Range("B2").Value = 5000
    Sheets(2).Range("C2").Value = 7999
    
    '好像sheets和Worksheets一样，但是他们是不一样的
    'Worksheets：表示的就是普通的工作表
    'Sheets：既可以表示包含数据的工作表，也可以表示这个插入图表的工作表
        '就是在Sheet表那里，点击插入，可以插入的是Chart图表
        '实际操作中，用Sheets比较方便
        
        
    '选择工作表，
    '效果类似从别的工作表跳到这个工作表里面
    Sheets("Sheet4").Select
    '在A10输入值
    Range("A10").Value = 9999
    
    
End Sub

Sheets对象_添加工作表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Sub SheetsTest2()

    'Sheets.Add
    '若当前活动的工作表是“Sheet2”，执行这个代码后，会在当前工作表的左侧，也就是前面，插入一个新的工作表
    
    
    
    '若是想要明确指定插入的工作表是某张工作表的前/后面
    
    '比如想要在工作表Sheet4后面插入一个工作表
    'Sheets.Add after:=Sheets("Sheet4")

    '在sheet1前面插入一个工作表
    'Sheets.Add before:=Sheets("Sheet1")

    '弹窗显示第一个sheet表的名称
    'MsgBox Sheets(1).Name

    '把第2个工作表的名称改成“添加工作表2”
    'Sheets(2).Name = "添加工作表2"

    '在添加工作表时，设置好添加的工作表的名称
    '即在指定位置，添加一个指定名称的Sheet表
    '在sheet6前面，插入一个名称为“指定名称的工作表”的工作表
    Sheets("sheet6").Select         '这一步会跳到Sheet6里
    Sheets.Add.Name = "指定名称的工作表"        '这一步会在Sheet6前面插入一个有名称的工作表
    
End Sub

Sheets对象_移动工作表

1
2
3
4
5
6
7
8
9
Sub SheetsTest3()
    '把“添加工作表”这个sheet移动，放在“指定名称的工作表”之前
    Sheets("添加工作表").Move before:=Sheets("指定名称的工作表")
    
    '把“添加工作表”这个sheet移动，放在“Sheet4”之前
    Sheets("添加工作表").Move after:=Sheets("Sheet4")
    
    
End Sub

Sheets对象_复制工作表

1
2
3
4
5
6
7
8
9
Sub SheetsTest4()
    '既然要复制，那还需要指定复制后的位置
    '把"添加工作表"，复制后，放在Sheet2前面
    Sheets("添加工作表").Copy before:=Sheets("Sheet2")
    
    '把"添加工作表"，复制后，放在Sheet6后面
    Sheets("添加工作表").Copy after:=Sheets("Sheet6")
    
End Sub

Sheets对象_隐藏和删除工作表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Sub SheetsTest5()
    '隐藏Sheet1
    '就是能看见吗？不能的意思
    'Sheets("Sheet1").Visible = False

    '取消隐藏Sheet1
    'Sheets("Sheet1").Visible = True

    '删除工作表
    '不可恢复，不可撤销，谨慎操作
    'Sheets("Sheet6").Delete
    '只写这一行，会有弹窗
    
    '如果不想要弹窗，就这么写
    '不可恢复，不可撤销，谨慎操作
    Application.DisplayAlerts = False
    Sheets("Sheet1").Delete
    Application.DisplayAlerts = True

End Sub

Workbooks对象_新建工作簿

1
2
3
4
5
6
7
8
9
10
Sub WorkbooksTest()
    'Workbooks.Add
    '运行后，只会新建一个工作簿，但是没有保存
    
    '新建文档，并且设置好另存为的路径及名称
    '设置保存的地方
    'filename后面要加一个完整的路径，并且加上文件的名字+格式
    Workbooks.Add.SaveAs Filename:="C:\Users\陈勇平\Desktop\vvjjcjy\测试文档.xlsx"

End Sub

Workbooks对象_打开和关闭工作簿

1
2
3
4
5
6
7
8
9
10
11
Sub WorkbooksTest2()
    '打开工作簿
    '记得写后缀名
    'Workbooks.Open Filename:="C:\Users\陈勇平\Desktop\vvjjcjy\测试文档.xlsx"
    
    '关闭工作簿
    '因为Excel不会同时打开重名的工作簿，所以一个名字只会有1个打开着的工作簿
    
    Workbooks("测试文档.xlsx").Close

End Sub

Workbooks对象_保存工作簿

1
2
3
4
5
6
7
8
Sub WorkbooksTest3()
    '先对表格做修改
    Range("A1:A10").Value = 100
    
    Workbooks("20221029jy.xlsm").Save
    '此时已经成功进行了保存
  
End Sub

Workbooks对象_删除工作簿

用kill

1
2
3
Sub WorkbooksTest4()
    Kill pathname:="C:\Users\陈勇平\Desktop\vvjjcjy\测试文档.xlsx"
End Sub

Workbooks对象_显示文档的名称及路径

path: 路径

name: 文件名

full name: 路径＋名称

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Sub WorkbooksTest5()
    '弹窗显示，显示当前文档的路径
    MsgBox ThisWorkbook.Path
    '在某个单元格中显示，显示当前文档的路径
		'效果类似：C:\Users\******\Desktop
    Range("A20").Value = ThisWorkbook.Path
    
    
    'ThisWorkbook与ActiveWorkbook的区别
    'ThisWorkbook：表示这个模块所在的这个文档
    'ActiveWorkbook：表示当前的活动工作簿
    '【这两个有可能不一样】
		MsgBox ThisWorkbook.Name
    MsgBox ActiveWorkbook.Name
    Range("A21").Value = ThisWorkbook.Name
    
    
    '一步到位，显示路径+名称
    '效果类似：C:\Users\******\Desktop\20221029Jemma.xlsm
    MsgBox ThisWorkbook.FullName
    MsgBox ActiveWorkbook.FullName
    Range("A22").Value = ThisWorkbook.FullName

		'打开相同路径下的其他文件，需要输入名称及后缀名
    Workbooks.Open Filename:=ThisWorkbook.Path & "\" & "******.xlsm"
    
End Sub

使用ForEach遍历工作簿

遍历3个Excel文档，展示它们的名称

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Sub ForEachTest()
    'for循环时，遍历的都是一些基本的数据类型，比如整数、小数、文本、日期等等
    '遍历对象，需要用for each
    '操作方法类似
    
    Dim book As Workbook
    'workbook 既是对象，也可以是一种数据类型——工作簿类型
    
    For Each book In Workbooks
    'for each表示遍历，workbooks表示现在打开的所有工作簿
    '也就是去遍历每一个打开的工作簿，每一个工作簿的名称都会赋值给变量book
        MsgBox book.Name
    Next book

    '使用ForEach遍历Workbooks（打开的所有的工作簿）
		'打开的工作簿赋值给变量book
		'book的数据类型就是Workbook，所以可以接受Workbooks遍历后的赋值

End Sub

使用ForEach遍历工作表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub ForEachTest2()
    Dim sheet As Worksheet
    '定义一个变量，为Worksheet类型
    
    
    For Each sheet In Worksheets
    'Worksheets表示：当前工作簿中的这些工作表
    '遍历工作表，并且把每个工作表赋值给变量sheet
        
        MsgBox sheet.Name
        
    Next sheet
    
    
End Sub

使用ForEach遍历单元格

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Sub ForEachTest3()
    Dim cell As Range
'    '方法一：for each
'    For Each cell In Range("A5:A10")
'        MsgBox cell.Value
'        '如果不想弹窗太多，可以把数据范围弄小一点
'    Next cell
'
    
    '方法二：for循环
    Dim index As Integer, RowIndex As Integer, ColumnIndex As Integer
    Dim Rng As Range
    Set Rng = Range("A5:A10")
    'Set的应用：这里的Rng是个对象类型
    
    For RowIndex = 1 To Rng.Rows.Count
        'Rng.Rows.Count:表示的是Range("A5:A10")，这里面有多少行，即行的数量
        For ColumnIndex = 1 To Rng.Columns.Count
            '可以弹窗显示
            'MsgBox Rng.Cells(RowIndex, ColumnIndex)
            
            '弹窗显示如果很多，就可以把这部分数据安排到别的单元格区域去
            Range("C14").Offset(RowIndex, ColumnIndex) = Rng.Cells(RowIndex, ColumnIndex)

        Next ColumnIndex
        
    Next RowIndex
    
    
End Sub

【Chapter 4】VBA日期与文本函数

日期类型数据基础知识

不是Excel函数，而是VBA函数。

“立即窗口”中的内容会叠加，不会自动消失。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
Option Explicit

Sub DateTimeFunctionTest()
    'VBA中提供了很多的函数，msgbox就是VBA的函数
    '前缀VBA.可以省略
    'msgbox本质上也是个vba函数，只是我们之前用的时候没加上前缀
    'VBA.MsgBox "VBA函数"
    
    
    '常用的有2类函数：1.对于日期和时间处理的函数；2. 对于文本和字符串的函数
    
    Dim dt As Date
    
    
    
    'dt = #11/5/2022#
    'MsgBox dt
    '如果显示出来只有年月，是和本机电脑显示日期的格式有关
    
    'Debug.Print dt
    '如果不想要msgbox弹窗，可以用Debug.Print配合【视图-立即窗口】
    '如果"立即窗口"有多余信息，可以选中后delete掉
    
    
    '获得当天日期？
    dt = VBA.Date
    Debug.Print dt
    
    '如获得当前时间？
    dt = VBA.Time
    Debug.Print dt
    
    '使当天日期与时间同时显示出来？
    dt = VBA.Now
    Debug.Print dt
    
    '单独获得年月日、时分秒？
    Debug.Print VBA.Year(dt)
    Debug.Print VBA.Month(dt)
    Debug.Print VBA.Day(dt)
    
    '不写vba.的前缀也可以
    Debug.Print Hour(dt)
    Debug.Print Minute(dt)
    Debug.Print Second(dt)
    
    
End Sub

日期格式化操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Option Explicit

Sub DateTimeFunctionTest2()
    Dim dt As Date, dt2 As Date
    
    dt = Date
    
    dt2 = VBA.Time
    
    '通过VBA的format进行日期的格式化操作
    Debug.Print VBA.Format(dt, "yyyy-mm-dd")
    
    Debug.Print VBA.Format(dt, "现在是yyyy年mm月dd日，我好爱你！")
    
    
    '通过VBA的FormatDateTime函数，设置日期的格式，和本机的日期、事件的格式设置相关
    Debug.Print VBA.FormatDateTime(dt, vbGeneralDate) '默认的日期格式
    
    Debug.Print VBA.FormatDateTime(dt, vbLongDate) '效果是长日期，如2022年11月9日
    Debug.Print VBA.FormatDateTime(dt, vbShortDate) '效果是短日期，如2022/11/9
    
    Debug.Print VBA.FormatDateTime(dt2, vbLongTime) '效果是长时间，17:28:13
    Debug.Print VBA.FormatDateTime(dt2, vbShortTime) '效果是短时间，17:28
    
    '如果想对单元格中的日期进行设置
    'NumberFormat不是VBA提供的日期函数，是单元格的属性
    Range("A2:A21").NumberFormat = "yyyy年mm月dd日"
    

End Sub

日期计算操作

可以用VBA.DateDiff直接相减，也可以VBA.DateAdd表示 加/减 几年几月几日

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
Option Explicit

Sub DateTimeFunctionTest3()
    Dim StartDate As Date, EndDate As Date
    
    StartDate = Date '获取当期日期
    EndDate = #12/12/2024# '赋值一个日期
    
    Debug.Print "剩余天数：" & (EndDate - StartDate) & " Days"
    '把2个日期直接相减，可以获得他们相差的天数
    
    '也可以用函数
    '如果想要知道他们相差的年份/月数
    '
    Debug.Print "剩余天数：" & VBA.DateDiff("d", StartDate, EndDate) & " Days"
      
    Debug.Print "剩余月数：" & VBA.DateDiff("m", StartDate, EndDate) & " Months"
    
    '注意年数要使用4个y，“yyyy”
    Debug.Print "剩余年数：" & VBA.DateDiff("yyyy", StartDate, EndDate) & " Years"
    
    
    
    '在 当期日期 上加上正的5年，得到的日期
    
    
    '表示在 当期日期 上加上5年，得到的日期
    Debug.Print VBA.DateAdd("yyyy", 5, StartDate)
    
    '表示在 当期日期 上加上5个月，得到的日期
    Debug.Print VBA.DateAdd("m", 5, StartDate)
    
    '表示在 当期日期 上加上10天，得到的日期
    Debug.Print VBA.DateAdd("d", 10, StartDate)
    
    
    
    '也可以加上负数，表示在 当期日期 减去5年/5个月/10天
    
    Debug.Print VBA.DateAdd("yyyy", -5, StartDate)
    
    Debug.Print VBA.DateAdd("m", -5, StartDate)
    
    Debug.Print VBA.DateAdd("d", -10, StartDate)
    
    
    '表示在当期日期上先加5年，再加5个月，再加10天
    '可以用函数赋值的思想，一步一步操作
    
    StartDate = VBA.DateAdd("yyyy", 5, StartDate)
    Debug.Print "先加上5年后的日期：" & StartDate
    
    StartDate = VBA.DateAdd("m", 5, StartDate)
    Debug.Print "先加上5个月的日期：" & StartDate

    StartDate = VBA.DateAdd("d", 10, StartDate)
    Debug.Print "先加上10天后的日期：" & StartDate
    
End Sub

日期案例实战

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
Option Explicit

Sub DateTimeFunctionTest4()
    '计算日期1和日期2的相隔天数，并存储到C列
    Dim StartDate As Date, EndDate As Date
    
    'Cell表示A列活动的单元格
    'NextCell表示B列活动的单元格
    'DiffCell表示C列活动的单元格
    
    Dim Cell As Range, NextCell As Range, DiffCell As Range
    
    '遍历
    '针对A列的A2单元格，到最末尾的单元格进行遍历
    '表示从A2开始，到A2最底下这块区域
    For Each Cell In Range(Range("A2"), Range("A2").End(xlDown))
    
        '针对A列的活动单元格进行格式化操作
        Cell.NumberFormat = "yyyy-mm-dd"
        
        '获得A列活动单元格旁边的单元格，赋值给NextCell
        Set NextCell = Cell.Offset(0, 1)
        '针对B列的活动单元格进行格式化操作
        NextCell.NumberFormat = "yyyy-mm-dd"
    
        '获得C列的对应单元格
        Set DiffCell = NextCell.Offset(0, 1)
        
        '获得日期1、日期2，分别赋值给日期类型的变量
        StartDate = Cell.Value
        EndDate = NextCell.Value
        
        '使用VBA提供的DateDiff函数，获取相差天数，算出的结果赋值给C列对应单元格
        DiffCell.Value = DateDiff("d", StartDate, EndDate)

				'把相差天数超过400的，字体颜色设置成红色，并进行加粗
        If DiffCell > 400 Then
            DiffCell.Font.Color = vbRed
            DiffCell.Font.Bold = True
        End If
    
    Next Cell
    
    
End Sub

文本类型数据基础知识

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
Sub StringTest()
    Dim Str As String
    
    '文本类型变量赋值
    Str = "文本类型数值"
    Debug.Print Str
    
    '隐式类型转换
    '整数类型数据，赋值给文本类型变量
    '为什么没问题？因为发生了隐式类型转化，也就是说，这里看上去是123456这样1个整数类型的数据；
    '但是实际上它已经是包括有双引号这样的1个文本类型数据，即“123456”
    
    Str = 123456
    Debug.Print Str
    '有方法可以查看当前的str是个什么数据类型
    '可以发现这里的123456是文本数据类型，string，变量类型没有发生变化
    Debug.Print TypeName(Str)
    
    
    '显示类型转换
    Str = CStr(123456) '这里的作用就是把123456，用VBA的方法变成文本数据类型
    Debug.Print Str
    Debug.Print TypeName(Str)
    
    
    
    '固定长度文本变量
    Dim s As String * 3
    s = Str
    Debug.Print "Str的内容是：" & Str
    Debug.Print "s的内容是：" & s
    
    '为什么str是123456，而s是123呢？
    '因为定义s变量的时候，我们限制了它的长度为3；会把超出它限制长度的数据给删除
    '和python里不一样
    '实际编写代码时，不推荐使用
    
    
End Sub

拆分字符串

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
Sub StringTest2()
    Dim str As String
    
    str = "本节课是关于字符串的拆分"
    
    
'    'left，mid，right函数
'    Debug.Print VBA.Left(str, 3)
'    Debug.Print VBA.Right(str, 3)
'    '2个参数，表示截取哪个字符串，要从左/右截取字符串的长度
'
'    Debug.Print VBA.Mid(str, 4) '第3个参数，不填默认从指定的字符串开始，往后的所有内容都会获取到
'    Debug.Print VBA.Mid(str, 4, 3) '第3个参数，填了就表示，从第4个文字开始，想要获取几个长度的文字
'
'
'
'
'    '有固定放分隔符，需要定义一个数组，由于不知道长度，就是不知道会拆分成几个，就定义1个动态数组
'    Dim SplitStr() As String
'    str = "ACV-KJH-OMS-LLX"
'
'    'split函数：根据特定的字符对文本字符串进行拆分
'    SplitStr = Split(str, "-", 2)
'    '第1个参数表示，需要进行拆分的字符串
'    '第2个参数表示，根据哪个分隔符拆分
'    '第3个参数表示，我们想要把这个文本的字符串，想要拆分成几份，那么在从左往右拆分时，已拆到这个数量时，就不继续拆分，即使后面还有这个拆分符；不填的话就是有多少拆多少
'    '第4个参数，有3中可选，我们这里进行更加详细的说明
'
'
'    '想要展示数组，需要通过for循环，遍历数组内容
'    Dim index As Integer
'    For index = LBound(SplitStr) To UBound(SplitStr)
'        Debug.Print SplitStr(index)
'    Next index
'
'
    
    
    
    '第4个参数就表示，假如是用英文字母做拆分符号，那么是否采用大小写敏感
    
    str = "JKavcFGAJKLAHYH"
    '这个字符串里，有大写的A和小写的a
    '假定我们就用字母Dd，把这个文本的字符串进行拆分
    
    '大小写敏感，表示和大小写相关，那么只会对A进行拆分，a不进行拆分
    'SplitStr = Split(str, "A", , vbBinaryCompare)
    
    '与数据库有关，目前暂时不学习，目前只涉及到excel，而不是数据库
    'SplitStr = Split(str, "A", , vbDatabaseCompare)
    
    '大小写不敏感，表示和大小写吧相关，A和a都会进行拆分
    SplitStr = Split(str, "A", , vbTextCompare)
    
     '想要展示数组，需要通过for循环，遍历数组内容
    Dim index As Integer
    For index = LBound(SplitStr) To UBound(SplitStr)
        Debug.Print SplitStr(index)
    Next index
    
    
End Sub

拆分字符串应用案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Function GetOrderPart(OrderInfoRange As Range, index As Integer)
'编写一个子函数，函数名是GetOrderPart，"意思是获得订单编号对应部分“；
'订单编号是类似ABC-123-jlj这样的数据
'第一个参数：设置了1个range类型的变量作为参数，指引用的单元格，也就是想要用短横线作为分隔符的原始数据/针对哪个单元格中的内容进行拆分
'第二个参数：用短横线作为分隔符，拆分后的字符串会有好几个，我们想要输出其中的第几个

    Dim InfoAray() As String
    '设置了一个文本类型的数组，用于接收拆分之后的信息
    
    InfoAray = Split(OrderInfoRange, "-")
    '拆分，针对单元格中的内容进行拆分，拆分的分隔符就是短横线；用InfoAray进行接收，会有>=1个部分，组成了数组
    
    GetOrderPart = InfoAray(index - 1)
    '返回指定部分，即数组中的第(index-1)个：因为文本类型的数组是从0开始，所以index要减1；数组中的第(index-1)个就是这个函数的结果，即输出的结果
    
End Function

连接字符串

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
Sub StringTest3()
    Dim str1 As String, str2 As String, str3 As String
    str1 = "蓦然回首"
    str2 = "那人却在"
    str3 = "灯火阑珊处"
    
    
    '通过&链接运算符
    Debug.Print str1 & "，" & str2 & "，" & str3 & "。"
    
    '通过VBA的Join函数，实现字符串数组元素的连接
    '第一步：把想要连接的内容，转变成字符串数组；
    '第二步：通过join函数进行连接，设定特点字符进行连接
    
    
    
    '先把str1.2.3转化成一个字符串的数组
    Dim strarray() As Variant
    '声明一个可变的类型的动态数组
    
    strarray = Array(str1, str2, str3)
    'VBA有1个array函数，就是把变量包含的所有内容，作为这样的数组元素；用array函数处理之后，就会返回这样的1个数组
    
    Debug.Print Join(strarray, ",")
    
    
End Sub

Function StrConcat(str1 As String, str2 As String, str3 As String)
'这个函数的用处在于把(str1, str2, str3)这3个文本的内容，用斜杠/连接起来
    Dim strarray() As Variant
    strarray = Array(str1, str2, str3)
    StrConcat = Join(strarray, "/")
End Function

替换字符串

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
Sub StringTest4()
    Dim str As String
    '字符串穿插了很多d，其中有大写的和小写的
    str = "dfhDGHJdttDOO99gggd"
    
    '把字符串中的小写d全都替换成短横线-
    Debug.Print Replace(str, "d", "-")
    '效果是【-fhDGHJ-ttDOO99ggg-】
    '默认情况下，大小写敏感
    
    '第1个参数：需要对哪个字符串进行部分字符串的替换
    '第2个参数：对哪个字符串进行替换
    '第3个参数：替换成什么字符串
    '第4个参数：设置替换的起始位置，即从str第一个字符开始计数，从第几个字符串开始替换，没有设置为起点范围内的字符，都会自动被清除掉
    '第5个参数：设置替换的数量，替换了这么多之后，后面的就不再进行替换
    '第6个参数：大小写敏感，vbBinaryCompare——大小写敏感；vbTextCompare——大小写不敏感
    
    
    '第6个参数：想要实现大小写不敏感的效果，需要去考虑最后一个参数
    
    '大小写敏感:
    Debug.Print Replace(str, "d", "-", , , vbBinaryCompare)
    '效果：【-fhDGHJ-ttDOO99ggg-】
    
    '大小写不敏感
    Debug.Print Replace(str, "d", "-", , , vbTextCompare)
    '效果：【-fh-GHJ-tt-OO99ggg-】
    
    
    
    '第4个参数：设置替换的起始位置，即从str第一个字符开始计数，从第几个字符串开始替换，没有设置为起点范围内的字符，都会自动被清除掉
    Debug.Print Replace(str, "d", "-", 3, , vbTextCompare)
    '效果：【h-GHJ-tt-OO99ggg-】
    
    
    '第5个参数：设置替换的数量，替换了这么多之后，后面的就不再进行替换
    Debug.Print Replace(str, "d", "-", 3, 2, vbTextCompare)
    '效果：【h-GHJ-ttDOO99gggd】
    
End Sub

'目的是将JK替换为AC
Function replaceJKtoAC(orderinforange As Range)
    replaceJKtoAC = Replace(orderinforange.Value, "JK", "AC", , 1, vbTextCompare)
End Function

'有点像定义一个y=f(x),x>0,y=(x+1)*(x-9)

字母大小写设置

大小写不敏感：vbTextCompare

大小写敏感：vbBinaryCompare

大小写设置本身就是很重要的一个点

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Option Explicit

Sub StringTest5()
    Dim str1 As String, str2 As String, str3 As String
    str1 = "JK"
    str2 = "jk"
    str3 = "jkJK"
    
    Debug.Print str1 = str2
    '结果是False，说明：默认情况下，大小写敏感。

    'comp,compare的缩写
    Debug.Print StrComp(str1, str2, vbTextCompare)
    '结果是0，说明相等
    
    Debug.Print StrComp(str1, str3, vbTextCompare)
    '结果是-1，说明不相等
    
    
    
    '切换大小写，可以使用函数LCase（小写处理）、Ucase（大写处理）
    Debug.Print LCase(str1)
    Debug.Print UCase(str2)
    
    
    '切换大小写，也可以使用大小写切换函数，根据后面的参数，表现为不同的效果
    'Conv，Converter
    Debug.Print StrConv(str3, vbUpperCase) '全部变成大写
    Debug.Print StrConv(str3, vbLowerCase) '全部变成小写
    Debug.Print StrConv(str3, vbProperCase) '首字母变成大写，其他字母全都变成小写
    
    
End Sub

查找字符串

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
Option Explicit

Sub StringTest()
    Dim str As String
    str = "ABdCDdEAFdGaAbcddAefg"
    
    '获取某个字母的起始位置
    '使用Instr查看在某个文本变量中，是否存在某个字符串，如果存在，就会返回相应的位置；
    '如果不存在，就返回0
'    Debug.Print InStr(1, str, "d", vbBinaryCompare)
'
'    Debug.Print InStr(12, str, "d", vbBinaryCompare)

    '想要查找字母d出现的次数
    
    Dim index As Integer, count As Integer
    index = 1
    
    '只要还有“d”存在，就继续执行
    While index <> 0
    
    
        '不加这段代码的话，档要查找的字符串是整个字符串的第一个字母的时候，会有bug
        '因为是从整个字符串的第二个字母开始查找的，那么第一个位置的字符串就会查找不到
        If count = 0 Then
            index = 0
        End If
    

        '从上一次查找到的位置的右边开始，继续查找“d”的位置
        index = InStr(index + 1, str, "A", vbBinaryCompare)
        '如果不为0，就继续查找
        If index <> 0 Then
            count = count + 1
        End If
        Debug.Print index
        
        
    Wend
    
    Debug.Print "Count=" & count

End Sub

LIKE运算符

Pattern规则
* 零个或多个字符
? 任意单个字符
# 任何单个数字 (0-9)
[ charlist ] charlist 内的任意单个字符
[ !charlist ] charlist 外的任意单个字符
订单编号(练习素材)
IPV-NSU-36456
TS-EYS-77G
HDC-HUJ-19481
OY-HX-815
LNG-ZSN-831
OS-SW-852
NVH-OHD-9376
OZW-SR-125
JI-TKX-132
FGX-CPZ-26532
EY-HXR-4334
UUK-IZT-46
ZUF-BCV-6146
GHW-UOU-457
SHN-MJN-37
DHD-OCT-3716 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
Option Explicit

Sub StringTest()
    'Like：符合某个文本的模式
    'I*：设置了某个模型，含义就是以I字母开头
    '下面这行代码的意思就是，验证这个字符串是不是以字母A开头，是的话就输出TRUE，不是就输出FALSE
    '只有A这个字符，也是会返回TRUE
    'Debug.Print "ABC-DEF-GHIJ" Like "A*"
    
    Dim pattern As String
    
    '以O开头
    'pattern = "O*"
    
    '以两个字符开头
    '也就是短横线左侧有两个字符，所以要加个短横线；短横线右侧就可以有0个或者多个字符串，所以要加个*
    'pattern = "??-*"
    
    '以3个数字结尾，就是最后短横线后面只有3个数字
    'pattern = "*-###"
    
    '以O和G开头，记得加上双引号
    'pattern = "[OG]*"
    
    
    '不以O和G开头，记得加上双引号
    'pattern = "[!OG]*"
    
    '以O开头，两个字符开头，中间两个字符，三个数字结尾
    pattern = "O?-??-###"
    
    
    Dim cell As Range
    
    For Each cell In Range("A2", Range("A2").End(xlDown)) '从A2开始的，到A2以下的所有单元格
        cell.Font.Color = vbBlack '如果重复操作几次，修改过格式，需要先把之前操作的格式清除，也就是把字体颜色变成黑色
        If cell.Value Like pattern Then
            cell.Font.Color = vbRed
        End If
    Next cell
    
    
End Sub

Excel内置函数的使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
Option Explicit

Sub ExcelFunctionTest()
    Range("E2").Value = "求和结果："
    Range("F2").Value = Application.WorksheetFunction.Sum(Range("A1:A15"))
    
    Range("E3").Value = "最大值为："
    Range("F3").Value = Application.WorksheetFunction.Max(Range("A1:A15"))
    
    Range("E4").Value = "最小值为："
    Range("F4").Value = Application.WorksheetFunction.Min(Range("A1:A15"))
    
    
    Range("E6").Value = "排名第5的单元格的值为："
    Range("F6").Value = Application.WorksheetFunction.Large(Range("A1:A15"), 5)
    
    
    Range("E8").Value = "对大于5000的数进行求和："
    Range("F8").Value = Application.WorksheetFunction.SumIf(Range("A1:A15"), ">" & 5000)
    
    '并不是Excel所有的内置函数都可以进行使用
    'VBA函数包含的功能，与Excel内置函数有冲突时，Excel内置函数无法使用！
    '就是说，当你想用一个Excel内置函数却发现VBA不支持时，有可能是VBA自己有这个功能的函数
    
    '举例
    '无法使用Excel里面的rand函数生成随机数，因为VBA有这个功能的函数
    'Application.WorksheetFunction.Rand
    
    '初始化随机数：0~1（不包含）
    '会在H4单元格生成一个0~1（不包含）之间的随机数
    VBA.Randomize
    Range("H3").Value = "生成一个0~1（不包含）之间的随机数"
    Range("H4").Value = VBA.Rnd
    
    
    
End Sub

【Chapter 5】VBA异常和事件处理

VBA程序异常OnError处理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
Sub ExceptionTest1()
    Dim Num As Variant
    
    Num = InputBox("请输入一个数字：")
    
    '输出输入的数字的平方根
'    Debug.Print VBA.Sqr(Num)
    
    '假如输入的是负数/文本/空值，就会报错
    
    
    '思路一：用if和msgbox
    If Num < 0 Then
        MsgBox "请不要输入负数"
    ElseIf Not VBA.IsNumeric(Num) Then
        MsgBox "请输入数字"
    Else
        Debug.Print VBA.Sqr(Num)
    End If
'
End Sub

Sub ExceptionTest2()
    Dim Num As Variant
    
    '如果出现错误，就跳过其他代码，直接跳转到相应标签
    '如果没有这行代码，那么输入负数/文本/空值时，就会提示错误
    On Error GoTo ErrorHandler
    
    
    Num = InputBox("请输入一个数字：")
    Debug.Print VBA.Sqr(Num)
    
    '没有错误，不再显示错误信息
    '如果没有这段代码，那么输入正数时，代码运行没有任何错误时，也会弹窗“请确认输入一个正数”
    Exit Sub
    
ErrorHandler:
    MsgBox "请确认输入一个正数"
    
    
End Sub

Sub ExceptionTest3()
    Dim Num As Variant
    
    '出现错误，跳过继续执行；可以跳过不止一次
    '没有这段代码，代码执行会报错；加上这段代码，就代码不会报错，只是继续往下执行：弹窗“异常”
    On Error Resume Next
    
    
    Num = InputBox("请输入一个数字：")
    
    Debug.Print VBA.Sqr(Num)
    Exit Sub
    
    MsgBox "VBA异常处理1"
    
    '如果不想要下面的代码出现错误时，和上面是一样的处理方式，那么就用这段代码
    '重置错误默认处理方式
    On Error GoTo 0
    
    Debug.Print 1 / 0 '不会在立即窗口出现结果，因为有上面的代码，所以出现错误就跳过了，继续执行接下来的代码
    
    MsgBox "VBA异常处理2"
End Sub

VBA程序异常Resume处理

可以考虑再听一遍课程，resume的三种用法下，代码的运行逻辑不一样

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
Sub ExceptionTest()
    Dim Num As Variant
        
InputNum:
    '如果出现错误，就跳过其他代码，直接跳转到相应标签
    '如果没有这行代码，那么输入负数/文本/空值时，就会提示错误
    On Error GoTo ErrorHandler

    Num = InputBox("请输入一个数字：")
    Debug.Print VBA.Sqr(Num)
    
    MsgBox "VBA代码异常"
    
    '没有错误，不再显示错误信息
    '如果没有这段代码，那么输入正数时，代码运行没有任何错误时，也会弹窗“请确认输入一个正数”
    Exit Sub
    
ErrorHandler:
    MsgBox "请确认输入一个正数"
    '弹出这个窗口后，点击“确定”，这个程序就结束了，这是因为程序的意思是，
    '出现错误后直接跳过其他代码，跳转到这个弹窗，作用仅仅是弹窗一下
    
    
    'resume有三种使用方式：
    
    '方法一：
    '如果想要弹窗之后，处理错误之后，继续允许输入数字，再写一个标签InputNum，到达相应的标签处再次运行
'    Resume InputNum
    
    '方法二：
    '跳过错误的代码行，继续执行
'    Resume Next
    
    '方法三：
    '从出现错误的代码处开始执行（就是Debug.Print VBA.Sqr(Num)这一行）
    '如果进入死循环，就长按ESC按钮；
    '如果出现错误，就将num赋值为100
    'Num=100
    Num = -Num
    Resume
    

End Sub

VBA程序Bug处理技巧

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
Option Explicit
Sub DebugTest1()
    '编译错误
    '查看代码，确保代码语法没有问题
    '比如这里少了个括号或者英文引号之类的
    Range("A1").Value = 100
End Sub

Sub DebugTest2()
    '运行时错误
    '执行代码的时候，如果代码行数很多，遇到了“运行时错误”，且不知道哪一行代码错了
    '1. 可以点击【调试】按钮，它会提示你哪一行代码错了
    '2. 还可以通过注释某一行代码的方式，去缩小错误排查范围
    '3. 也可以通过逐语句执行的方式
    Dim Num As Integer
    
    For Num = 1 To 10
        Range("A" & Num).Value = Num * 100
        Range("A" & Num).Interior.Color = vbRed
    Next Num
End Sub

Sub DebugTest3()
'1. 明确非常常见的错误：程序没有语法错误，而是程序执行的结果并没有出现预期的结果，逻辑性错误。
'2. 针对程序的逻辑性错误，就需要通过变量或者表达式在执行的过程中的内容判定逻辑问题出在哪里。
'3. 有三种方式查看变量的内容。本地窗口、监控窗口、立即窗口
'4. 打断点：希望程序在哪个位置停止运行，方便我们查看当前状态下的变量以及表达式的内容。
    
    
    
    '1. 可以逐行写注释，来展示思路
    '2. 还可以通过打断点的方式，就是在这行代码的左侧，点一下红色，会有个红点
        '这个红点的意思就是：程序运行时，到达这个断点之后，就停止了运行，可以查看到达这个断点的时候，各个变量的数值是多少（有点麻烦）
    '3. 想要看到各个变量的值，还可以去【视图-本地窗口】，可以看到当前的子程序中，所有的相关的变量所发生的变化
    '4. 【视图-立即窗口】，输入【?Num1】，然后回车，可以展示当前变量的值
    '5. 【视图-监视窗口】，可以指定想要监视某个变量，比如想要监视变量num1，就鼠标点在num1处，右击，点击“添加监视”；
        '【监视窗口】，除了可以监视变量，还可以监视表达式，比如想要监视【Num1 * Num2】的结果，就鼠标左键选中代码中的Num1 * Num2，然后右击“添加监视”；监视【Num2 & "*" & Num1 & "=" & (Num1 * Num2)】也可以
    Dim Num1 As Integer, Num2 As Integer
    
    '将num1作为行数
    For Num1 = 1 To 9
        '将num2作为列数
        For Num2 = 1 To 9
            '只有行数大于列数的时候，才会执行相应的内容设置代码
            If Num1 >= Num2 Then
                '设置相应单元格的内容
                Cells(Num1, Num2) = Num2 & "*" & Num1 & "=" & (Num1 * Num2)
            End If
        Next Num2
    Next Num1
    
End Sub

工作表-事件处理程序

常用工作表事件

事件名称 触发条件
Activate 当工作表被激活时触发（即用户切换到该工作表）。
Deactivate 当工作表失去焦点时触发（即用户切换到其他工作表）。
Change 当工作表中的单元格内容被更改时触发。
SelectionChange 当用户选择不同的单元格或区域时触发。
BeforeDoubleClick 当用户双击单元格时触发（在默认的双击操作之前）。
BeforeRightClick 当用户右键单击单元格时触发（在默认的右键菜单弹出之前）。
Calculate 当工作表重新计算时触发（例如公式结果更新）。
FollowHyperlink 当用户点击工作表中的超链接时触发。
PivotTableUpdate 当数据透视表更新时触发。 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
Option Explicit

'   所谓事件，就是一个动作
'   事件: 动作
'   做出的反应: 编写的代码

'   选择了一个动作，Activate：工资表激活
'   对于事件的方法调用，并不是我们自己调用，而是由excel自动进行调用
'   只是针对事件的反应（具体的代码）由我们自己进行编写

Private Sub Worksheet_Activate()

    '  可以在这个worksheet里，可以通过一些事件，来触发代码的运行
    '  在这里是，如果sheet2工作表被激活，那么就设置A1单元格为活动单元格
    Range("A1").Activate
    
End Sub


'   在工作表中右键前，可以清除选中单元格的格式
'   右键本身的功能，可以通过“ Cancel ”来进行自定义
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Target.ClearFormats
    '   清除格式
    
    Cancel = True
    '   右键不会弹出本来右键的菜单
    
    
    '   遇到不会写的代码，可以去用宏录制
End Sub


'工作表失去激活，把工作表里所有的样式全部去除掉
Private Sub Worksheet_Deactivate()
    Cells.ClearFormats
    ' 所有单元格全部去除格式
End Sub

'选择单元格
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    '   选中单元格/某块区域，这块区域的背景颜色，就变高亮
    Target.Interior.Color = RGB(222, 200, 222)
    '   选中单元格/某块区域，这块区域的字体就会变粗
    Target.Font.Bold = True

    
End Sub


工作簿-事件处理程序

常用工作簿事件

事件名称 触发条件
Open 当工作簿被打开时触发。
BeforeClose 当工作簿关闭之前触发。
BeforeSave 当工作簿保存之前触发。
AfterSave 当工作簿保存之后触发。
SheetActivate 当工作簿中的任何工作表被激活时触发。
SheetDeactivate 当工作簿中的任何工作表失去焦点时触发。
SheetChange 当工作簿中的任何工作表的单元格内容被更改时触发。
SheetCalculate 当工作簿中的任何工作表重新计算时触发（例如公式结果更新）。
NewSheet 当工作簿中新建工作表时触发。
SheetBeforeDelete 当工作簿中的工作表被删除之前触发。
SheetBeforeRightClick 当工作簿中的任何工作表被右键单击之前触发。
SheetBeforeDoubleClick 当工作簿中的任何工作表被双击之前触发。
WindowActivate 当工作簿窗口被激活时触发。
WindowDeactivate 当工作簿窗口失去焦点时触发。
WindowResize 当工作簿窗口大小被调整时触发。 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Option Explicit

'   关闭工作簿
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    '   避免关闭时选择不保存
    '   把文档另存到相同的路径下，只是备份文档的名称前有一个“\Copy_”的前缀
    ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "\Copy_" & ThisWorkbook.Name

'    MsgBox "ByeBye!"

    
'   输入关闭密码，否则不许关闭
    Dim password As String
    password = InputBox("请输入关闭文档的密码")
    If Not password = "123" Then
        MsgBox "你没有权限关闭本文档！"
        Cancel = True   '   密码不正确，且取消对本文档的关闭
    End If
End Sub

'禁止另存为
'SaveAsUI为true，意思为想把文档另存为，可以用在if句里
'Cancel为true，意思为取消另存为的行为
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI = True Then
        MsgBox "本文档禁止另存"
        Cancel = True
    End If
End Sub

'   打开工作簿，就是一个事件
Private Sub Workbook_Open()
    MsgBox "Welcome!"
End Sub

【Chapter 6】MsgBox和InputBox

MsgBox各个参数的作用

1
2
3
4
5
6
7
8
9
10
11
12
13
Option Explicit

Sub MsgBoxTest()
    '参数一：prompt，作用就是设置弹出的窗口的内容
    'MsgBox "VBA is very useful"
    
    '参数二：图标 + 按钮 + 默认按钮
    'MsgBox "VBA is very useful?", vbYesNo + vbQuestion + vbDefaultButton2

    '参数三：设置弹出的窗口的标题
    MsgBox "VBA is very useful?", vbYesNo + vbQuestion + vbDefaultButton2, "询问"
End Sub

MsgBox返回值处理技巧

常量 值 说明
vbOKOnly 0 仅显示”确定”按钮。
vbOKCancel 1 显示”确定”和”取消”按钮。
vbAbortRetrylgnore 2 显示”中止”、”重试”和”忽略”按钮。
vbYesNoCancel 3 显示”是”、”否”和”取消”按钮。
vbYesNo 4 显示”是”和”否”按钮。
vbRetryCancel 5 显示”重试”和”取消”按钮。
vbOK 1 确定
vbCancel 2 Cancel
vbAbort 3 中止
vbRetry 4 重试
vblgnore 5 忽略
vbYes 6 是
vbNo 7 否
vbCritical 16 显示”关键消息”图标。
vbQuestion 32 显示”警告查询”图标。
vbExclamation 48 显示”警告消息”图标。
vblnformation 64 显示”信息消息”图标。vbCritical
vbDefaultButton1 0 第一个按钮是默认按钮。
vbDefaultButton2 256 第二个按钮是默认按钮。
vbDefaultButton3 512 第三个按钮是默认按钮。
vbDefaultButton4 768 第四个按钮是默认按钮。 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Option Explicit

Sub MsgboxTest()
    '定义一个变量，类型是VbMsgBoxResult，也就是msgbox函数返回值的类型
    Dim OptionSelected As VbMsgBoxResult
    
'    MsgBox "是否将数据进行相加？", vbYesNo + vbQuestion, "数据操作"——弹窗
'   要变量赋值，得加个括号
'   获取返回值
    OptionSelected = MsgBox("是否将数据进行相加？", vbYesNo + vbQuestion, "数据操作")
    
    '根据不同返回值，分别进行处理
    If OptionSelected = vbYes Then
        Dim TOTAL As Integer
        TOTAL = WorksheetFunction.Sum(Range("A1:A15"))
        Range("A16").Value = TOTAL
        Range("A16").Font.Color = vbRed
        Range("A16").Font.Bold = True
    Else
        Range("A16").Clear
        MsgBox "相加的数据结果已清除"
    End If
    
End Sub

InputBox函数的使用方式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Option Explicit

Sub InputboxTest()
    Dim name As String
    name = InputBox("请输入你的名字", "输入个人信息", "吴明")
    
    If name = "" Then
        MsgBox "请务必输入自己的姓名", vbExclamation, "警告"
    Else
        Range("A1").Value = name
    End If
End Sub

' 输入数字，就生成多少个新sheet
Sub AddWorksheet()
    Dim result As String
    result = InputBox("请问想要输入几张工作表", "输入数字", "1")
    
    If result = "" Then
        MsgBox "清确定已经录入数字并点击确定按钮", vbExclamation, "说明"
    Else
        ' input返回的值的类型是文本类型，但是sheets对象的add方法的count参数需要整数类型
        
        ' 方法一：隐式类型转换
        ' result存储的内容是文本类型，但是它默认转换为了整数类型
        'Sheets.Add Count:=result
        
        ' 方法二：显式类型转换【更推荐】
        'VBA.CInt(result)可以把result转换为整数类型，然后再传递给count这个参数
        Sheets.Add Count:=VBA.CInt(result)
    End If
End Sub

InputBox函数的Type参数使用

类型值 描述
0 公式
1 数字，用户输入的内容必须为数字，否则会报错。
2 文本（字符串），用户输入的内容作为字符串返回。
4 逻辑值（True/False），用户输入的内容必须为逻辑值，否则会报错。
8 range对象形式的单元格引用，用户输入的内容必须为单元格引用（如 A1），否则会报错。
16 错误值，用户输入的内容必须为错误值（如 #N/A），否则会报错。
64 数值数组，用户输入的内容必须为数组，否则会报错。 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
Option Explicit

Sub InputboxTest1()
    Dim num As Integer
    '在Excel中创建一个简单的输入框，要求用户输入一个数字，并在用户输入后在一个消息框中显示该数字。
    '使用application下的InputBox的好处在于有一个type函数
    '存在内置的数据验证
    '1——数字
    num = Application.InputBox(prompt:="请输入一个数字", Type:=1)
    MsgBox num
    
End Sub

Sub InputboxTest2()
    Dim rangeselected As Range
    
    '8——range对象形式的单元格引用
    Set rangeselected = Application.InputBox(prompt:="选择单元格区域", Type:=8)
    '设置选择的单元格区域样式
    rangeselected.Interior.Color = vbRed
    
    '弹窗之后可以直接选中单元格区域，会自动识别出【$D$28:$D$31】这样的编码
    
End Sub

Sub InputboxTest3()
    '0——公式
    Range("A1").FormulaLocal = Application.InputBox(prompt:="请输入公式", Type:=0)
    '如果输入【123】，那么A1单元格就是【=123】
    '如果输入【type类型】，那么A1单元就是【="type类型"】
    '如果输入【=today()】，那么A1单元格就是【2025/3/5】,会显示出今天的日期
    '如果输入【=randbetween(10,100)】，就会显示出一个10到100间的数字
End Sub

    
Sub InputboxTest4()
    Dim rangeselected As Range
    '0——公式
    '8——range对象形式的单元格引用
    Set rangeselected = Application.InputBox(prompt:="选择单元格区域", Type:=8)
    '设置单元格区域中每一个单元格的公式
    rangeselected.FormulaLocal = Application.InputBox(prompt:="请输入公式", Type:=0)
End Sub


Sub InputboxTest5()
    '64——数组
    Dim Nums As Variant
    Nums = Application.InputBox(prompt:="选择转换为数组的区域", Type:=64)
    
    '可以用二维，但是这里写的是一维
    Dim index As Integer
    For index = LBound(Nums, 1) To UBound(Nums, 1)
        Debug.Print Nums(index, 1)
    Next index
    
End Sub



【Chapter 7】VBA工作应用实战

拆分工作表（上）

拆分工作表（下）

多工作表合并

拆分成多工作簿

多工作簿合并