Excel VBA程序开发自学宝典(第4版)

目录 第1 章 初步感受VBA 的魅力 .......................................................................................... 1 1.1 批量任务一键执行 ................................................................................................... 1 1.1.1 准备工作 ...................................................................................................... 1 1.1.2 程序测试 ...................................................................................................... 1 1.1.3 案例点评 ...................................................................................................... 2 1.2 数据汇总自动完成 ................................................................................................... 2 1.2.1 案例需求 ...................................................................................................... 2 1.2.2 程序测试 ...................................................................................................... 3 1.2.3 案例点评 ...................................................................................................... 3 1.2.4 开发专业程序 ............................................................................................... 4 第2 章 VBA 程序入门 ...................................................................................................... 5 2.1 如何存放代码 ........................................................................................................... 5 2.1.1 认识模块 ...................................................................................................... 5 2.1.2 管理模块 ...................................................................................................... 6 2.2 如何产生代码 ........................................................................................................... 7 2.2.1 复制现有的代码 ........................................................................................... 7 2.2.2 录制宏 .......................................................................................................... 7 2.2.3 手工编写代码 ............................................................................................... 9 2.2.4 从模板中获取代码 ..................................................................................... 10 2.3 如何调用代码 ......................................................................................................... 10 2.3.1 ＜F5＞键 .................................................................................................... 10 2.3.2 ＜Alt+F8＞组合键 ...................................................................................... 11 2.3.3 自定义快捷键 ............................................................................................. 12 2.3.4 按钮 ............................................................................................................ 12 2.3.5 菜单 ............................................................................................................ 13 2.4 如何保存代码 ......................................................................................................... 13 2.4.1 工作簿格式 ................................................................................................ 13 2.4.2 解决丢失代码问题 ..................................................................................... 14 2.4.3 显示文件扩展名 ......................................................................................... 14 2.5 如何放行代码 ......................................................................................................... 15 2.5.1 调整“宏设置” ......................................................................................... 16 2.5.2 添加受信任位置 ......................................................................................... 16 2.6 如何查询代码帮助 ................................................................................................. 17 2.6.1 Excel 不同版本中的帮助差异 .................................................................... 17 2.6.2 如何调用帮助系统 ..................................................................................... 17 2.7 如何简化开发难度 ................................................................................................. 19 2.7.1 录制宏 ........................................................................................................ 19 2.7.2 调用内置提示 ............................................................................................. 19 2.7.3 调用笔记 .................................................................................................... 21 第3 章 VBA 的程序结构详解 ......................................................................................... 22 3.1 Sub 过程基本语法 ................................................................................................. 22 3.1.1 认识程序结构 ............................................................................................. 22 3.1.2 为VBA 程序添加注释 ................................................................................ 23 3.2 过程的作用范围 ..................................................................................................... 24 3.2.1 何谓作用范围 ............................................................................................. 24 3.2.2 公有过程与私有过程的区别 ...................................................................... 24 3.3 过程的命名规则 ..................................................................................................... 25 3.4 过程的参数 ............................................................................................................ 25 3.5 过程的执行流程 ..................................................................................................... 26 3.5.1 正常的执行流程 ......................................................................................... 26 3.5.2 改变程序的执行流程 ................................................................................. 26 3.6 中断过程 ................................................................................................................ 27 3.6.1 结束过程：End Sub .................................................................................. 27 3.6.2 中途结束过程：Exit Sub ............................................................................ 27 3.6.3 中途结束一切：End .................................................................................. 27 3.6.4 暂停过程：Stop ......................................................................................... 27 3.6.5 手动暂停程序：Ctrl+Break ....................................................................... 28 第4 章 VBA 四大基本概念 ............................................................................................. 29 4.1 Excel 的对象 .......................................................................................................... 29 4.1.1 什么是对象 ................................................................................................ 29 4.1.2 对象与对象集合 ......................................................................................... 30 4.1.3 对象的层次结构 ......................................................................................... 32 4.1.4 父对象与子对象 ......................................................................................... 33 4.1.5 活动对象 .................................................................................................... 34 4.2 对象的方法和属性 ................................................................................................. 35 4.2.1 属性与方法的区别 ..................................................................................... 36 4.2.2 查询方法与属性的两种方法 ...................................................................... 36 4.2.3 方法与属性的应用差异 .............................................................................. 37 4.3 对象的事件 ............................................................................................................ 40 4.3.1 什么是事件 ................................................................................................ 40 4.3.2 事件的分类及其层级关系 .......................................................................... 41 4.3.3 工作簿事件与工作表事件一览 .................................................................. 41 4.3.4 工作簿与工作表事件的作用对象 .............................................................. 42 4.3.5 快速掌握事件过程 ..................................................................................... 44 4.3.6 何时需要使用事件过程 .............................................................................. 44 第5 章 通过变量强化程序功能 ...................................................................................... 45 5.1 数据类型 ................................................................................................................ 45 5.1.1 为什么要区分数据类型 .............................................................................. 45 5.1.2 认识VBA 的数据类型 ................................................................................ 45 5.2 声明变量 ................................................................................................................ 47 5.2.1 变量的定义 ................................................................................................ 47 5.2.2 变量的声明方式 ......................................................................................... 47 5.2.3 变量的赋值方式与初始值 .......................................................................... 49 5.2.4 如何确定变量的数据类型正确 .................................................................. 50 5.2.5 正确声明变量的数据类型的优势 .............................................................. 50 5.2.6 变量的作用域 ............................................................................................. 52 5.2.7 变量的生命周期 ......................................................................................... 53 5.3 对象变量 ................................................................................................................ 53 5.3.1 如何区分对象变量和数据变量 .................................................................. 54 5.3.2 对变量赋值 ................................................................................................ 54 5.3.3 使用对象变量的优势 ................................................................................. 55 5.4 声明常量 ................................................................................................................ 56 5.4.1 常量的定义与用途 ..................................................................................... 56 5.4.2 常量的声明方式 ......................................................................................... 57 5.2.3 常量的命名规则 ......................................................................................... 57 第6 章 详解五大常用对象 .............................................................................................. 59 6.1 对象基础知识 ......................................................................................................... 59 6.1.1 对象的结构 ................................................................................................ 59 6.1.2 对象与对象的集合 ..................................................................................... 60 6.1.3 引用集合中的单一对象 .............................................................................. 60 6.1.4 父对象与子对象 ......................................................................................... 60 6.1.5 活动对象 .................................................................................................... 61 6.1.6 使用变量简化对象的引用 .......................................................................... 61 6.1.7 使用With 语句简化对象的引用 ................................................................. 62 6.2 单元格对象 ............................................................................................................ 63 6.2.1 Range("A1")方式引用单元格 ..................................................................... 64 6.2.2 Cells(1,1)方式引用单元格 ......................................................................... 66 6.2.3 [a1]方式引用单元格 .................................................................................. 67 6.2.4 Range（"A1"）、Cells（1,1）与[a1]比较 ................................................ 68 6.2.5 Selection 与ActiveCell：当前选区与活动单元格 ..................................... 69 6.2.6 已用区域与当前区域 ................................................................................. 69 6.2.7 SpecialCells：按条件引用区域 ................................................................. 70 6.2.8 CurrentArray：引用数组区域 .................................................................... 74 6.2.9 Resize：重置区域大小 .............................................................................. 75 6.2.10 Offset：根据偏移量引用新区域 .............................................................. 76 6.2.11 Union：多区域合集 ................................................................................. 78 6.2.12 Intersect：单元格、区域的交集 .............................................................. 79 6.2.13 End：引用源区域的区域尾端的单元格 .................................................. 80 6.2.14 EntireRow/EntireColumn：扩展至整行、整列 ........................................ 81 6.2.15 RangeSelection：工作表中的选定单元格 .............................................. 82 6.2.16 VisibleRange：指定窗口的可见区域 ...................................................... 83 6.3 批注对象 ................................................................................................................ 84 6.3.1 批注对象的特点 ......................................................................................... 84 6.3.2 判断单元格中是否有批注 .......................................................................... 85 6.3.3 AddComment：对单元格添加批注 .......................................................... 85 6.3.4 AutoSize：让批注框自动缩放 ................................................................... 86 6.3.5 AutoShapeType：修改批注的外观 ........................................................... 86 6.3.6 ClearComments：清空表中所有批注 ....................................................... 86 6.4 图形对象 ................................................................................................................ 87 6.4.1 Shapes：图形对象集合 ............................................................................ 87 6.4.2 图形对象的名称 ......................................................................................... 87 6.4.3 隐藏的图形对象集合 ................................................................................. 88 6.5 表对象 .................................................................................................................... 90 6.5.1 表的类别 .................................................................................................... 90 6.5.2 Worksheets：工作表集合.......................................................................... 90 6.5.3 引用工作表子集 ......................................................................................... 91 6.5.4 ActiveSheet：活动表 ................................................................................. 91 6.5.5 工作表的特性 ............................................................................................. 92 6.5.6 新建工作表 ................................................................................................ 93 6.6 工作簿对象 ............................................................................................................ 93 6.6.1 工作簿格式与特性 ..................................................................................... 93 6.6.2 新建工作簿 ................................................................................................ 94 6.6.3 Workbooks：工作簿集合 .......................................................................... 94 6.6.4 引用工作簿子集 ......................................................................................... 94 6.6.5 ActiveWorkbook：活动工作簿 .................................................................. 94 第7 章 常用语句解析 ..................................................................................................... 95 7.1 输出/输入语句........................................................................................................ 95 7.1.1 MsgBox 函数 .............................................................................................. 95 7.1.2 Application.InputBox 方法 ......................................................................... 98 7.2 条件判断语句 ....................................................................................................... 103 7.2.1 IIf 函数的语法与应用 ............................................................................... 103 7.2.2 IIf 函数的限制 ........................................................................................... 105 7.2.3 If Then 语句的语法详解 ........................................................................... 106 7.2.4 If Then 应用案例 ...................................................................................... 106 7.2.5 If Then Else 语句的语法与应用 ............................................................... 107 7.2.6 多条件嵌套的条件判断语句 .................................................................... 110 7.2.7 Select Case 语法详解 .............................................................................. 114 7.2.8 Select Case 与If Then Else 之比较 ......................................................... 117 7.2.9 借用Choose 函数简化条件选择 ............................................................. 118 7.3 循环语句 .............................................................................................................. 120 7.3.1 For Next 语句 ........................................................................................... 120 7.3.2 For Each Next 语句 .................................................................................. 127 7.3.3 Do Loop 语法详解 ................................................................................... 132 7.4 错误处理语句 ....................................................................................................... 140 7.4.1 错误类型与原因 ....................................................................................... 140 7.4.2 err 对象及其属性、方法 .......................................................................... 141 7.4.3 认识Error 函数 ........................................................................................ 142 7.4.4 On Error GoTo line ................................................................................... 142 7.4.5 On Error Resume Next ............................................................................. 146 7.4.6 On Error GoTo 0 ...................................................................................... 151 7.5 浏览语句 .............................................................................................................. 151 7.5.1 认识FileDialog 对象 ................................................................................ 151 7.5.2 选择路径 .................................................................................................. 152 7.5.3 选择文件 .................................................................................................. 153 7.5.4 按类型选择文件 ....................................................................................... 154 第8 章 开发自启动程序 ............................................................................................... 157 8.1 让宏自动执行 ....................................................................................................... 157 8.1.1 Auto 自动宏 .............................................................................................. 157 8.1.2 升级版自动宏：事件 ............................................................................... 158 8.1.3 事件的禁用与启用 ................................................................................... 160 8.1.4 事件的特例 .............................................................................................. 161 8.2 工作表事件 .......................................................................................................... 162 8.2.1 在状态栏提示*大值的单元格地址 ......................................................... 162 8.2.2 快速输入出勤表 ....................................................................................... 163 8.2.3 在状态栏显示选区的字母、数字、汉字个数 ......................................... 164 8.2.4 实时监控单元格每一次编辑的数据与时间 ............................................. 166 8.2.5 利用数字简化公司名输入 ........................................................................ 168 8.2.6 输入数据时自动跳过带公式的单元格 ..................................................... 170 8.3 工作簿事件 .......................................................................................................... 171 8.3.1 新建工作表时自动设置页眉 .................................................................... 171 8.3.2 未汇总则禁止打印与关闭工作簿 ............................................................ 172 8.3.3 自动选中相同值并计数 ............................................................................ 174 8.3.4 设计未启用宏就无法打开的工作簿 ......................................................... 176 8.4 定时执行的程序 ................................................................................................... 177 8.4.1 基本语法 .................................................................................................. 178 8.4.2 每天在13:28 语音提示开会 .................................................................... 178 8.4.3 在单元格中显示当前时期 ........................................................................ 179 第9 章 综合应用案例 ................................................................................................... 181 9.1 Application 应用案例 ........................................................................................... 181 9.1.1 计算字符表达式 ....................................................................................... 181 9.1.2 合并相同且相邻的单元格 ........................................................................ 182 9.1.3 定时打印文件 ........................................................................................... 184 9.1.4 模拟键盘快捷键打开高级选项 ................................................................ 185 9.1.5 使用快捷键合并与取消合并单元格 ......................................................... 186 9.1.6 查找至少两月未付货款的客户名称 ......................................................... 188 9.2 Range 对象应用案例 ........................................................................................... 190 9.2.1 合并工作表 .............................................................................................. 190 9.2.2 合并区域且保留所有数据 ........................................................................ 193 9.2.3 合并计算多区域的值 ............................................................................... 194 9.2.4 模糊查找公司名称并罗列出来 ................................................................ 196 9.2.5 反向选择单元格 ....................................................................................... 198 9.2.6 插入图片并调整为选区大小 .................................................................... 200 9.2.7 提取唯一值 .............................................................................................. 202 9.2.8 隐藏所有公式结果为错误的单元格 ......................................................... 203 9.3 Comment 对象应用案例 ..................................................................................... 205 9.3.1 在所有批注末尾添加指定日期 ................................................................ 205 9.3.2 生成图片批注 ........................................................................................... 207 9.3.3 添加个性化批注 ....................................................................................... 208 9.3.4 批量修改当前表的所有批注外形 ............................................................ 210 9.4 WorkSheet 对象应用案例 .................................................................................... 213 9.4.1 新建工作表并且命名为今日日期 ............................................................ 213 9.4.2 批量保护工作表与解除保护 .................................................................... 214 9.4.3 为所有工作表设置水印 ............................................................................ 216 9.4.4 批量命名工作表 ....................................................................................... 218 9.4.5 判断筛选条件 ........................................................................................... 220 9.5 Workbook 对象应用案例 ..................................................................................... 223 9.5.1 拆分工作簿 .............................................................................................. 223 9.5.2 每10 分钟备份一次工作簿 ...................................................................... 225 9.5.3 5 分钟未编辑工作簿则自动备份 ............................................................. 226 9.5.4 记录文件打开次数 ................................................................................... 228 9.5.5 不打开工作簿而提取数据 ........................................................................ 230 9.5.6 建立指定文件夹下所有工作簿目录和工作表目录 .................................. 232 第10 章 编程规则与代码优化 ...................................................................................... 234 10.1 代码编写规则 ..................................................................................................... 234 10.1.1 对代码添加注释 ..................................................................................... 234 10.1.2 长代码分行 ............................................................................................ 237 10.1.3 代码缩进对齐 ......................................................................................... 238 10.1.4 声明有意义的变量名称 .......................................................................... 239 10.1.5 If Then End If 类配对语句的输入方式 ................................................... 240 10.1.6 输入属性与方法的技巧 .......................................................................... 240 10.1.7 无提示的词组的输入技巧 ...................................................................... 242 10.1.8 善用公共变量 ......................................................................................... 242 10.1.9 使用常量名称替代数值 .......................................................................... 243 10.1.10 尽可能兼容Excel 2003 到Excel 2019 之间的所有版本 ..................... 243 10.2 优化代码思路 ..................................................................................................... 244 10.2.1 强制声明变量 ......................................................................................... 244 10.2.2 善用常量 ................................................................................................ 244 10.2.3 关闭屏幕更新 ......................................................................................... 244 10.2.4 利用With 减少对象读取次数................................................................. 245 10.2.5 利用变量减少对象读取次数 .................................................................. 245 10.2.6 善用带$的字符串处理函数 .................................................................... 246 10.2.7 不使用Select 和Activate 直接操作对象 ............................................... 246 10.2.8 将与循环无关的语句放到循环语句外 ................................................... 247 10.2.9 利用Instr 函数简化字符串判断 ............................................................. 247 10.2.10 使用Replace 简化字符串连接 ............................................................ 248 10.3 借用参数简化程序调用 ..................................................................................... 248 10.3.1 参数的概念与用途 ................................................................................. 248 10.3.2 参数的语法结构 ..................................................................................... 249 10.3.3 设计带有必选参数的过程 ...................................................................... 249 10.3.4 设计带有可选参数的过程 ...................................................................... 251 10.3.5 设计带有不确定数量参数的过程 .......................................................... 253 10.3.6 参数的赋值方式 ..................................................................................... 254 第11 章 高阶应用1：数组 ........................................................................................... 256 11.1 数组基础 ............................................................................................................ 256 11.1.1 何谓数组 ................................................................................................ 256 11.1.2 数组的特点 ............................................................................................ 256 11.1.3 一维数组 ................................................................................................ 257 11.1.4 二维数组 ................................................................................................ 259 11.1.5 数组的参数 ............................................................................................ 260 11.1.6 声明数组变量 ......................................................................................... 261 11.1.7 动态数组与静态数组的区别 .................................................................. 263 11.1.8 释放动态数组的存储空间 ...................................................................... 268 11.2 数组函数 ............................................................................................................ 268 11.2.1 用函数创建数组 ..................................................................................... 268 11.2.2 获取数组元素 ......................................................................................... 270 11.2.3 判断变量是否为数组 ............................................................................. 270 11.2.4 转置数组 ................................................................................................ 271 11.2.5 获取数组的上标与下标 .......................................................................... 272 11.2.6 转换文本与数组 ..................................................................................... 273 11.2.7 筛选数组 ................................................................................................ 275 11.3 数组实战 ............................................................................................................ 276 11.3.1 将指定区域的单词统一为首字母大写 ................................................... 276 11.3.2 罗列不及格人员姓名、科目和成绩 ....................................................... 277 11.3.3 跨表搜索学员信息 ................................................................................. 278 11.3.4 将职员表按学历拆分成多个工作表 ....................................................... 280 11.3.5 将选区的数据在文本与数值间互换 ....................................................... 281 11.3.6 获取两列数据的相同项 .......................................................................... 283 11.3.7 无人值守的多工作簿自动汇总 .............................................................. 285 第12 章 高阶应用2：正则表达式 ................................................................................ 288 12.1 何谓正则表达式 ................................................................................................. 288 12.1.1 概念 ........................................................................................................ 288 12.1.2 特点 ........................................................................................................ 288 12.1.3 调用方式 ................................................................................................ 289 12.2 语法基础 ............................................................................................................ 290 12.2.1 调用正则表达式的基本格式 .................................................................. 290 12.2.2 正则表达式的对象、属性和方法 .......................................................... 291 12.2.3 匹配的优先顺序 ..................................................................................... 294 12.2.4 借用元字符强化搜索功能 ...................................................................... 295 12.3 正则表达式应用 ................................................................................................. 311 12.3.1 乱序字符串取值并汇总 .......................................................................... 311 12.3.2 计算建筑面积 ......................................................................................... 312 12.3.3 计算括号中的数字合计 .......................................................................... 313 12.3.4 删除字符串首尾的空白字符 .................................................................. 314 12.3.5 将字符串中的多段数字分列 .................................................................. 315 12.3.6 提取E-mail 地址 .................................................................................... 315 12.3.7 提取文件的路径和名称 .......................................................................... 316 12.3.8 汇总人民币 ............................................................................................ 317 12.3.9 开发分列函数 ......................................................................................... 318 12.3.10 删除重复字词 ....................................................................................... 319 第13 章 高阶应用3：字典 .......................................................................................... 3