Excel经典教程 VBA与宏 (美)比尔·耶伦,(美)特雷茜·塞尔斯塔德 著 贾旭,冯伟 译 专业科技 文轩网

第1章使用VBA释放Excel的力量1
1．1Excel的力量1
1．2入门难点1
1．2．1宏录制器无法工作1
1．2．2VisualBasic不同于BASIC2
1．2．3好消息：非常容易入门2
1．2．4非常棒的消息：ExcelVBA值得学习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．1VB编辑器设置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．3VBA实际并不难27
2．3．1VBA帮助文件：使用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
2．8案例分析：综合应用——修改录制的代码51
第3章引用区域54
3．1Range对象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共享UDF69
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获取USERID73
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在工作表中使用SelectCase92
第5章循环和流程控制94
5．1For．．．Next循环94
5．1．1在声明语句For中使用变量97
5．1．2For．．．Next循环的变体97
5．1．3在特定条件满足时提前跳出循环98
5．1．4循环嵌套99
5．2Do循环100
5．2．1在Do循环中使用While或Until语句102
5．2．2While．．．Wend循环104
5．3VBA循环：ForEach104
对象变量105
案例分析：遍历文件夹中的所有文件106
5．4流程控制：使用If．．．Then．．．Else和SelectCase108
5．4．1基础的流程控制：If．．．Then．．．Else108
5．4．2条件108
5．4．3If．．．Then．．．EndIf109
5．4．4Either/Or决策：If．．．Then．．．Else．．．EndIf109
5．4．5使用If．．．ElseIf．．．EndIf检测多项条件109
5．4．6使用SelectCase．．．EndSelect检测多项条件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．3Excel公式创造的奇迹115
6．3．1输入一次公式并复制1000次116
6．3．2秘密：其实并不神奇117
案例分析：在VBA中使用A1样式和R1C1引用样式的比较118
6．4R1C1引用样式简介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．1Excel名称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
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．1Excel中的图表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
11．10案例分析：使用两种高级筛选为每个顾客创建报表249
11．11在原区域筛选非重复记录252
Excel实践：在自动筛选时关闭部分下拉列表254
第12章使用VBA创建数据透视表255
12．1数据透视表简介255
12．2版本介绍255
12．2．1自Excel2010新增的功能256
12．2．2自Excel2007新增的功能256
12．3在Excel用户界面中创建数据透视表258
压缩布局简介261
12．4在ExcelVBA中创建数据透视表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导入CSV299
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．5VBA专业技术318
13．5．1数据透视表深化318
13．5．2加速页面设置319
13．5．3计算代码的执行时间322
13．5．4自定义排列顺序323
13．5．5单元格进度指示器324
13．5．6密码保护框325
13．5．7更改大小写327
13．5．8使用SpecialCells进行选择329
13．5．9ActiveX右键菜单330
13．6一个出色的应用程序331
第14章数据可视化与条件格式334
14．1数据可视化简介334
14．2VBA中的数据可视化方法和属性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章自动控制Word380
16．1前期绑定380
编译错误：无法找到对象或库382
16．2后期绑定383
16．3创建和引用对象383
16．3．1关键字New384
16．3．2CreateObject函数384
16．3．3GetObject函数384
16．4使用常量386
16．4．1使用监视窗口检索常量的真实值386
16．4．2使用对象浏览器检索常量的真实值386
16．5Word对象简介387
16．5．1Document对象388
16．5．2Selection对象389
16．5．3Range对象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
第18章处理文本文件408
18．1导入文本文件408
18．1．1导入不超过1048576行的文本文件408
18．1．2读取多于1048576行的文件414
18．2写入文本文件418
第19章将Access用作后端以改善多用户数据访问420
19．1ADO与DAO421
案例分析：创建共享的Access数据库421
19．2ADO工具423
19．3向数据库中添加记录424
19．4在数据库中检索记录425
19．5更新记录427
19．6使用ADO删除记录430
19．7通过ADO汇总记录430
19．8ADO的其他实用程序431
19．8．1检查表是否存在431
19．8．2检验字段是否存在432
19．8．3动态添加表433
19．8．4动态添加字段434
19．9SQLServer示例434
第20章创建类、记录和集合437
20．1插入类模块437
20．2捕获应用程序事件和插入图表事件437
嵌入图表事件439
20．3创建自定义对象440
20．4使用自定义对象441
20．5使用PropertyLet和PropertyGet
控制用户使用自定义对象的方式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．2TabStrips455
21．2．3RefEdit457
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什么是WindowsAPI476
22．2理解API声明476
22．3使用API声明477
22．4API示例478
22．4．1检索计算机名478
22．4．2确定Excel文件是否已在网络上打开479
22．4．3获取显示器分辨率信息480
22．4．4自定义“关于”对话框481
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使用OnErrorGoTo进行基本错误处理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．5Excel文件的结构509
24．6理解RELS文件510
24．7重命名Excel文件并将其打开511
自定义用户界面编辑器工具511
24．8为按钮指定图像511
MicrosoftOffice图标512
案例分析：将Excel2003自定义工具栏转换为Excel2010自定义选项卡513
24．9排除错误515
24．9．1在DTD/架构中没有找到指定属性515
24．9．2非法的名称字符516
24．9．3元素之间的父子关系不正确517
24．9．4Excel发现不可读取的内容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