中文版Excel 2019高级VBA编程宝典(第9版)

● 记录简单的宏以及执行其他相关操作
● 动态操作工作簿和工作表
● 自动生成透视表和图表
● 管理外部数据和外部文件
● 从Excel直接发送邮件
● 创建自定义Excel功能区界面
● 开发和发布自定义的Excel加载项

Michael Alexander是一名微软认证应用程序开发人员(MCAD)，著有Microsoft Access和 Microsoft Excel高级业务分析的相关书籍。他在Microsoft Office咨询和开发领域拥有超过20年的经验。

　　Dick Kusleika荣膺12次Microsoft Excel MVP，25年来通过各种在线论坛、博客、图书以及会议为微软产品用户提供咨询服务。

借助VBA成为Excel高级用户

利用VBA语言的卓越功能，将自己的Excel技能提升到全新水平。《中文版Excel 2019高级VBA编程宝典(第9版)》是一本综合性指南，帮助读者进行自动化数据处理，开发功能齐全的Excel应用。本书针对Excel 2019做了全面更新，是独一无二的资源；无论你开发大型还是小型Excel应用，本书都将为你提供所需的一切内容。
　　本书以读者为中心，选材精当，直抒主题，摒弃华而不实的、冗长的解释，为你开始学习提供所需的提示、技巧和实际范例(包括所有代码和范例模板)，助你从普通数据分析人员华丽蜕变为Excel编程专家。

前 言

对大多数人来讲，想要学习Excel VBA编程技术都起因于需要执行一些利用Excel的标准工具无法完成的任务。对于我们每个人来讲，任务都各不相同。任务可能是需要为数据集中的所有行自动创建单独的工作簿，也可能是需要自动通过电子邮箱发送几十封报告。不管你面对的是什么样的任务，基本上都可以肯定已经有人使用Excel VBA来解决跟你一样的问题了。

就Excel VBA来讲，最美妙的事莫过于你不是必须成为专家后才能解决问题。你可以为解决一个具体问题而学习相关知识，也可以为处理各种自动化场景而深入学习各种技巧。

无论你的目标如何，本书所讲解的Excel 2019编程技术都可以帮助你驾驭VBA语言的强大功能来使任务自动化，并使工作更省心更有效率。

本书涵盖的内容

《中文版Excel 2019高级VBA编程宝典(第9版)》主要介绍VBA(Visual Basic for Applications)，这是一种构建于Excel(和其他Microsoft Office应用程序)中的编程语言。更具体地说，《中文版Excel 2019高级VBA编程宝典(第9版)》将展示如何编写使Excel中各种任务自动化的程序。《中文版Excel 2019高级VBA编程宝典(第9版)》涵盖了从录制简单的宏乃至创建复杂的、面向用户的应用程序和实用程序等所有内容。

你可按自己所需来学习《中文版Excel 2019高级VBA编程宝典(第9版)》的内容，可以从头读到尾，也可以从中挑出觉得对自己有用的部分。VBA编程通常都是面向任务的，因此在面对一个具有挑战性的任务时，可以先从《中文版Excel 2019高级VBA编程宝典(第9版)》中查一查哪些章节是专门针对你所面对的问题的。

《中文版Excel 2019高级VBA编程宝典(第9版)》并没有涵盖VSTO(Visual Studio Tools for Office)中的内容。VSTO是一门较新技术，它使用了Visual Basic .NET和Microsoft Visual C#。VSTO也可用于控制Excel和其他Microsoft Office应用程序。

你可能知道，Excel 2019也可用于其他平台。例如，你可在浏览器中使用微软的Excel Web App，甚至在iPad和平板电脑上运行Excel。这些版本不支持VBA。也就是说，《中文版Excel 2019高级VBA编程宝典(第9版)》介绍的是针对Windows平台的Excel 2019桌面版本。

《中文版Excel 2019高级VBA编程宝典(第9版)》读者对象

《中文版Excel 2019高级VBA编程宝典(第9版)》并不是为Excel的初学者编写的。如果读者对使用Excel没有任何经验，那么最好先阅读Wiley出版社出版的由John Walkenbach撰写的《中文版Excel 2019宝典》，该书全面概括了Excel的所有功能，它是为各个层次的读者服务的。

为发挥《中文版Excel 2019高级VBA编程宝典(第9版)》的最大功效，读者应该是有一定使用经验的Excel用户。《中文版Excel 2019高级VBA编程宝典(第9版)》假设读者已经掌握了如下技能：

