正版新书]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

案例分析：使用SpeCells方法选择特定单元格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使用SpeCells进行选择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

1. 源自美国经典办公图书Mr.Excel系列，知名的Excel非常不错专家写作
2. 不过时的VBA宝典，用以理解VBA
3. 注重编程逻辑与思路的讲解，注重代码的执行效率
4. 提供书中大部分源代码下载，稍加修改即可应用