Excel VBA完全自学手册

第1章 Excel VBA编程概述 1
1.1 Excel VBA简介 1
1.1.1 Excel VBA的发展历程 1
1.1.2 为什么要使用Excel VBA 2
1.1.3 Excel VBA提供了哪些功能 2
1.2 电子表格应用程序开发流程 3
1.2.1 优秀电子表格应用程序的标准 3
1.2.2 确定用户类型 4
1.2.3 确定用户需求 4
1.2.4 构思用户界面 5
1.2.5 编写代码 6
1.2.6 对应用程序进行测试 6
1.2.7 修复任何可能的错误 7
1.2.8 发布应用程序 7
1.3 Excel VBA文件格式与安全性 8
1.3.1 Excel 2007/2010文件格式的改变 8
1.3.2 禁止运行VBA代码 10
1.3.3 防止VBA代码被意外修改 13
1.4 录制宏 13
1.4.1 【开发工具】选项卡 13
1.4.2 录制宏以前需要做的事 13
1.4.3 录制宏 14
1.4.4 运行宏 15
1.4.5 修改宏 15
1.5 使用VBE窗口 16
1.5.1 打开VBE窗口 16
1.5.2 工程资源管理器 17
1.5.3 属性窗口 17
1.5.4 代码窗口 17
1.5.5 管理模块 18
1.6 数据类型、变量与常量 18
1.6.1 数据类型 19
1.6.2 变量声明 19
1.6.3 变量的可用范围和有效时间 21
1.6.4 常量 23
1.6.5 命名约定 23
1.7 Sub过程 24
1.7.1 声明Sub过程 24
1.7.2 Sub过程的使用范围 25
1.7.3 包含参数的Sub过程 26
1.7.4 在VBA代码中调用Sub过程 27
1.7.5 Sub过程的递归 28
1.8 Function过程 28
1.8.1 Sub过程与Function过程的区别 28
1.8.2 声明Function过程 28
1.8.3 调用Function过程 29
1.9 判断和循环结构 30
1.9.1 判断结构之If Then 30
1.9.2 判断结构之Select Case 33
1.9.3 循环结构之For Next 34
1.9.4 循环结构之Do Loop 35
1.10 VBA内置函数 37
1.11 数组 38
1.11.1 数组的类型 38
1.11.2 声明数组 38
1.11.3 为数组赋值 39
1.11.4 动态数组 40
1.12 错误处理 41
1.12.1 错误类型 41
1.12.2 调试代码 43
1.12.3 错误的常见处理方式 43
1.12.4 利用错误编写高效的代码 45
1.13 VBA代码编写规则与提速技巧 45
1.13.1 使用缩进格式突显代码层次结构 45
1.13.2 为长代码换行 46
1.13.3 为代码添加注释 46
1.13.4 强制声明变量 46
1.13.5 关闭屏幕刷新 47
1.13.6 使用With语句使程序变得简洁 47
1.13.7 将重复使用的属性值预先赋值给变量 48
1.13.8 利用数组代替单元格区域进行读、写操作 48
第2章 使用对象编程 49
2.1 对象与集合 49
2.1.1 类与对象 49
2.1.2 引用集合中的对象 50
2.1.3 Excel对象模型 50
2.1.4 父对象与子对象 51
2.1.5 使用【对象浏览器】 52
2.1.6 通过录制宏+F1键学习更多对象 52
2.2 属性 53
2.2.1 引用对象的属性 53
2.2.2 为属性赋值 53
2.2.3 某些属性可返回新的对象 54
2.3 方法 54
2.3.1 方法的参数 54
2.3.2 使用命名参数 55
2.3.3 某些方法可返回新的对象 55
2.4 事件 56
2.4.1 事件分类 56
2.4.2 事件过程的一个简单示例 56
2.5 对象编程中的3个有用技巧 59
2.5.1 使用对象变量简化对象引用 59
2.5.2 使用With结构简化对象引用 60
2.5.3 使用For Each结构遍历集合中的对象 61
第3章 使用Excel应用程序 62
3.1 全局属性 62
3.2 定制代码运行时的环境 64
3.2.1 控制状态栏信息的显示 64
3.2.2 控制警告提示的显示方式 64
3.2.3 控制屏幕刷新 65
3.3 OnTime——安排定时任务 65
3.4 OnKey——为操作指定快捷键 67
3.5 在VBA中使用工作表函数 68
第4章 使用工作簿 69
4.1 Workbooks集合和Workbook对象 69
4.1.1 Workbooks集合的常用属性和方法 69
4.1.2 Workbook对象的常用属性和方法 70
4.1.3 ThisWorkbook和ActiveWorkbook 75
4.2 Workbooks和Workbook应用示例 75
4.2.1 新建指定数量的多个工作簿 75
4.2.2 判断工作簿是否存在 75
4.2.3 批量打开多个工作簿 76
4.2.4 保存并关闭所有工作簿 76
4.2.5 覆盖现有工作簿 76
4.2.6 关闭多余的工作簿窗口 77
4.2.7 清除所有打开工作簿中的密码 78
4.2.8 拆分大工作簿 78
第5章 使用工作表 80
5.1 Worksheets集合和Worksheet对象 80
5.1.1 Worksheets集合的常用属性和方法 80
5.1.2 Worksheet对象的常用属性和方法 81
5.1.3 Worksheets集合与Sheets集合 86
5.2 Worksheets和Worksheet应用示例 86
5.2.1 批量重命名工作表 86
5.2.2 隐藏除【主表】以外的其他工作表 87
5.2.3 删除指定工作表以外的其他工作表 87
5.2.4 创建工作表目录 88
5.2.5 同步工作表 89
第6章 使用单元格区域 91
6.1 引用单元格或单元格区域 91
6.1.1 引用一个单元格（Range和Cells属性） 91
6.1.2 引用连续或不连续的区域（Range属性） 93
6.1.3 引用行（Rows和EntireRow属性） 93
6.1.4 引用列（Columns和EntireColumn属性） 94
6.1.5 [A1]引用方式 94
6.1.6 通过偏移引用新的单元格或区域（Offset属性） 95
6.1.7 调整引用的范围（Resize属性） 95
6.1.8 引用多个不连续的区域（Union方法） 96
6.1.9 引用多个区域的重叠部分（Intersect方法） 97
6.1.10 引用当前区域（CurrentRegion属性） 97
6.1.11 引用工作表中的数据区域（UsedRange属性） 98
6.1.12 定位区域边界（End属性） 98
6.1.13 定位指定类型的数据（SpecialCells方法） 100
6.2 在区域中读写数据 103
6.2.1 常规方法 103
6.2.2 使用数组 104
6.2.3 选择性粘贴（PasteSpecial方法） 107
6.3 区域应用示例 108
6.3.1 复制数据 108
6.3.2 删除工作表中的空行 108
6.3.3 高亮显示活动单元格所在的行和列 109
6.3.4 选择工作表中的所有合并单元格 109
6.3.5 禁止用户选择指定区域 109
6.3.6 删除区域中的重复值 110
6.3.7 取消工作表中的超链接 111
6.4 使用名称 111
6.4.1 命名单元格区域 111
6.4.2 在名称中存储值 114
6.4.3 在名称中存储公式 115
6.4.4 在名称中存储数组 115
6.4.5 Name对象和Name属性 116
6.4.6 在VBA代码中使用名称 116
6.4.7 Excel内部名称 117
6.4.8 隐藏名称 118
6.4.9 删除名称 118
6.5 排序和筛选数据 118
6.5.1 排序数据 119
6.5.2 自动筛选 122
6.5.3 高级筛选 125
第7章 使用自定义函数 128
7.1 一个自定义函数示例 128
7.1.1 编写自定义函数 128
7.1.2 在工作表公式中使用自定义函数 129
7.1.3 在VBA过程中使用自定义函数 129
7.1.4 分析自定义函数的工作原理 130
7.1.5 自定义函数不能做什么 130
7.2 函数开发基础 131
7.2.1 创建无参数的函数 131
7.2.2 创建包含一个参数的函数 131
7.2.3 创建包含两个参数的函数 132
7.2.4 创建包含可选参数的函数 132
7.2.5 创建使用单元格区域作为参数的函数 133
7.2.6 创建包含不定数量的参数的函数 134
7.2.7 创建返回数组的函数 135
7.2.8 创建返回错误值的函数 136
7.3 更多自定义函数示例 136
7.3.1 获取固定不变的日期和时间 137
7.3.2 获取当前工作簿的路径和名称 137
7.3.3 确定单元格数据的类型 137
7.3.4 返回区域中第一个非空单元格的值 138
7.3.5 从文本中提取数字 139
7.3.6 在区域内搜索特定的文本 139
7.3.7 统计非重复值的数量 140
7.3.8 反转单元格内容的顺序 141
7.3.9 多功能函数 142
7.4 为函数添加帮助信息与封装函数 143
7.4.1 为自定义函数添加帮助信息 143
7.4.2 使用加载宏封装自定义函数 144
第8章 使用图表 147
8.1 嵌入图表和图表工作表 147
8.1.1 嵌入图表 147
8.1.2 图表工作表 148
8.1.3 图表的结构 148
8.1.4 图表对象模型 149
8.1.5 在嵌入图表和图表工作表之间转换 150
8.2 创建与设置图表 151
8.2.1 创建嵌入图表 151
8.2.2 创建图表工作表 158
8.2.3 更改图表类型 159
8.2.4 使用预置的图表布局 159
8.2.5 自定义图表布局 160
8.2.6 使用预置的图表样式 162
8.2.7 自定义设置图表格式 163
8.2.8 编辑图表的数据系列 165
8.2.9 确定图表使用的单元格区域 169
8.2.10 为图表添加数据标签 171
8.2.11 统一所有嵌入图表的大小 173
8.2.12 将所有嵌入图表转换为图表工作表 173
8.2.13 批量删除图表 174
8.3 将图表导出为图片 174
8.3.1 导出单个图表 174
8.3.2 导出所有图表 175
第9章 使用数据透视表 176
9.1 数据透视表基础 176
9.1.1 创建一个数据透视表 176
9.1.2 数据透视表的结构 177
9.1.3 数据透视表常用术语 179
9.1.4 数据透视表缓存 180
9.2 数据透视表对象模型 182
9.2.1 PivotCache/PivotCaches对象 182
9.2.2 PivotTable/PivotTables对象 182
9.2.3 PivotField/PivotFields对象 183
9.2.4 PivotItem/PivotItems对象 184
9.3 使用VBA创建数据透视表 184
9.3.1 创建基本的数据透视表 184
9.3.2 对字段布局 186
9.3.3 刷新数据透视表 186
9.3.4 编辑字段 187
9.3.5 设置数据透视表的布局方式 188
9.3.6 控制总计的显示与隐藏 188
9.3.7 修改数字格式 189
9.3.8 设置字段的汇总方式 189
9.3.9 设置数据的显示方式 190
第10章 定制工具栏 191
10.1 命令栏简介 191
10.1.1 命令栏的类型 191
10.1.2 罗列Excel中的内置命令栏 193
10.1.3 罗列所有工具栏及其上的控件 194
10.1.4 罗列Excel内置控件的FaceId及其图像 196
10.2 操作工具栏 197
10.2.1 引用指定的工具栏 198
10.2.2 引用工具栏中的控件 198
10.2.3 在现有工具栏中添加控件 199
10.2.4 设置控件的属性 200
10.2.5 从工具栏中删除控件 201
10.2.6 禁止修改工具栏 202
10.2.7 隐藏工具栏 203
10.2.8 删除工具栏 203
10.3 创建新的工具栏 203
第11章 定制菜单和快捷菜单 207
11.1 在现有菜单栏中添加菜单 207
11.1.1 罗列所有菜单栏及其上的控件 207
11.1.2 引用指定的菜单栏 208
11.1.3 引用菜单栏中的菜单 208
11.1.4 在现有菜单栏中添加菜单 209
11.1.5 在菜单中添加菜单项 210
11.1.6 使用Parameter存储额外信息 212
11.1.7 禁用菜单或菜单项 213
11.1.8 隐藏菜单或菜单项 213
11.1.9 重置菜单 214
11.1.10 删除菜单或菜单项 214
11.2 创建新的菜单栏 215
11.3 定制快捷菜单 217
11.3.1 罗列所有快捷菜单及其上的控件 217
11.3.2 在快捷菜单中添加新的菜单项 218
11.3.3 删除快捷菜单中的菜单项 219
11.3.4 禁用快捷菜单和菜单项 219
11.3.5 禁用所有快捷菜单 220
11.3.6 创建可响应上下文的快捷菜单 220
第12章 定制RibbonX功能区 222
12.1 功能区使用基础 222
12.1.1 功能区的结构 222
12.1.2 自定义功能区 224
12.2 使用VBA操作功能区 225
12.2.1 使用VBA可以对功能区进行的操作 225
12.2.2 访问功能区中的控件 226
12.2.3 激活功能区中的选项卡 226
12.3 使用XML定制功能区 227
12.3.1 定制功能区的整体流程 227
12.3.2 Excel文件结构 228
12.3.3 编写用于实现功能区中控件功能的VBA过程 229
12.3.4 编写定制功能区的RibbonX代码 230
12.3.5 将包含VBA代码的工作簿更改为压缩文件 233
12.3.6 在压缩文件内创建customUI文件夹 233
12.3.7 将customUI.xml文件移入customUI文件夹中 233
12.3.8 修改.rels文件以便建立关联 234
12.3.9 测试自定义功能区 235
12.3.10 定制过程中可能遇到的问题 235
12.3.11 使用Office 2007 Custom UI Editor工具 237
第13章 使用Excel对话框 238
13.1 MsgBox和InputBox函数 238
13.1.1 使用MsgBox函数返回信息 238
13.1.2 使用InputBox函数接收信息 240
13.2 InputBox方法 241
13.2.1 InputBox方法与InputBox函数的区别 241
13.2.2 使用InputBox方法接收指定类型的内容 241
13.2.3 程序防错 242
13.3 Excel的打开和另存对话框 243
13.3.1 GetOpenFilename方法 243
13.3.2 GetSaveAsFilename方法 244
13.4 FileDialog对象 245
13.4.1 FileDialog对象的属性和方法 245
13.4.2 打开不同类型的对话框 245
13.4.3 指定显示的文件类型 246
13.4.4 在对话框中选择一个文件 246
13.4.5 在对话框中选择多个文件 247
13.4.6 执行所选择的文件 247
13.5 Excel内置对话框 248
13.5.1 Dialogs集合 248
13.5.2 执行功能区命令 248
第14章 创建用户窗体 250
14.1 用户窗体简介 250
14.2 用户窗体的基本操作 251
14.2.1 显示和关闭用户窗体 251
14.2.2 模式与无模式用户窗体 252
14.2.3 禁用用户窗体中的关闭按钮 252
14.3 在用户窗体中添加控件 252
14.3.1 表单控件和ActiveX控件 253
14.3.2 用户窗体中的可用控件 253
14.3.3 在用户窗体中添加控件 255
14.3.4 调整控件的位置 255
14.3.5 设置控件的大小 256
14.3.6 设置控件的共同属性 257
14.3.7 设置控件的特有属性 257
14.3.8 设置控件的Tab键顺序 258
14.3.9 为控件设置加速键 258
14.4 编写窗体与控件的事件代码 258
14.4.1 引用用户窗体中的控件 258
14.4.2 用户窗体中的控件集合 259
14.4.3 用户窗体事件 259
14.4.4 用户窗体控件的事件 260
14.5 用户窗体示例 261
14.5.1 创建欢迎界面 261
14.5.2 创建登录窗口 263
14.5.3 创建信息录入窗口 265
14.5.4 创建一个颜色选择器 267
14.5.5 创建程序设置向导 271
14.5.6 使用一个事件过程处理多个控件 277
第15章 利用事件编写自动交互的代码 280
15.1 编写事件代码前需要了解的内容 280
15.1.1 事件发生顺序 280
15.1.2 输入事件代码的方法 280
15.1.3 使用参数的事件代码 281
15.1.4 开启与关闭事件 282
15.2 工作簿事件 282
15.2.1 工作簿包含的事件 282
15.2.2 Open事件 283
15.2.3 Activate事件 284
15.2.4 Deactivate事件 284
15.2.5 BeforeClose事件 285
15.2.6 BeforeSave事件 286
15.2.7 BeforePrint事件 286
15.2.8 SheetActivate事件 286
15.2.9 SheetDeactivate事件 287
15.2.10 NewSheet事件 288
15.2.11 SheetChange事件 288
15.2.12 SheetSelectionChange事件 288
15.2.13 SheetBeforeRightClick事件 289
15.2.14 SheetBeforeDoubleClick事件 289
15.3 工作表事件 289
15.3.1 工作表包含的事件 290
15.3.2 Activate事件 290
15.3.3 Deactivate事件 290
15.3.4 Change事件 290
15.3.5 SelectionChange事件 291
15.3.6 BeforeRightClick事件 291
15.3.7 BeforeDoubleClick事件 291
15.4 图表工作表事件 291
15.5 应用程序级事件与嵌入图表事件 292
15.5.1 类模块简介 292
15.5.2 捕获应用程序级事件 293
15.5.3 捕获嵌入图表事件 295
15.6 事件应用示例 296
15.6.1 新建工作簿时自动创建指定数量和名称的工作表 296
15.6.2 关闭工作簿之前删除所有空白的工作表 297
15.6.3 在状态栏中显示选区地址 298
15.6.4 禁止修改工作表中的标题行 299
第16章 处理文件 300
16.1 VBA内置语句和FSO对象模型 300
16.1.1 用于处理文件和文件夹的VBA语句 300
16.1.2 FSO对象模型 301
16.1.3 使用前创建对FileSystemObject对象的引用 303
16.2 获取磁盘和文件信息 304
16.2.1 获取磁盘信息 304
16.2.2 获取文件夹信息 305
16.2.3 获取文件夹中的所有子文件夹的名称 306
16.2.4 获取指定文件夹下所有文件的名称 307
16.2.5 判断某个文件是否存在 308
16.2.6 判断某个路径是否存在 308
16.3 操作文件和文件夹 308
16.3.1 复制文件 309
16.3.2 重命名文件 309
16.3.3 删除文件 310
16.3.4 创建文件夹 310
16.3.5 删除文件夹 311
16.4 处理文本文件 312
16.4.1 打开文本文件 312
16.4.2 关闭文本文件 312
16.4.3 写入文本文件 313
16.4.4 读取文本文件 315
16.4.5 使用TextStream对象处理文本文件 317
第17章 与其他应用程序交互 319
17.1 自动化与前、后期绑定 319
17.1.1 自动化的基本概念 319
17.1.2 前期绑定 319
17.1.3 后期绑定 321
17.1.4 引用一个已存在的应用程序实例 322
17.2 与Office应用程序交互 323
17.2.1 与Word交互 323
17.2.2 与PowerPoint交互 325
17.2.3 与Access交互 326
17.3 在Excel中启动和激活其他应用程序 327
17.3.1 启动其他应用程序 327
17.3.2 激活其他应用程序 328
第18章 注册表 330
18.1 注册表简介 330
18.1.1 为什么使用注册表 330
18.1.2 进入注册表的方法 330
18.1.3 注册表的结构 330
18.1.4 注册表包含的数据类型 332
18.1.5 注册表的备份与恢复 332
18.2 使用VBA内置函数和语句操作注册表 333
18.2.1 SaveSetting语句 333
18.2.2 GetSetting函数 334
18.2.3 GetAllSettings函数 334
18.2.4 DeleteSetting语句 335
18.2.5 让网格线设置应用于工作簿的所有工作表 335
18.2.6 记忆用户选择的打开文件的默认位置 336
18.3 使用WSH的WshShell对象操作注册表 338
18.3.1 RegWrite方法 339
18.3.2 RegRead方法 340
18.3.3 RegDelete方法 340
18.3.4 限制Excel工作簿的使用次数 340
第19章 加载宏 342
19.1 加载宏简介 342
19.1.1 为什么使用加载宏 342
19.1.2 对比加载宏与普通工作簿 343
19.2 创建加载宏 343
19.3 安装与管理加载宏 345
19.3.1 安装加载宏 346
19.3.2 修改加载宏 347
19.3.3 卸载加载宏 347
19.3.4 删除加载宏 347
19.3.5 使用VBA操作加载宏 347
附录1 VBA函数速查 349
附录2 VBA语句速查 352
附录3 VBA错误代码速查 355
附录4 Excel VBA常用对象速查 358