Excel经典教程 VBA与宏

第1 章 使用VBA 释放Excel 的
力量1
1．1　Excel 的力量1
1．2　入门难点1
1．2．1　宏录制器无法工作1
1．2．2　Visual Basic 不同于BASIC　2
1．2．3　好消息：非常容易入门2
1．2．4　非常棒的消息：Excel VBA 值得
学习2
1．3　了解你的工具：“开发工具”
选项卡3
1．4　宏安全性3
1．4．1　添加受信任位置4
1．4．2　使用宏设置启用不在受信任位置
的工作簿中的宏5
1．4．3　使用设置“禁用所有宏，并发出
通知”　5
1．5　录制宏、存储宏和运行宏概述6
1．6　运行宏7
1．6．1　在功能区中创建宏按钮8
1．6．2　在快速访问工具栏上创建宏
按钮8
1．6．3　将宏关联到窗体控件、文本框或
形状9
1．7　了解VB 编辑器　11
1．7．1　VB 编辑器设置　11
1．7．2　自定义VB 编辑器选项设置　11
1．7．3　工程资源管理器　12
1．7．4　属性窗口　13
1．8　了解宏录制器的不足之处　13
案例分析：准备录制宏　13
录制宏　15
1．8．1　在编程窗口中查看代码　15
1．8．2　日后运行该宏时得到意外的
结果　18
1．8．3　一种可能的解决方案：在录制宏时
使用相对引用　18
案例分析：使用相对引用录制宏　18
1．8．4　录制宏时千万不要使用“自动
求和”按钮　22
1．8．5　使用宏录制器时的三点建议　22
第2　章 听起来像BASIC，但为什么
它们并不相似　23
2．1　我不理解这种代码　23
2．2　了解VBA 语言的组成部分　24
2．3　VBA 实际并不难　27
2．3．1　VBA 帮助文件：使用F1 键获取
任何帮助　27
2．3．2　使用帮助主题　28
2．4　查看录制的宏代码：使用VB
编辑器和帮助　29
2．4．1　可选参数　30
2．4．2　定义常量　31
2．4．3　可返回对象的属性　35
2．5　使用调试工具帮助理解录制的
代码　36
2．5．1　单步执行代码　36
2．5．2　另一个调试选项：断点　39
2．5．3　在代码中向前或向后移动　40
2．5．4　不逐步执行每行代码　40
2．5．5　在逐句执行代码时进行查询　40
2．5．6　使用监视设置断点　45
2．5．7　监视对象　45
2．6　对象浏览器：终极参考信息　46
2．7　整理所录制代码的7 点建议　48
Excel　经典教程—VBA 与宏
2　|
2．8　案例分析：综合应用——修改
录制的代码　51
第3　章 引用区域　54
3．1　Range 对象　54
3．2　指定区域的语法　54
3．3　命名区域　55
3．4　引用区域的快捷方式　55
3．5　在其他工作表中引用区域　55
3．6　引用相对于其他区域的区域　56
3．7　使用Cells 属性选择区域　57
3．8　使用Offset 属性引用区域　58
3．9　使用Resize 属性改变区域的
大小　59
3．10　使用Columns 和Rows 属性指定
区域　60
3．11　使用Union 方法合并多个
区域　61
3．12　使用Intersect 方法在重叠区域
创建新区域　61
3．13　使用ISEMPTY 函数检查单元格
是否为空　61
3．14　使用CurrentRegion 属性选择
数据区域　63
案例分析：使用SpecialCells　方法选择
特定单元格　63
3．15　使用Areas 集合返回非连续
区域　65
3．16　引用数据表　66
第4　章 用户自定义函数　67
4．1　创建用户自定义函数　67
案例分析：用户自定义函数——示例和
解析　67
4．2　共享UDF　69
4．3　有用的Excel 自定义函数　69
4．3．1　在单元格中获取当前工作簿的
名称　70
4．3．2　在单元格中获取当前工作簿的
名称和文件名　70
4．3．3　检查工作簿是否打开　70
4．3．4　检查打开的工作簿中是否存在
工作表　71
4．3．5　统计目录中的工作簿数量　72
4．3．6　获取USERID　73
4．3．7　获取*后一次保存的日期和
时间　74
4．3．8　获取固定的日期和时间　74
4．3．9　验证E-mail 地址　75
4．3．10　根据内部颜色对单元格求和　77
4．3．11　统计唯一值的数量　78
4．3．12　删除区域中的重复值　79
4．3．13　在区域中查找**个非空
单元格　81
4．3．14　替换多个字符　82
4．3．15　从混合文本中获取数值　83
4．3．16　将星期编号转换为日期　84
4．3．17　从使用分隔符分离的字符串中
提取元素　84
4．3．18　排序并连接　85
4．3．19　对数字和字符进行排序　87
4．3．20　在文本中查找字符串　89
4．3．21　颠倒单元格中内容的顺序　89
4．3．22　多个*大值　90
4．3．23　返回超链接地址　91
4．3．24　返回单元格地址的列字母　91
4．3．25　静态随机　92
4．3．26　在工作表中使用Select Case　92
第5　章 循环和流程控制　94
5．1　For．．．Next 循环　94
5．1．1　在声明语句For 中使用变量　97
5．1．2　For．．．Next 循环的变体　97
5．1．3　在特定条件满足时提前跳出
循环　98
5．1．4　循环嵌套　99
5．2　Do 循环　100
5．2．1　在Do 循环中使用While 或Until
语句　102
5．2．2　While．．．Wend 循环　104
5．3　VBA 循环：For Each　104
目录
|　3
对象变量105
案例分析：遍历文件夹中的所有文件106
5．4　流程控制：使用If．．．Then．．．Else
和Select　Case　108
5．4．1　基础的流程控制：If．．．Then．．．
Else　108
5．4．2　条件108
5．4．3　If．．．Then．．．End If　109
5．4．4　Either/Or 决策： If．．．Then．．．Else．．．
End　If109
5．4．5　使用If．．．Else If．．．End If 检测多项
条件109
5．4．6　使用Select Case．．．End Select 检测
多项条件110
5．4．7　在Case 语句中使用复杂
表达式111
5．4．8　嵌套If 语句111
第6　章 R1C1 引用样式114
6．1　引用单元格：A1 和R1C1 引用
样式的比较114
6．2　将Excel 切换到R1C1 引用
样式114
6．3　Excel 公式创造的奇迹115
6．3．1　输入一次公式并复制1 000 次116
6．3．2　秘密：其实并不神奇117
案例分析：在VBA　中使用A1 样式和
R1C1　引用样式的比较118
6．4　R1C1 引用样式简介119
6．4．1　使用R1C1 相对引用119
6．4．2　使用R1C1 绝对引用120
6．4．3　使用R1C1 混合引用120
6．4．4　使用R1C1 引用样式引用整行或
整列121
6．4．5　使用一个R1C1 引用样式替换
多个A1　公式121
6．4．6　记住与列字母相关的列号123
6．5　在数组公式中需要使用R1C1
引用样式124
第7　章 在VBA 中创建和操作
名称125
7．1　Excel 名称125
7．2　全局名称和局部名称　125
7．3　添加名称　126
7．4　删除名称　127
7．5　添加备注　127
7．6　名称类型　128
7．6．1　公式　128
7．6．2　字符串　128
7．6．3　数字　130
7．6．4　数据表　130
7．6．5　在名称中使用数组　131
7．6．6　保留名称　131
7．7　隐藏名称　132
7．8　检验名称是否存在　133
案例分析：将命名区域用作函数
VLOOKUP　的参数　133
第8　章 事件编程　136
8．1　事件级别　136
8．2　使用事件　136
8．2．1　事件参数　137
8．2．2　启用事件　137
8．3　工作簿事件　138
处于工作簿等级的工作表和图表事件　143
8．4　工作表事件　144
案例分析：在单元格中快速输入24　小
时制时间　147
8．5　图表事件　148
嵌入图表　148
8．6　应用程序级事件　151
第9　章 用户窗体简介　157
9．1　用户交互方式　157
9．1．1　输入框　157
9．1．2　消息框　157
9．2　创建用户窗体　158
9．3　调用和隐藏用户窗体　159
9．4　用户窗体编程　160
9．5　控件编程　161
案例分析：向现有窗体中添加控件时进行
错误修正　162
Excel　经典教程—VBA 与宏
4　|
9．6　使用基本的窗体控件163
9．6．1　使用标签、文本框和命令按钮163
9．6．2　选择在窗体中使用列表框还是
文本框164
9．6．3　在用户窗体中添加单选钮167
9．6．4　在用户窗体中添加图片169
9．6．5　在用户窗体中使用微调按钮169
9．6．6　使用多页控件组合窗体171
9．7　验证用户输入173
9．8　非法关闭窗口173
9．9　获取文件名174
第10　章 创建图表176
10．1　Excel 中的图表176
10．2　在VBA 代码中引用图表和
图表对象176
10．3　创建图表177
10．3．1　指定图表的大小和位置177
10．3．2　日后引用特定图表178
10．4　录制“布局”或“设计”
选项卡中的命令180
10．4．1　指定一个内置图表类型181
10．4．2　指定模板图表类型183
10．4．3　修改图表的布局或样式184
10．5　使用SetElement 模仿在“布
局”选项卡中所做的修改186
10．6　使用VBA 修改图表标题191
10．7　模拟在“格式”选项卡中所做的
修改191
使用Format　方法访问格式选项191
10．8　创建高级图表205
10．8．1　创建真正的“开盘-盘高-盘低-
收盘”股价图205
10．8．2　为频数图创建区间207
10．8．3　创建堆积面积图210
10．9　将图表导出为图形215
在用户窗体中创建动态图表215
10．10　创建数据透视图217
第11　章 使用高级筛选进行数据
挖掘　220
11．1　使用自动筛选代替循环　220
11．1．1　使用新增的自动筛选技术　222
11．1．2　只筛选可见单元格　225
11．2　案例分析：使用定位条件代替
循环　226
11．3　在VBA 中使用高级筛选比在
Excel　用户界面中更容易　227
通过Excel　用户界面创建一个高级
筛选　227
11．4　使用高级筛选提取非重复值
列表　228
11．4．1　通过用户界面提取非重复值
列表　229
11．4．2　使用VBA 代码提取非重复值
列表　229
11．4．3　获取多个字段的不重复组合　233
11．5　使用包含条件区域的高级
筛选　234
11．5．1　使用逻辑or 合并多个条件　236
11．5．2　使用逻辑and 合并两个条件　236
11．5．3　其他稍微复杂的条件区域　237
11．5．4　*复杂的条件：使用公式结果
作为条件代替值列表　237
11．6　案例分析：使用非常复杂的
条件　237
11．7　案例分析：在Excel 用户界面中
使用基于公式的条件　239
11．8　在原有区域显示高级筛选
结果　244
11．8．1　在原有区域使用筛选却没有
筛选出任何记录　245
11．8．2　在原有区域筛选之后显示所有
记录　245
11．9　*常用的功能：使用xlFilterCopy
复制所有记录而不只是非重复
记录　246
11．9．1　复制所有列　246
11．9．2　复制部分列并重新排序　247
目录
|　5
11．10　案例分析：使用两种高级筛选为
每个顾客创建报表249
11．11　在原区域筛选非重复记录252
Excel　实践：在自动筛选时关闭部分
下拉列表254
第12　章 使用VBA 创建数据
透视表255
12．1　数据透视表简介255
12．2　版本介绍255
12．2．1　自Excel 2010 新增的功能256
12．2．2　自Excel 2007 新增的功能256
12．3　在Excel 用户界面中创建数据
透视表258
压缩布局简介261
12．4　在Excel VBA 中创建数据
透视表262
12．4．1　定义数据透视表缓存262
12．4．2　创建并配置数据透视表263
12．4．3　向数据区域添加字段264
12．4．4　无法移动或修改部分数据透视表
的原因267
12．4．5　确定数据透视表的*终大小以便
将其转化为值267
12．5　使用高级数据透视表功能270
12．5．1　使用多个值字段270
12．5．2　统计记录的数量271
12．5．3　将日期按月份、季度或年进行
分组271
12．5．4　修改计算方法显示百分比274
12．5．5　删除值区域中的空单元格276
12．5．6　使用“自动排序”控制排列
顺序276
12．5．7　为每种产品复制报表277
12．6　筛选数据集280
12．6．1　手工筛选数据透视表字段中的
多个记录280
12．6．2　使用概念筛选281
12．6．3　使用搜索筛选器285
案例分析：使用筛选器筛选出前5　或
前10　名记录285
12．6．4　创建切片器来筛选数据
透视表　288
12．6．5　使用命名集筛选OLAP 数据
透视表　290
12．7　使用其他数据透视表功能　292
12．7．1　计算数据字段　292
12．7．2　计算项　293
12．7．3　使用ShowDetail 筛选数据集　293
12．7．4　通过“设计”选项卡修改
布局　293
12．7．5　禁用多行字段的分类汇总　294
案例分析：应用数据可视化　295
第13　章 Excel 的力量　296
13．1　文件操作　296
13．1．1　列出文件夹中的文件　296
13．1．2　导入CSV　299
13．1．3　将整个TXT 文件读入内存并进行
分析　300
13．2　合并、拆分工作簿　301
13．2．1　将工作表合并成工作簿　301
13．2．2　合并工作簿　302
13．2．3　筛选数据并将结果复制到新
工作表中　303
13．2．4　将数据导出为Word 文件　304
13．3　处理单元格批注　305
13．3．1　列表批注　305
13．3．2　调整批注框的大小　306
13．3．3　使用居中调整批注框的大小　308
13．3．4　将图表加入批注框　309
13．4　让客户叫绝的程序　310
13．4．1　使用条件格式突出显示
单元格　310
13．4．2　在不使用条件格式的情况下突出
显示单元格　312
13．4．3　自定义转置数据　313
13．4．4　选中/取消选中非连续单元格　315
13．5　VBA 专业技术　318
13．5．1　数据透视表深化　318
13．5．2　加速页面设置　319
13．5．3　计算代码的执行时间　322
Excel　经典教程—VBA 与宏
6　|
13．5．4　自定义排列顺序323
13．5．5　单元格进度指示器324
13．5．6　密码保护框325
13．5．7　更改大小写327
13．5．8　使用SpecialCells 进行选择329
13．5．9　ActiveX 右键菜单330
13．6　一个出色的应用程序331
第14　章 数据可视化与条件格式334
14．1　数据可视化简介334
14．2　VBA 中的数据可视化方法和
属性335
14．3　向区域中添加数据条336
14．4　在区域中添加色阶340
14．5　在区域中添加图标集342
14．5．1　指定图标集342
14．5．2　为每个图标指定范围344
14．6　使用可视化技巧345
14．6．1　为部分区域创建图标集345
14．6．2　在同一区域中应用两种颜色的
数据条347
14．7　使用其他条件格式方法350
14．7．1　设置高于或低于平均值单元格的
格式350
14．7．2　设置值为前5 名或后10 名单元格
的格式350
14．7．3　设置非重复或重复单元格的
格式351
14．7．4　根据单元格的值设置其格式353
14．7．5　设置包含文本的单元格格式353
14．7．6　设置包含日期的单元格格式354
14．7．7　设置包含空格或错误的单元格
格式354
14．7．8　使用公式确定要设置格式的
单元格354
14．7．9　突出显示*大销量所在的行356
14．7．10　使用新增的NumberFormat
属性356
第15　章 在Excel 中使用迷你图绘制
仪表板358
15．1　创建迷你图358
15．2　设置迷你图的范围　360
15．3　设置迷你图格式　365
15．3．1　应用主题颜色　365
15．3．2　应用RGB 颜色　368
15．3．3　设置迷你图元素的格式　370
15．3．4　设置盈/亏图表的格式　373
15．4　创建仪表板　374
15．4．1　观察迷你图得到的结果　375
15．4．2　在仪表板中创建130 多个独立的
迷你图　375
第16　章 自动控制Word　380
16．1　前期绑定　380
编译错误：无法找到对象或库　382
16．2　后期绑定　383
16．3　创建和引用对象　383
16．3．1　关键字New　384
16．3．2　CreateObject 函数　384
16．3．3　GetObject 函数　384
16．4　使用常量　386
16．4．1　使用监视窗口检索常量的
真实值　386
16．4．2　使用对象浏览器检索常量的
真实值　386
16．5　Word 对象简介　387
16．5．1　Document 对象　388
16．5．2　Selection 对象　389
16．5．3　Range 对象　390
16．5．4　书签　394
16．6　控制Word 窗体控件　397
第17　章 数组　400
17．1　声明数组　400
多维数组　400
17．2　填充数组　401
17．3　清空数组　403
17．4　使用数组提高代码的执行
速度　404
17．5　动态数组　406
17．6　传递数组　407
目录
|　7
第18　章 处理文本文件408
18．1　导入文本文件408
18．1．1　导入不超过1 048 576 行的文本
文件408
18．1．2　读取多于1 048 576 行的文件414
18．2　写入文本文件418
第19　章 将Access 用作后端以改善
多用户数据访问420
19．1　ADO 与DAO421
案例分析：创建共享的Access　数据库421
19．2　ADO 工具423
19．3　向数据库中添加记录424
19．4　在数据库中检索记录425
19．5　更新记录427
19．6　使用ADO 删除记录430
19．7　通过ADO 汇总记录430
19．8　ADO 的其他实用程序431
19．8．1　检查表是否存在431
19．8．2　检验字段是否存在432
19．8．3　动态添加表433
19．8．4　动态添加字段434
19．9　SQL Server 示例434
第20　章 创建类、记录和集合437
20．1　插入类模块437
20．2　捕获应用程序事件和插入图表
事件437
嵌入图表事件439
20．3　创建自定义对象440
20．4　使用自定义对象441
20．5　使用Property Let 和Property Get
控制用户使用自定义对象的
方式442
20．6　集合444
20．6．1　在标准模块中创建集合444
20．6．2　在类模块中创建集合445
案例分析：帮助按钮447
20．7　用户自定义类型　449
第21　章 高级用户窗体技术　453
21．1　使用“用户窗体”工具栏设计
用户窗体控件　453
21．2　其他用户窗体控件　453
21．2．1　复选框　453
21．2．2　Tab Strips　455
21．2．3　RefEdit　457
21．2．4　切换按钮　458
21．2．5　将滚动条用作滑块来选择值　459
21．3　控件和集合　461
21．4　非模态用户窗体　462
21．5　在用户窗体中使用超链接　463
21．6　在运行阶段添加控件　464
21．6．1　动态地调整用户窗体大小　466
21．6．2　动态地添加控件　466
21．6．3　动态地调整大小　466
21．6．4　添加其他控件　467
21．6．5　动态地添加图像　467
21．7　完整代码　468
21．7．1　向用户窗体中添加帮助　470
21．7．2　显示快捷键　470
21．7．3　添加控件提示文本　471
21．7．4　指定Tab 顺序　471
21．7．5　为活动控件着色　472
案例分析：多列列表框　473
21．8　透明窗体　474
第22　章 Windows 应用程序编程
接口　476
22．1　什么是Windows API　476
22．2　理解API 声明　476
22．3　使用API 声明　477
22．4　API 示例　478
22．4．1　检索计算机名　478
22．4．2　确定Excel 文件是否已在网络上
打开　479
22．4．3　获取显示器分辨率信息　480
22．4．4　自定义“关于”对话框　481
Excel　经典教程—VBA 与宏
8　|
22．4．5　禁用用于关闭用户窗体的“X”
按钮482
22．4．6　连续时钟483
22．4．7　播放声音483
22．4．8　检索文件路径484
22．5　查找更多API 声明487
第23　章 错误处理488
23．1　错误所导致的后果488
令人费解的用户窗体代码错误调试489
23．2　使用On Error GoTo 进行基本错误
处理491
23．3　通用的错误处理程序492
23．3．1　忽略错误493
案例分析：页面设置问题通常可以
忽略493
23．3．2　禁止显示Excel 警告494
23．3．3　利用错误495
23．4　培训用户495
23．5　开发阶段错误和运行阶段
错误496
23．5．1　运行错误9：下标越界496
23．5．2　运行错误1004：Global 对象的
Range　方法失败497
23．6　保护代码的缺点498
案例分析：破解密码498
23．7　密码保护的其他问题499
23．8　不同版本导致的错误499
第24　章 创建自定义选项卡以方便
运行宏501
24．1　辞旧迎新501
24．2　将代码加入到文件夹
Customui　中502
24．3　创建选项卡和组503
24．4　在组中添加控件504
24．5　Excel 文件的结构509
24．6　理解RELS 文件510
24．7　重命名Excel 文件并将其
打开511
自定义用户界面编辑器工具　511
24．8　为按钮指定图像　511
Microsoft　Office 图标　512
案例分析：将Excel　2003 自定义工具栏
转换为Excel　2010 自定义
选项卡　513
24．9　排除错误　515
24．9．1　在DTD/架构中没有找到指定
属性　515
24．9．2　非法的名称字符　516
24．9．3　元素之间的父子关系不正确　517
24．9．4　Excel 发现不可读取的内容　517
24．9．5　参数数量不正确或属性值无效　518
24．9．6　自定义选项卡没出现　518
24．10　其他运行宏的方式　518
24．10．1　快捷键　519
24．10．2　将宏关联到命令按钮　520
24．10．3　将宏关联到形状　520
24．10．4　将宏同ActiveX 控件关联
起来　521
24．10．5　通过超链接运行宏　522
第25　章 创建加载项　524
25．1　标准加载项的特点　524
25．2　将Excel 工作簿转换为
加载项　525
25．2．1　使用“另存为”将文件转换为
加载项　525
25．2．2　使用VB 编辑器将文件转换为
加载项　526
25．3　让用户安装加载项　527
25．3．1　标准加载项并不安全　529
25．3．2　关闭加载项　529
25．3．3　删除加载项　530
25．3．4　使用隐藏工作簿代替加载项　530
案例分析：使用隐藏工作簿存储所有宏和
窗体　530
结束语　532