● 如何创建工作簿、插入工作表、保存文件等

● 如何在工作簿中导航

● 如何使用Excel功能区用户界面

● 如何输入公式

● 如何使用Excel的工作表函数

● 如何给单元格和单元格区域命名

● 如何使用基本的Windows功能，例如文件管理方法和剪贴板的使用

所需的资源

Excel 有多种版本，包括 Web 版本和平板电脑及手机版本。《中文版Excel 2019高级VBA编程宝典(第9版)》是专门为适用于 Windows 的 Microsoft Excel 2019 的桌面版本编写的。如果你计划开发将在早期版本的 Excel 中使用的应用程序，我们强烈建议你使用目标受众所使用的早期版本的 Excel。在过去几年中，Microsoft 对具有 Office 365 的 Excel 网络版本采用了敏捷发布周期，几乎每月生成发布更新。

为充分学习《中文版Excel 2019高级VBA编程宝典(第9版)》的知识，应该安装Excel的完整版。如果想要学习《中文版Excel 2019高级VBA编程宝典(第9版)》中的高级技术(如Excel)与其他Office程序之间的通信，你还需要安装Office软件。

你使用哪种 Windows 版本并不重要。任何可以运行 Windows 的计算机系统都足够了，但使用具有充足内存的快速计算机会好得多。Excel 是一个大型程序，在较慢的系统或内存最少的系统上使用它可能会非常令人沮丧。

请注意，《中文版Excel 2019高级VBA编程宝典(第9版)》不适用于Mac计算机上的Microsoft Excel版本。

《中文版Excel 2019高级VBA编程宝典(第9版)》约定

请花点时间阅读本节内容，这里介绍《中文版Excel 2019高级VBA编程宝典(第9版)》使用的一些约定。

Excel命令

Excel使用上下文相关的功能区系统。顶部的单词(如“插入”“视图”等)称为“选项卡”。单击某个选项卡，图标的功能区就将显示最适合当前任务的命令。每个图标都有一个名称，通常显示在该图标的旁边或下方。图标是按组排列的，组名显示在图标下方。

《中文版Excel 2019高级VBA编程宝典(第9版)》约定：先指明的是选项卡的名称，随后是组的名称，最后是图标的名称。例如，用于处理单元格中自动换行的命令如下所示：

“开始”|“对齐方式”|“自动换行”

单击第一个选项卡，即“文件”选项卡，将进入一个名为Backstage的新界面。Backstage窗口的左侧有一列命令。为了指明Backstage命令，先使用单词“文件”，然后是命令名。例如，下面的命令将显示“Excel选项”对话框：

“文件”|“选项”

VBE命令

VBE是在其中使用VBA代码的窗口。VBE使用传统的“菜单和工具栏”界面。下面的命令指单击“工具”菜单并选中“引用”菜单项：

“工具”|“引用”

键盘的约定

需要使用键盘来输入数据。此外，使用键盘还可以直接操作菜单和对话框，如果双手已经放到键盘上，那么这种方法会更方便一些。

输入

较长的输入通常以等宽字体显示在单独一行中。例如，书中会以下文的方式提示输入以下公式：

=VLOOKUP(StockNumber,PriceList,2)

VBA代码

《中文版Excel 2019高级VBA编程宝典(第9版)》包含许多VBA代码片段以及完整的过程清单。每个清单以等宽字体显示，每行代码占据单独一行(笔者直接从VBA模块中复制这些清单，并把它们粘贴到自己的字处理程序中)。为使代码更易于阅读，书中使用一个或多个制表符进行缩进。缩进是可选的，但确实可以帮助限定一起出现的语句。

当《中文版Excel 2019高级VBA编程宝典(第9版)》中的单独一行放不下一行代码时，《中文版Excel 2019高级VBA编程宝典(第9版)》使用标准的VBA续行符：在一行的结尾，采用空格后跟下画线字符的方式表明代码行延伸到了下一行。例如，下面两行是一条代码语句：

columnCount = Application.WorksheetFunction._

CountA(Range ("A:A"))+1

可按上面的显示把代码输入到两行中，或者删除下画线字符并把代码输入到一行中。

函数、文件名和命名单元格区域

Excel的工作表函数以大写字母显示，如“在单元格C20中输入一个SUM公式”。对于VBA过程名、属性、方法和对象，《中文版Excel 2019高级VBA编程宝典(第9版)》经常混合使用大写和小写字母以便读者阅读这些名称。

