第1篇Excel VBA基础篇
第1章Excel VBA基础知识
1.1认识与了解VBA......................................................................2
1.1.1VBA与VB的区别 ............................................................. 2
1.2.2VBA的主要功能和作用.................................................... 2
1.3.3ExcelVBA的主要框架..................................................... 3
1.2 宏的概念及应用......................................................................3
1. 2.1宏的概念 ......................................................................... 3
1.2.2宏的应用 ......................................................................... 4
技巧1使用“录制宏”功能获取有效的代码............................. 4
技巧2执行宏的方式(1)........................................................ 7
技巧3执行宏的方式(2)........................................................ 8
技巧4执行宏的方式(3)........................................................ 9
1.3 代码的编写...........................................................................11
技巧5快速输入代码(1)...................................................... 11
技巧6快速输入代码(2)...................................................... 12
技巧7快速输入代码(3)...................................................... 13
技巧8编写高效VBA代码的原则 ............................................. 13
技巧9有效地获得VBA帮助 .................................................... 14
第2篇Excel VBA对象模型篇
第2章Application(应用程序)对象
2.1 认识Application(应用程序)对象 .......................................18
2.2 Excel应用程序基本信息的查看 ............................................18
技巧1查看Excel的当前用户名 ............................................... 18
技巧2查看当前操作系统的名称及版本 .................................. 19
技巧3查看Excel的安装路径................................................... 19
技巧4查看Excel的启动路径................................................... 20
技巧5查看打开Excel文件时的默认路径................................. 20
技巧6查看Excel模板保存的默认路径 .................................... 21
技巧7查看Excel窗口的标题文本 ........................................... 21
技巧8查看Excel窗口的显示状态 ........................................... 22
技巧9查看Excel窗口的显示位置 ........................................... 22
技巧10查看Excel窗口的大小................................................. 23
2.3 Excel应用程序的基本操作与设置.........................................23
技巧11改变Excel窗口的大小................................................. 24
技巧12使Excel窗口最大化 .................................................... 24
技巧13全屏显示Excel窗口 .................................................... 26
技巧14设置Excel窗口的显示位置 ......................................... 27
技巧15隐藏Excel窗口 ........................................................... 28
技巧16更改Excel的标题文本................................................. 28
技巧17删除Excel的标题文本................................................. 29
技巧18自定义Excel状态栏中的信息...................................... 30
技巧19显示或隐藏Excel默认的菜单栏和工具栏.................... 31
技巧20设置Excel编辑栏的高度 ............................................. 32
技巧21批量更改工作表的名称............................................... 33
技巧22设置新工作簿中的工作表个数.................................... 33
技巧23设置Excel文件的默认保存位置 .................................. 34
技巧24设置保存自动恢复信息时间的间隔和文件位置 .......... 35
技巧25设置关闭文件时不显示警告信息对话框 ..................... 36
技巧26在Excel中安装加载宏................................................. 36
第3章Workbook(工作簿)对象
3.1 认识Workbook(工作簿)对象 ............................................38
3.2 工作簿常用的基本操作.........................................................38
技巧1新建工作簿并自定义名称和路径 .................................. 38
技巧2打开指定名称的工作簿................................................. 39
技巧3打开指定路径下的工作簿............................................. 39
技巧4打开工作簿时不显示更新链接对话框........................... 40
技巧5保护或取消保护工作簿................................................. 40
技巧6保存工作簿的更改........................................................ 41
技巧7直接另存工作簿至指定路径中...................................... 42
技巧8打开“另存为”对话框................................................. 43
技巧9保存工作簿副本 ........................................................... 43
技巧10关闭当前工作簿 ......................................................... 44
技巧11关闭所有打开的工作簿............................................... 45
技巧12控制工作簿只能通过代码关闭.................................... 45
技巧13重新排列工作簿窗口 .................................................. 46
技巧14顺序打印工作簿的奇数页........................................... 47
技巧15逆序打印工作簿的偶数页........................................... 48
3.3 工作簿基本信息的查看与设置 ..............................................48
技巧16查看当前活动工作簿的名称 ....................................... 48
技巧17查看当前运行宏代码的工作簿名称............................. 49
技巧18查看当前活动工作簿的路径 ....................................... 49
技巧19查看当前运行宏代码的工作簿路径............................. 50
技巧20查看所有打开的工作簿名称及其路径......................... 51
技巧21判断指定工作簿是否打开........................................... 52
技巧22判断指定工作簿是否保存及最近的保存时间 .............. 52
技巧23判断当前工作簿是否为加载宏工作簿......................... 54
技巧24查看工作簿的保护状态............................................... 55
技巧25设置工作簿为加载宏工作簿或取消加载宏.................. 55
技巧26工作簿窗口大小的改变............................................... 56
技巧27工作簿窗口显示位置的改变 ....................................... 57
技巧28工作簿窗口显示比例的设置 ....................................... 58
技巧29工作簿窗口的隐藏与显示 ........................................... 59
3.4 工作簿的引用 .......................................................................60
技巧30使用索引号指定工作簿............................................... 60
技巧31使用名称指定工作簿 .................................................. 60
技巧32引用当前运行宏代码的工作簿.................................... 61
技巧33引用当前活动工作簿 .................................................. 62
技巧34引用最后打开的工作簿............................................... 62
技巧35引用新建的工作簿...................................................... 63
第4章Worksheet(工作表)对象
4.1 认识Worksheet(工作表)对象 ...........................................66
4.2 工作表常用的基本操作.........................................................66
技巧1轻松添加一个工作表 .................................................... 66
技巧2一次性添加多个工作表................................................. 67
技巧3在当前工作簿中移动工作表 ......................................... 68
技巧4将工作表移至新建的工作簿中...................................... 69
技巧32设置工作表的显示状态............................................... 94
技巧33设置工作表标签的颜色............................................... 94
第5章Range(单元格)对象
5.1 认识Range(单元格)对象..................................................98
5.2 单元格常用的基本操作.........................................................98
技巧1快速选定单元格和单元格区域...................................... 98
技巧2在指定位置显示选定的单元格...................................... 99
技巧3选定非活动工作表中的单元格.................................... 100
技巧4复制单元格的值 ......................................................... 101
技巧5复制单元格的格式...................................................... 102
技巧6复制单元格的值和公式............................................... 102
技巧7复制单元格的值和数字格式 ....................................... 103
技巧8复制单元格的公式和数字格式.................................... 104
技巧9复制单元格的列宽...................................................... 105
技巧10复制单元格的批注.................................................... 105
技巧11快速移动单元格 ....................................................... 106
技巧12合并或取消合并单元格............................................. 106
技巧13合并单元格时保留每个单元格的文本....................... 108
技巧14取消合并单元格时保留每个单元格的文本................ 108
技巧15合并内容相同的连续单元格 ..................................... 109
技巧16插入单元格............................................................... 110
技巧17插入整行或整列 ....................................................... 111
技巧18一次性插入多行 ....................................................... 112
技巧19按颜色重排单元格.................................................... 113
技巧20按自定义序列重排单元格......................................... 114
技巧21删除空单元格后重排数据......................................... 115
技巧22删除单元格的公式和值............................................. 116
技巧23 删除单元格的格式....................................................117
技巧24 删除单元格的批注....................................................118
技巧25 删除单元格中的超链接.............................................119
技巧26 删除单元格的全部信息.............................................119
5.3 单元格的引用.....................................................................120
技巧27 引用活动单元格.......................................................121
技巧28 使用Range属性引用单元格......................................121
技巧29 使用Range属性引用单元格区域..............................122
技巧30 通过字符串引用单元格和单元格区域.......................123
技巧31 引用单列或多列.......................................................124
技巧32 引用单行或多行.......................................................125
技巧33 使用Cells属性引用单元格........................................126
技巧34 使用Cells属性引用单元格区域.................................126
技巧35 使用Offset属性引用单元格或单元格区域.................127
技巧36 引用单元格的快捷方式.............................................128
技巧37 通过已定义的名称引用单元格区域...........................129
技巧38 引用含有公式的单元格.............................................130
技巧39 引用含有批注的单元格.............................................130
技巧40 引用空白单元格.......................................................131
技巧41 引用设置了数据有效性的单元格..............................132
技巧42 返回公式中引用的所有单元格..................................133
5.4 单元格基本信息的查看.......................................................133
技巧43 查看单元格区域内的单元格数目..............................133
技巧44 查看单元格区域的行数和列数..................................134
技巧45 查看单元格的行号和列号.........................................135
技巧46 查看单元格区域的行号和列号起止范围...................136
技巧47 查看单元格的显示值和显示字符串...........................137
技巧48 查看单元格内输入的公式.........................................138
技巧49 判断单元格内是否输入了公式..................................139
技巧50判断单元格内的公式是否引用了其他工作表或工作簿的数据................. 140
技巧51判断单元格内的公式是否存在错误........................... 142
技巧52判断单元格是否包含在合并单元格区域中................ 142
技巧53查看单元格的数字格式............................................. 143
技巧54判断单元格是否含有超链接 ..................................... 144
技巧55判断单元格是否含有批注......................................... 145
5.5 单元格基本信息的设置.......................................................146
技巧56设置单元格的字体格式............................................. 146
技巧57设置单元格的下划线 ................................................ 147
技巧58设置单元格的填充颜色和背景图案........................... 148
技巧59为单元格添加边框.................................................... 149
技巧60设置单元格的对齐方式............................................. 150
技巧61当单元格内容过长时自动缩小字体........................... 151
技巧62设置单元格的大小.................................................... 152
技巧63为单元格插入超链接 ................................................ 153
技巧64为单元格添加、修改批注......................................... 154
技巧65为单元格区域定义名称............................................. 156
技巧66为单元格设置数据有效性序列.................................. 156
技巧67隐藏、显示单元格和单元格区域 .............................. 157
技巧68隐藏、显示单元格的公式......................................... 159
第6章Chart(图表)对象
6.1 认识Chart(图表)对象 .....................................................162
6.2 图表常用的基本操作 ..........................................................162
技巧1自动生成单个图表...................................................... 162
技巧2自动生成多个图表...................................................... 163
技巧3重排多个图表 ............................................................. 165
技巧4创建可以随源数据变化而变化的图表......................... 166
技巧5将图表保存为图像文件............................................... 167
技巧6将图表图像文件载入到工作表中 ................................ 168
技巧7删除图表 .................................................................... 169
6.3 图表基本信息的查看 ..........................................................170
技巧8查看图表的名称 ......................................................... 170
技巧9查看图表的大小 ......................................................... 170
技巧10查看图表的位置 ....................................................... 171
技巧11查看图表的标题信息 ................................................ 172
技巧12查看图表的图例信息 ................................................ 173
技巧13查看图表的坐标轴信息............................................. 174
6.4 图表基本信息的设置 ..........................................................175
技巧14更改图表的类型 ....................................................... 175
技巧15更改图表的大小 ....................................................... 176
技巧16更改图表的名称 ....................................................... 178
技巧17更改图表的源数据.................................................... 179
技巧18设置图表区的格式.................................................... 181
技巧19设置绘图区的格式.................................................... 182
技巧20设置图表的标题格式 ................................................ 183
技巧21设置图表的图例格式 ................................................ 184
技巧22设置图表的坐标轴格式............................................. 185
第7章Shape(图形)对象
7.1 认识Shape(图形)对象....................................................188
7.2 图形常用的基本操作 ..........................................................188
技巧1快速插入图形对象...................................................... 188
技巧2插入指定名称的图形对象 ........................................... 189
技巧3插入指定宏的图形对象............................................... 190
技巧4为插入的图形对象指定不同的宏 ................................ 191
技巧5选定相同类型的图形对象........................................... 192
技巧6制作Flash动画图形 .................................................... 193
技巧7一次性复制多个图形对象........................................... 195
技巧8移动图形对象............................................................. 196
技巧9将图形对象保存为图片............................................... 197
技巧10删除图形对象 ........................................................... 198
7.3 图形基本信息的查看 ..........................................................199
技巧11查看图形对象的名称 ................................................ 199
技巧12查看图形对象的类型 ................................................ 200
技巧13查看图形对象的大小 ................................................ 202
技巧14查看图形对象的位置 ................................................ 202
技巧15查看图形对象中的文字............................................. 202
技巧16查看图形对象的宏名称............................................. 203
7.4 图形基本信息的设置 ..........................................................203
技巧17设置图形对象中的文字............................................. 204
技巧18设置图形对象的大小 ................................................ 205
技巧19更改图形对象的位置 ................................................ 206
技巧20为图形对象插入超链接............................................. 207
第3篇数据的实用操作篇
第8章 数据的查询、排序及筛选
8.1 数据的查询操作..................................................................210
技巧1从活动工作表中查询数据........................................... 210
技巧2从多个工作表中查询数据........................................... 211
技巧3查询数据及其公式...................................................... 212
技巧4查询数据所在的行...................................................... 213
技巧5通过指定多个条件查询数据(1) .............................. 214
技巧6通过指定多个条件查询数据(2) .............................. 215
技巧7通过指定多个条件查询数据(3) .............................. 215
技巧8通过指定多个条件查询数据(4) .............................. 216
技巧9通过指定多个条件查询数据(5) .............................. 217
技巧10查询包含指定字符的单元格数目 .............................. 218
技巧11查询包含指定字符所在的行 ..................................... 218
8.2 数据的排序操作..................................................................219
技巧12对指定区域数据进行自动排序.................................. 220
技巧13对数据进行排序后再重新恢复.................................. 221
技巧14使用多个关键字进行排序......................................... 222
技巧15按自定义序列进行排序............................................. 223
技巧16按字符的长度进行排序............................................. 224
技巧17按单元格的颜色进行排序......................................... 225
8.3 数据的筛选操作..................................................................226
技巧18执行、撤销自动筛选 ................................................ 226
技巧19执行与、或条件下的高级筛选.................................. 227
技巧20撤销高级筛选 ........................................................... 229
技巧21在新工作表中显示筛选结果 ..................................... 229
技巧22筛选不重复的行数据 ................................................ 230
技巧23筛选两个工作表中相同或不同的行数据 ................... 231
技巧24筛选并删除数据区域中的空行或空列....................... 233
第9章数据的条件格式操作
技巧1突出显示数据区域中的前N项或最后N项.................... 236
技巧2突出显示小于或大于平均值的数据............................. 237
技巧3突出显示重复或不重复的数据.................................... 238
技巧4突出显示昨天、今天、明天的数据............................. 239
技巧5突出显示上周、本周、下周的数据............................. 240
技巧6突出显示上月、本月、下月的数据............................. 242
技巧7利用数据条突出显示数据........................................... 244
技巧8利用图标集突出显示数据........................................... 245
技巧9利用色阶突出显示数据............................................... 247
第4篇函数与公式等操作应用篇
第10章函数与公式的应用
10.1 工作表函数和VBA函数的使用 ..........................................250
技巧1数学函数的使用....................................................... 250
技巧2字符串函数的使用 ................................................... 251
技巧3日期与时间函数的使用............................................ 252
技巧4财务函数的使用....................................................... 253
技巧5数组处理函数的使用................................................ 254
技巧6判断函数的使用....................................................... 254
技巧7格式化函数的使用 ................................................... 255
技巧8类型转换函数的使用................................................ 255
10.2 自定义函数.......................................................................256
技巧9自定义不确定参数个数的函数 ................................. 256
技巧10根据指定的条件自定义函数................................... 257
技巧11自定义返回数组的函数.......................................... 259
技巧12为自定义函数定制信息说明................................... 259
技巧13为自定义函数分类 ................................................. 260
10.3 公式的应用.......................................................................261
技巧14输入并自动填充一般公式 ...................................... 261
技巧15输入并自动填充数组公式 ...................................... 262
技巧16查看工作簿中所有的公式 ...................................... 263
技巧17删除公式以外的所有数据 ...................................... 264
10.4 数组的应用.......................................................................265
技巧18创建行标题和列标题.............................................. 265
技巧19查看指定单元格区域的数据................................... 266
技巧20查看指定数据在数组中出现的次数........................ 266
第11章加载宏的应用
11.1 Excel加载宏的加载和卸载................................................270
技巧1通过加载宏隐藏所有工作表..................................... 270
技巧2手动加载Excel加载宏.............................................. 272
技巧3自动加载Excel加载宏.............................................. 273
技巧4Excel加载宏的载入及应用....................................... 274
技巧5卸载加载宏.............................................................. 275
11.2 定制Excel加载宏的信息 ...................................................275
技巧6为加载宏添加信息说明............................................ 275
技巧7修改加载宏的信息说明............................................ 277
技巧8设置打开Excel文件时的启动页面............................ 278
技巧9创建加载宏的菜单和工具栏..................................... 280
11.3 加载宏工作表的应用 ........................................................282
技巧10保存大量数据 ........................................................ 282
技巧11保存工作表格式..................................................... 283
技巧12保存单元格格式..................................................... 284
第5篇交互功能应用篇
第12章菜单和工具栏的操作应用
12.1 自定义菜单.......................................................................286
技巧1添加、删除一级菜单命令 ........................................ 286
技巧2添加、删除多级菜单命令 ........................................ 287
技巧3添加、删除图表菜单命令 ........................................ 288
技巧4屏蔽、恢复Excel菜单和菜单命令............................ 289
12.2 自定义快捷菜单................................................................290
技巧5添加、删除自定义右键快捷菜单命令 ...................... 290
技巧6添加、删除内置右键快捷菜单命令.......................... 292
技巧7创建、删除自定义右键快捷菜单 ............................. 293
技巧8屏蔽、恢复右键快捷菜单 ........................................ 294
12.3 自定义工具栏 ...................................................................295
技巧9创建、删除自定义工具栏 ........................................ 295
技巧10创建、删除内置命令工具栏................................... 296
技巧11恢复Excel的菜单和工具栏..................................... 297
第13章窗体的应用
13.1 输入对话框窗体................................................................300
技巧1利用对话框输入数据................................................ 300
技巧2利用对话框获取单元格地址..................................... 301
技巧3防止用户输入错误信息............................................ 303
13.2 消息对话框窗体................................................................304
技巧4仅显示简单提示信息................................................ 304
技巧5显示特定按钮、图标和标题..................................... 305
技巧6单击按钮返回提示信息............................................ 306
技巧7设置自动关闭消息对话框 ........................................ 307
13.3 用户窗体...........................................................................308
技巧8创建用户窗体 .......................................................... 308
技巧9两种状态显示用户窗体............................................ 309
技巧10在指定位置显示用户窗体 ...................................... 310
技巧11扩大、恢复用户窗体.............................................. 311
技巧12全屏显示用户窗体 ................................................. 313
技巧13在标题栏中添加最大化和最小化按钮 .................... 314
技巧14设置用户窗体标题文字.......................................... 316
技巧15同时显示多个用户窗体.......................................... 317
技巧16在用户窗体中自动添加、删除控件........................ 317
技巧17在用户窗体中使用滚动条 ...................................... 319
技巧18禁用用户窗体右上角的关闭按钮 ........................... 321
第14章控件的应用
14.1 表单(窗体)控件 ............................................................324
技巧1查看所有表单控件的名称 ........................................ 324
技巧2插入表单控件(1) ................................................. 325
技巧3插入表单控件(2) ................................................. 326
技巧4插入表单控件并添加文字 ........................................ 326
技巧5插入表单控件并自定义名称..................................... 327
技巧6插入表单控件并指定宏............................................ 328
技巧7插入列表框并设置选项............................................ 329
技巧8插入列表框、组合框并设置选项(1) .................... 330
技巧9插入列表框、组合框并设置选项(2) .................... 331
技巧37删除列表框、组合框中的单个选项........................ 357
技巧38删除列表框、组合框中的所有选项........................ 358
技巧39设置数值调节框的项目值小于1 ............................. 359
第6篇高级应用篇
第15章数据库的应用
15.1 数据库的基本操作 ............................................................362
技巧1创建Access数据库文件 ........................................... 362
技巧2添加数据表.............................................................. 364
技巧3删除数据表.............................................................. 365
技巧4添加已知字段 .......................................................... 366
技巧5添加自定义字段....................................................... 367
技巧6设置字段的类型....................................................... 368
技巧7设置字段的长度....................................................... 369
技巧8删除字段.................................................................. 370
技巧9添加已知数据记录 ................................................... 371
技巧10添加自定义数据记录.............................................. 373
技巧11修改数据记录 ........................................................ 374
技巧12删除数据记录 ........................................................ 376
15.2 数据库基本信息的查看.....................................................377
技巧13查看数据库中所有数据表的名称 ........................... 377
技巧14查看数据表中所有字段的名称 ............................... 378
技巧15查看指定的数据表是否存在................................... 379
技巧16查看指定的字段是否存在 ...................................... 380
技巧17查看符合指定条件的数据记录 ............................... 380
技巧18在工作表中导入数据库中的全部数据 .................... 381
第16章文件系统的操作
16.1 文件的操作.......................................................................386
技巧1查看文件夹中的所有文件(1) ............................... 386
技巧2查看文件夹中的所有文件(2) ............................... 386
技巧3判断文件是否存在 ................................................... 388
技巧4判断文件是否处于锁定状态..................................... 388
技巧5查看指定文件的名称................................................ 389
技巧6查看文件的大小....................................................... 390
技巧7查看文件的修改时间................................................ 390
技巧8查看文件的属性....................................................... 391
技巧9查看文件的完整信息................................................ 391
技巧10查看文本文件的全部内容 ...................................... 392
技巧11查看文本文件的指定字符 ...................................... 393
技巧12查看文本文件的行数据.......................................... 394
技巧13新建文本文件并输入数据 ...................................... 394
技巧14重命名文件............................................................ 396
技巧15复制文件................................................................ 396
技巧16移动文件................................................................ 397
技巧17删除文件................................................................ 398
16.2 文件夹的操作 ...................................................................399
技巧18查看常用的系统文件夹.......................................... 399
技巧19查看文件夹中包含的子文件夹 ............................... 400
技巧20查看文件夹的属性 ................................................. 401
技巧21查看文件夹的完整信息.......................................... 401
技巧22判断文件夹是否存在.............................................. 402
技巧23通过对话框选择文件夹.......................................... 403
技巧24新建文件夹............................................................ 403
技巧25重命名、取消重命名文件夹................................... 404
技巧26复制文件夹............................................................ 406
技巧27移动文件夹............................................................ 406
技巧28删除文件夹............................................................ 407
第17章其他应用程序的操作
17.1 操作Office组件 .................................................................410
技巧1新建Word文档(1)................................................ 410
技巧2新建Word文档(2)................................................ 411
技巧3打开Word文档(1)................................................ 413
技巧4打开Word文档(2)................................................ 413
技巧5将工作表数据输出至Word文档中 ............................ 414
技巧6将Word文档的内容输出至工作表中......................... 415
技巧7新建PowerPoint文稿 ............................................... 417
技巧8打开PowerPoint文稿(1)...................................... 418
技巧9打开PowerPoint文稿(2)...................................... 419
技巧10将工作表数据输出至新建的PowerPoint文稿中...... 419
技巧11将工作表数据通过Outlook邮件发送(1) ............. 421
技巧12将工作表数据通过Outlook邮件发送(2) ............. 422
技巧13将当前工作簿设置为Outlook邮件的附件 ............... 423
17.2 操作Internet......................................................................424
技巧14打开指定的网页(1)............................................ 424
技巧15打开指定的网页(2)............................................ 425
技巧16打开指定的网页(3)............................................ 425
技巧17查询并获取网页数据.............................................. 426
技巧18制作自定义浏览器 ................................................. 426
17.3 操作Windows附件工具.....................................................430
技巧19打开记事本和计算器.............................................. 430
技巧20打开控制面板的子项目.......................................... 431
技巧21获取当前电脑的分辨率.......................................... 433
17.4 操作XML文件 ...................................................................433
技巧22创建XML文件 ........................................................ 433
技巧23将当前工作表保存为XML文件............................... 435
技巧24保存指定单元格区域为XML文件 ........................... 436
第18章代码调试及优化
18.1 代码调试...........................................................................440
技巧1设置断点.................................................................. 440
技巧2使用Debug.Print语句............................................... 442
技巧3立即窗口.................................................................. 443
技巧4本地窗口.................................................................. 444
技巧5监视窗口.................................................................. 446
技巧6单步调试.................................................................. 450
18.2 代码优化...........................................................................451
技巧7尽量使用Excel内置函数 .......................................... 451
技巧8尽量减少使用对象引用............................................ 451
技巧9尽量减少“.”符号引用 .......................................... 453
技巧10避免对象的激活或选择 ......................................... 454
技巧11 有效地使用数组..................................................... 455