Excel经典教程——VBA与宏_【美】Bill Jelen_9787115526007

本书为愿意提升Excel使用效率的用户而编写：基于学习路径，全面、系统、细致地讲解了编写Excel VBA所需的各方面知识。全书共分25章，分别介绍了宏录制的优缺点、VBA的基本语法与代码调试、单元格及单元格区域的引用、用户自定义函数的创建、循环和流程控制、R1C1引用样式、事件编程的方法、用户窗体的使用、图表的创建、数据的高级筛选、数据透视表的创建、数据可视化和条件格式、文本文件的处理等内容

第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

你还可能感兴趣

我要评论