鼠标约定

你将会遇到下列一些与鼠标相关的术语。

鼠标指针：这是移动鼠标时在屏幕上移动的小图形。鼠标指针通常是箭头，但当你移动到屏幕的某些区域或执行某些操作时，它会改变形状。

鼠标指向：移动鼠标，使鼠标指针位于指定项上。例如，“指向工具栏上的‘保存’按钮。”

单击：按左键一次并立即松开。

右键单击：按右键一次并立即松开。Excel 中使用鼠标右键打开适合当前选择的任何内容的快捷菜单。

双击：连续按下鼠标左键两次。

拖动：按下鼠标左键，并在移动鼠标时按住它。拖动通常用于选择单元格区域或更改对象的大小。

图标的含义

《中文版Excel 2019高级VBA编程宝典(第9版)》使用一些图标来引起读者的注意，告诉读者这些信息非常重要。

注意：

使用“注意”图标来告诉读者这些信息很重要，也许有助于读者掌握随后任务的概念，或有助于理解后面资料的一些基础知识。

提示：

“提示”图标指出更有效的工作方式或可能不是很明显的方法。

在线资源：

这类图标表明示例文件可在下载的示例文件包中找到。具体说明参见前言中的“关于下载的示例文件包”一节。

警告：

“警告”图标表明在操作时不小心可能会导致出现问题。

交叉参考：

这类图标表明请读者参阅其他章节中关于某个主题的详细信息。

《中文版Excel 2019高级VBA编程宝典(第9版)》的组织结构

《中文版Excel 2019高级VBA编程宝典(第9版)》的章节分为四个主要部分和一个附录。

第I部分 Excel VBA基础知识

第I部分介绍Excel VBA，为创建和管理Excel子例程和函数提供编程基础知识。第1章全面介绍Excel应用开发方面的各种概念。第2～6章讨论在进行VBA编程时需要了解的各种知识。第7章则列举许多有用的示例助你巩固前面所学的VBA知识点。

第II部分 高级VBA技术

第Ⅱ部分涵盖一些VBA高级编程技术。第8章和第9章讨论如何使用VBA来处理透视表和图表(包括迷你图)。第10章讨论在与其他应用程序(如Word和Outlook)交互时所采用的各种技术。第11章介绍如何处理文件和外部数据源。

第III部分 操作用户窗体

该部分的4章内容主要介绍自定义对话框(也称为用户窗体)。第12章介绍创建自定义用户窗体的一些内置方法。第13章介绍用户窗体以及可供使用的各种控件。第14章和第15章则列举从基本到高级的自定义对话框的大量示例。

第IV部分 开发Excel应用程序

该部分讲述创建面向用户的应用程序的重要内容。第16章手把手教你创建加载项。第17章和第18章讨论如何修改Excel的功能区和快捷菜单。第19章介绍向应用程序提供在线帮助的几种不同方法。第20章介绍类模块。第21章探讨在进行Excel VBA编程时与兼容性相关的一些信息。

附录A是一份参考指南，列出作为VBA中关键字的所有语句和函数。

关于下载的示例文件包

《中文版Excel 2019高级VBA编程宝典(第9版)》中讨论过的几乎所有知识都配有示例。可以下载《中文版Excel 2019高级VBA编程宝典(第9版)》中所包含的很多有用示例。

《中文版Excel 2019高级VBA编程宝典(第9版)》的配书网站是www.wiley.com/go/excel2019powerprogramming。

也可扫《中文版Excel 2019高级VBA编程宝典(第9版)》封底的二维码直接下载。

目 录

第 I 部分 Excel VBA基础知识
第1章 电子表格应用开发入门 3
1.1 什么是电子表格应用 3
1.2 应用开发的步骤 4
1.3 确定用户的需求 4
1.4 对满足这些需求的应用进行规划 5
1.5 确定最适用的用户界面 6
1.5.1 自定义功能区 7
1.5.2 自定义快捷菜单 7
1.5.3 创建快捷键 7
1.5.4 创建自定义对话框 8
1.5.5 在工作表中使用ActiveX控件 8
1.5.6 开始开发工作 9
1.6 关注最终用户 10
1.6.1 测试应用 10
1.6.2 应用的安全问题 11
1.6.3 如何让应用程序看起来更简明美观 12
1.6.4 创建用户帮助系统 12
1.6.5 将开发成果归档 13
1.6.6 给用户发布应用程序 13
1.6.7 在必要时对应用进行更新 13
1.7 其他开发问题 14
1.7.1 用户安装的Excel版本 14
1.7.2 语言问题 14
1.7.3 系统速度 14
1.7.4 显示模式 15
第2章 VBA概述 16
2.1 宏录制器 16
2.1.1 创建你的第一个宏 16
2.1.2 比较宏录制的绝对模式和相对模式 19
2.1.3 关于宏录制的其他概念 22
2.2 Visual Basic编辑器概述 26
2.2.1 了解VBE组件 26
2.2.2 使用工程资源管理器 27
2.2.3 使用代码窗口 29
2.2.4 自定义VBA环境 31
2.2.5 “编辑器格式”选项卡 32
2.2.6 “通用”选项卡 33
2.2.7 “可连接的”选项卡 33
2.3 VBA的基础知识 34
2.3.1 了解对象 34
2.3.2 了解集合 35
2.3.3 了解属性 35
2.3.4 了解方法 37
2.4 使用Range对象 38
2.4.1 找到Range对象的属性 39
2.4.2 Range属性 39
2.4.3 Cells属性 40
2.4.4 Offset属性 42
2.5 需要记住的基本概念 43
2.6 学习更多信息 44
2.6.1 阅读本书剩余的章节 44
2.6.2 让Excel来帮助编写宏 45
2.6.3 使用帮助系统 45
2.6.4 使用对象浏览器 45
2.6.5 从网上获取 46
2.6.6 利用用户论坛 47
2.6.7 访问专家博客 47
2.6.8 通过YouTube查找视频 48
2.6.9 通过Microsoft Office Dev Center获取信息 48
2.6.10 解析其他的Excel文件 48
2.6.11 咨询周围的Excel人才 48
第3章 VBA编程基础 49
3.1 VBA语言元素概览 49
3.2 注释 51
3.3 变量、数据类型和常量 52
3.3.1 定义数据类型 53
3.3.2 声明变量 54
3.3.3 变量的作用域 56
3.3.4 使用常量 58
3.3.5 使用字符串 60
3.3.6 使用日期 60
3.4 赋值语句 61
3.5 数组 63
3.5.1 声明数组 63
3.5.2 声明多维数组 64
3.5.3 声明动态数组 64
3.6 对象变量 64
3.7 用户自定义的数据类型 66
3.8 内置函数 66
3.9 处理对象和集合 69
3.9.1 With-End With结构 69
3.9.2 For Each-Next结构 70
3.10 控制代码的执行 71
3.10.1 GoTo语句 72
3.10.2 If-Then结构 72
3.10.3 Select Case结构 76
3.10.4 指令块的循环 79
第4章 VBA的子过程 86
4.1 关于过程 86
4.1.1 子过程的声明 87
4.1.2 过程的作用域 88
4.2 执行子过程 89
4.2.1 通过“运行子过程/用户窗体”命令执行过程 89
4.2.2 从“宏”对话框执行过程 89
4.2.3 用Ctrl+快捷键组合执行过程 90
4.2.4 从功能区执行过程 91
4.2.5 从自定义快捷菜单中执行过程 91
4.2.6 从另一个过程中执行过程 91
4.2.7 通过单击对象执行过程 95
4.2.8 在事件发生时执行过程 96
4.2.9 从“立即窗口”执行过程 97
4.3 向过程中传递参数 97
4.4 错误处理技术 100
4.4.1 捕获错误 101
4.4.2 错误处理示例 102
4.5 使用子过程的实际示例 104
4.5.1 目标 104
4.5.2 工程需求 105
4.5.3 已经了解的信息 105
4.5.4 解决方法 105
4.5.5 初步的录制工作 106
4.5.6 初始设置 107
4.5.7 代码的编写 108
4.5.8 排序过程的编写 109
4.5.9 更多测试 113
4.5.10 修复问题 113
4.6 实用程序的可用性 116
4.7 对工程进行评估 117
第5章 创建函数过程 118
5.1 子过程与函数过程的比较 118
5.2 为什么创建自定义的函数 119
5.3 自定义函数示例 119
5.3.1 在工作表中使用函数 119
5.3.2 在VBA过程中使用函数 120
5.3.3 分析自定义函数 121
5.4 函数过程 122
5.4.1 函数的作用域 123
5.4.2 执行函数过程 124
5.5 函数过程的参数 126
5.6 函数示例 126
5.6.1 无参数的函数 126
5.6.2 带有一个参数的函数 128
5.6.3 带有两个参数的函数 131
5.6.4 使用数组作为参数的函数 132
5.6.5 带有可选参数的函数 133
5.6.6 返回VBA数组的函数 134
5.6.7 返回错误值的函数 136
5.6.8 带有不定数量参数的函数 138
5.7 模拟Excel的SUM函数 139
5.8 扩展后的日期函数 142
5.9 函数的调试 143
5.10 使用“插入函数”对话框 144
5.10.1 使用MacroOptions方法 144
5.10.2 指定函数类别 146
5.10.3 手动添加函数说明 147
5.11 使用加载项存储自定义函数 148
5.12 使用Windows API 148
5.12.1 Windows API示例 149
5.12.2 确定Windows目录 149
5.12.3 检测Shift键 150
5.12.4 了解有关API函数的更多信息 151
第6章 了解Excel事件 152
6.1 Excel可以监视的事件类型 152
6.1.1 了解事件发生的顺序 153
6.1.2 存放事件处理程序的位置 153
6.1.3 禁用事件 154
6.1.4 输入事件处理代码 155
6.1.5 使用参数的事件处理程序 156
6.2 工作簿级别的事件 157
6.2.1 Open事件 158
6.2.2 Activate事件 159
6.2.3 SheetActivate事件 159
6.2.4 NewSheet事件 159
6.2.5 BeforeSave事件 160
6.2.6 Deactivate事件 160
6.2.7 BeforePrint事件 160
6.2.8 BeforeClose事件 162
6.3 检查工作表事件 163
6.3.1 Change事件 164
6.3.2 监视特定单元格区域的修改 165
6.3.3 SelectionChange事件 169
6.3.4 BeforeDoubleClick事件 170
6.3.5 BeforeRightClick事件 170
6.4 监视应用程序事件 171
6.4.1 启用应用程序级别的事件 172
6.4.2 确定工作簿何时被打开 173
6.4.3 监视应用程序级别的事件 174
6.4.4 访问与对象无关联的事件 174
6.4.5 OnTime事件 174
6.4.6 OnKey事件 176
第7章 VBA编程示例与技巧 179
7.1 通过示例学习 179
7.2 处理单元格区域 179
7.2.1 复制单元格区域 180
7.2.2 移动单元格区域 181
7.2.3 复制大小可变的单元格区域 181
7.2.4 选中或者识别各种类型的单元格区域 182
7.2.5 调整单元格区域大小 184
7.2.6 提示输入单元格中的值 184
7.2.7 在下一个空单元格中输入一个值 186
7.2.8 暂停宏的运行以便获得用户选中的单元格区域 187
7.2.9 计算选中单元格的数目 188
7.2.10 确定选中的单元格区域的类型 189
7.2.11 有效地循环遍历选中的单元格区域 191
7.2.12 删除所有空行 193
7.2.13 任意次数地复制行 194
7.2.14 确定单元格区域是否包含在另一个单元格区域内 195
7.2.15 确定单元格的数据类型 196
7.2.16 读写单元格区域 197
7.2.17 在单元格区域中写入值的更好方法 198
7.2.18 传递一维数组中的内容 200
7.2.19 将单元格区域传递给Variant类型的数组 201
7.2.20 按数值选择单元格 201
7.2.21 复制非连续的单元格区域 203
7.3 处理工作簿和工作表 204
7.3.1 保存所有工作簿 204
7.3.2 保存和关闭所有工作簿 205
7.3.3 隐藏除选区之外的区域 205
7.3.4 创建超链接内容表 206
7.3.5 同步工作表 207
7.4 VBA技巧 208
7.4.1 切换布尔类型的属性值 208
7.4.2 显示日期和时间 209
7.4.3 显示友好时间 210
7.4.4 获得字体列表 211
7.4.5 对数组进行排序 213
7.4.6 处理一系列文件 213
7.5 用于代码中的一些有用函数 215
7.5.1 FileExists函数 215
7.5.2 FileNameOnly函数 216
7.5.3 PathExists函数 216
7.5.4 RangeNameExists函数 216
7.5.5 SheetExists函数 217
7.5.6 WorkbookIsOpen函数 217
7.5.7 检索已经关闭的工作簿中的值 218
7.6 一些有用的工作表函数 220
7.6.1 返回单元格的格式信息 220