admin 管理员组文章数量: 1184232
2024年1月23日发(作者：common lisp)
EXCEL VBA常用代码实战大全
VBA常用技巧
目录
VBA常用技巧 ----------------------------------------------------------------- 1
第1章 Range（单元格）对象 ------------------------------------------- 10
技巧1 单元格的引用方法 -------------------------------------------- 10
1-1 使用Range属性 -------------------------------------------- 10
1-2 使用Cells属性 -------------------------------------------- 11
1-3 使用快捷记号 ---------------------------------------------- 11
1-4 使用Offset属性-------------------------------------------- 12
1-5 使用Resize属性-------------------------------------------- 13
1-6 使用Union方法 -------------------------------------------- 14
1-7 使用UsedRange属性----------------------------------------- 14
1-8 使用CurrentRegion属性 ------------------------------------- 15
技巧2 选定单元格区域的方法 ---------------------------------------- 15
2-1 使用Select方法-------------------------------------------- 15
2-2 使用Activate方法------------------------------------------ 16
2-3 使用Goto方法 --------------------------------------------- 17
技巧3 获得指定行、列中的最后一个非空单元格 ------------------------ 17
技巧4 定位单元格 -------------------------------------------------- 20
技巧5 查找单元格 -------------------------------------------------- 21
5-1 使用Find方法 --------------------------------------------- 21
5-2 使用Like运算符-------------------------------------------- 25
技巧6 替换单元格内字符串 ------------------------------------------ 26
技巧7 复制单元格区域 ---------------------------------------------- 27
技巧8 仅复制数值到另一区域 ---------------------------------------- 30
8-1 使用选择性粘贴 -------------------------------------------- 30
8-2 直接赋值的方法 -------------------------------------------- 31
技巧9 单元格自动进入编辑状态 -------------------------------------- 32
技巧10 禁用单元格拖放功能 ----------------------------------------- 32
技巧11 单元格格式操作 --------------------------------------------- 33
11-1 单元格字体格式设置 ---------------------------------------- 33
11-2 设置单元格内部格式 ---------------------------------------- 35
11-3 为单元格区域添加边框 -------------------------------------- 36
11-4 灵活设置单元格的行高列宽 ---------------------------------- 38
技巧12 单元格中的数据有效性 --------------------------------------- 39
12-1 在单元格中建立数据有效性 ---------------------------------- 39
12-2 判断单元格是否存在数据有效性 ------------------------------ 41
12-3 动态的数据有效性------------------------------------------ 41
12-4 自动展开数据有效性下拉列表 -------------------------------- 43
技巧13 单元格中的公式 --------------------------------------------- 44
1
EXCEL VBA常用代码实战大全
13-1 在单元格中写入公式---------------------------------------- 44
13-2 检查单元格是否含有公式 ------------------------------------ 45
13-3 判断单元格公式是否存在错误 -------------------------------- 46
13-4 取得单元格中公式的引用单元格 ------------------------------ 47
13-5 将单元格中的公式转换为数值 -------------------------------- 48
技巧14 单元格中的批注 --------------------------------------------- 49
14-1 判断单元格是否存在批注 ------------------------------------ 49
14-2 为单元格添加批注------------------------------------------ 50
14-3 删除单元格中的批注---------------------------------------- 51
技巧15 合并单元格操作 --------------------------------------------- 52
15-1 判断单元格区域是否存在合并单元格 -------------------------- 52
15-2 合并单元格时连接每个单元格的文本 -------------------------- 53
15-3 合并内容相同的连续单元格 ---------------------------------- 54
15-4 取消合并单元格时在每个单元格中保留内容 -------------------- 56
技巧16 高亮显示单元格区域 ----------------------------------------- 57
技巧17 双击被保护单元格时不显示提示消息框 ------------------------- 58
技巧18 重新计算工作表指定区域 ------------------------------------- 60
技巧19 录入数据后单元格自动保护 ----------------------------------- 60
技巧20 工作表事件Target参数的使用方法 ---------------------------- 62
20-1 使用单元格的Address 属性 --------------------------------- 62
20-2 使用Column属性和Row属性 --------------------------------- 63
20-3 使用Intersect方法---------------------------------------- 63
第2章 Worksheet（工作表）对象 --------------------------------------- 65
技巧21 引用工作表的方式 ------------------------------------------- 65
21-1 使用工作表的名称------------------------------------------ 65
21-2 使用工作表的索引号---------------------------------------- 65
21-3 使用工作表的代码名称 -------------------------------------- 66
21-4 使用ActiveSheet属性引用活动工作表 ------------------------ 66
技巧22 选择工作表的方法 ------------------------------------------- 67
技巧23 遍历工作表的方法 ------------------------------------------- 68
23-1 使用Next 语句 -------------------------------------- 68
23-2 使用Next 语句 --------------------------------- 70
技巧24 在工作表中上下翻页 ----------------------------------------- 71
技巧25 工作表的添加与删除 ----------------------------------------- 72
技巧26 禁止删除指定工作表 ----------------------------------------- 76
技巧27 自动建立工作表目录 ----------------------------------------- 78
技巧28 工作表的深度隐藏 ------------------------------------------- 80
技巧29 防止更改工作表的名称 --------------------------------------- 82
技巧30 工作表中一次插入多行 --------------------------------------- 83
技巧31 删除工作表中的空行 ----------------------------------------- 84
技巧32 删除工作表的重复行 ----------------------------------------- 86
技巧33 定位删除特定内容所在的行 ----------------------------------- 88
技巧34 判断是否选中整行 ------------------------------------------- 89
技巧35 限制工作表的滚动区域 --------------------------------------- 90
2
EXCEL VBA常用代码实战大全
技巧36 复制自动筛选后的数据区域 ----------------------------------- 91
技巧37 使用高级筛选获得不重复记录 --------------------------------- 93
技巧38 工作表的保护与解除保护 ------------------------------------- 94
技巧39 奇偶页打印 ------------------------------------------------- 97
第3章 Wordbook（工作簿）对象 ---------------------------------------- 99
技巧40 工作簿的引用方法 ------------------------------------------- 99
40-1 使用工作簿的名称------------------------------------------ 99
40-2 使用工作簿的索引号---------------------------------------- 99
40-3 使用ThisWorkbook ---------------------------------------- 100
40-4 使用ActiveWorkbook -------------------------------------- 101
技巧41 新建工作簿文件 -------------------------------------------- 101
技巧42 打开指定的工作簿 ------------------------------------------ 103
技巧43 判断指定工作簿是否打开 ------------------------------------ 106
43-1 遍历Workbooks集合方法 ----------------------------------- 106
43-2 错误处理方法 -------------------------------------------- 106
技巧44 禁用宏则关闭工作簿 ---------------------------------------- 107
技巧45 关闭工作簿不显示保存对话框 -------------------------------- 111
45-1 使用Close方法关闭工作簿 --------------------------------- 111
45-2 单击工作簿关闭按钮关闭工作簿 ----------------------------- 113
技巧46 禁用工作簿的关闭按钮 -------------------------------------- 113
技巧47 保存工作簿的方法 ------------------------------------------ 115
47-1 使用Save方法 ------------------------------------------- 115
47-2 直接保存为另一文件名 ------------------------------------- 115
47-3 保存工作簿副本 ------------------------------------------ 115
技巧48 保存指定工作表为工作簿文件 -------------------------------- 116
技巧49 打印预览时不触发事件 -------------------------------------- 118
技巧50 设置工作簿文档属性信息 ------------------------------------ 120
技巧51 不打开工作簿取得其他工作簿数据 ---------------------------- 121
51-1 使用公式 ------------------------------------------------ 121
51-2 使用GetObject函数--------------------------------------- 122
51-3 隐藏Application对象 ------------------------------------- 123
51-4 使用ExecuteExcel4Macro方法 ------------------------------ 124
51-5 使用SQL连接 -------------------------------------------- 125
技巧52 返回窗口的可视区域地址 ------------------------------------ 126
第4章 Shape（图形）、Chart（图表）对象 ------------------------------ 128
技巧53 在工作表中添加图形 ---------------------------------------- 128
技巧54 导出工作表中的图片 ---------------------------------------- 133
技巧55 在工作表中添加艺术字 -------------------------------------- 135
技巧56 遍历工作表中的图形 ---------------------------------------- 137
技巧57 移动、旋转图片 -------------------------------------------- 139
技巧58 工作表中自动插入图片 -------------------------------------- 140
技巧59 固定工作表中图形的位置 ------------------------------------ 143
技巧60 使用VBA自动生成图表 -------------------------------------- 145
技巧61 使用独立窗口显示图表 -------------------------------------- 149
3
EXCEL VBA常用代码实战大全
技巧62 导出工作表中的图表 ---------------------------------------- 150
技巧63 多图表制作 ------------------------------------------------ 151
第5章 Application对象 --------------------------------------------- 155
技巧64 取得Excel版本信息 ---------------------------------------- 155
技巧65 取得当前用户名称 ------------------------------------------ 156
技巧66 Excel中的“定时器” --------------------------------------- 156
技巧67 设置活动打印机的名称 -------------------------------------- 158
技巧68 屏蔽、改变组合键的功能 ------------------------------------ 159
技巧69 设置Excel窗口标题栏 -------------------------------------- 160
技巧70 自定义Excel状态栏 ---------------------------------------- 161
技巧71 灵活退出Excel -------------------------------------------- 162
技巧72 隐藏Excel主窗口 ------------------------------------------ 163
72-1 设置Application对象的Visible属性 ----------------------- 163
72-2 将窗口移出屏幕 ------------------------------------------ 164
72-3 设置工作簿作为加载宏运行 --------------------------------- 165
第6章 使用对话框 --------------------------------------------------- 167
技巧73 使用Msgbox函数 ------------------------------------------- 167
73-1 显示简单的提示信息--------------------------------------- 167
73-2 定制个性化的消息框--------------------------------------- 168
73-3 获得消息框的返回值--------------------------------------- 169
73-4 在消息框中排版 ------------------------------------------ 171
73-5 对齐消息框中显示的信息 ----------------------------------- 172
技巧74 自动关闭的消息框 ------------------------------------------ 174
74-1 使用方法显示消息框 ------------------------ 174
74-2 使用API函数显示消息框 ----------------------------------- 175
技巧75 使用InputBox函数 ----------------------------------------- 176
75-1 简单的数据输入 ------------------------------------------ 176
75-2 使用对话框输入密码--------------------------------------- 178
技巧76 使用InputBox方法 ----------------------------------------- 180
76-1 输入指定类型的数据--------------------------------------- 180
76-2 获得单元格区域地址--------------------------------------- 182
技巧77 内置对话框 ------------------------------------------------ 183
77-1 调用内置的对话框----------------------------------------- 183
77-2 获取选定文件的文件名 ------------------------------------- 186
77-3 使用“另存为”对话框 ------------------------------------- 188
技巧78 调用操作系统“关于”对话框 -------------------------------- 190
第7章 菜单和工具栏 ------------------------------------------------- 192
技巧79 在菜单中添加菜单项 ---------------------------------------- 192
技巧80 在菜单栏指定位置添加菜单 ---------------------------------- 195
技巧81 屏蔽和删除工作表菜单 -------------------------------------- 197
技巧82 改变系统菜单的操作 ---------------------------------------- 198
技巧83 定制自己的系统菜单 ---------------------------------------- 199
技巧84 改变菜单按钮图标 ------------------------------------------ 205
技巧85 右键快捷菜单增加菜单项 ------------------------------------ 206
4
EXCEL VBA常用代码实战大全
技巧86 自定义右键快捷菜单 ---------------------------------------- 207
技巧87 使用右键菜单制作数据有效性 -------------------------------- 210
技巧88 禁用工作表右键菜单 ---------------------------------------- 212
技巧89 创建自定义工具栏 ------------------------------------------ 213
技巧90 自定义工具栏按钮图标 -------------------------------------- 216
技巧91 自定义工作簿图标 ------------------------------------------ 217
技巧92 移除工作表的最小最大化和关闭按钮 -------------------------- 218
技巧93 在工具栏上添加下拉列表框 ---------------------------------- 219
技巧94 屏蔽工作表的复制功能 -------------------------------------- 221
技巧95 禁用工具栏的自定义 ---------------------------------------- 222
技巧96 屏蔽所有的命令栏 ------------------------------------------ 225
技巧97 恢复Excel的命令栏 ---------------------------------------- 226
第8章 控件与用户窗体 ----------------------------------------------- 228
技巧98 限制文本框的输入 ------------------------------------------ 228
技巧99 文本框添加右键快捷菜单 ------------------------------------ 230
技巧100 文本框回车自动输入 --------------------------------------- 234
技巧101 自动选择文本框内容 --------------------------------------- 235
技巧102 设置文本框数据格式 --------------------------------------- 236
技巧103 限制文本框的输入长度 ------------------------------------- 238
技巧104 将光标返回文本框中 --------------------------------------- 239
技巧105 文本框的自动换行 ----------------------------------------- 241
技巧106 多个文本框数据相加 --------------------------------------- 243
技巧107 控件跟随活动单元格 --------------------------------------- 244
技巧108 高亮显示按钮 --------------------------------------------- 245
技巧109 组合框和列表框添加列表项的方法 --------------------------- 247
109-1 使用RowSource属性添加列表项 ---------------------------- 247
109-2 使用List属性添加列表项 --------------------------------- 248
109-3 使用AddItem方法添加列表项 ------------------------------ 249
技巧110 去除列表框数据源的重复值和空格 --------------------------- 251
技巧111 移动列表框条目 ------------------------------------------- 253
技巧112 允许多项选择的列表框 ------------------------------------- 256
技巧113 多列组合框和列表框的设置 --------------------------------- 259
113-1 多列组合框和列表框添加列表项 ---------------------------- 259
113-2 多列列表框写入工作表 ------------------------------------ 261
技巧114 输入时逐步提示信息 --------------------------------------- 263
技巧115 二级组合框 ----------------------------------------------- 270
技巧116 使用DTP控件输入日期 ------------------------------------- 272
技巧117 使用RefEdit控件选择区域 --------------------------------- 275
技巧118 如何注册控件 --------------------------------------------- 276
技巧119 遍历控件的方法 ------------------------------------------- 279
119-1 使用名称中的变量遍历控件 -------------------------------- 279
119-2 使用对象类型遍历控件 ------------------------------------ 281
119-3 使用程序标识符遍历控件 ---------------------------------- 282
119-4 使用名称中的变量遍历图形 -------------------------------- 283
5
EXCEL VBA常用代码实战大全
119-5 使用FormControlType属性遍历图形 ------------------------ 284
技巧120 使微调框最小变动量小于1 --------------------------------- 285
技巧121 不打印工作表中的控件 ------------------------------------- 287
121-1 设置控件格式-------------------------------------------- 287
121-2 设置控件的printobjcet属性 ------------------------------ 289
技巧122 在框架中使用滚动条 --------------------------------------- 289
技巧123 使用多页控件 --------------------------------------------- 291
技巧124 标签文字垂直居中对齐 ------------------------------------- 293
技巧125 使用TabStrip控件 ---------------------------------------- 295
技巧126 显示GIF动画图片 ----------------------------------------- 297
技巧127 播放Flash文件 ------------------------------------------- 300
技巧128 在工作表中添加窗体控件 ----------------------------------- 302
128-1 使用AddFormControl方法 --------------------------------- 303
128-2 使用Add方法-------------------------------------------- 305
技巧129 在工作表中添加ActiveX控件 ------------------------------- 307
129-1 使用Add方法-------------------------------------------- 308
129-2 使用AddOLEObject方法 ----------------------------------- 310
技巧130 使用spreadsheet控件 ------------------------------------- 311
技巧131 使用Listview控件 ---------------------------------------- 314
131-1 使用Listview控件显示数据列表 --------------------------- 314
131-2 在Listview控件中使用复选框 ----------------------------- 317
131-3 调整Listview控件的行距 --------------------------------- 319
131-4 在Listview控件中排序 ----------------------------------- 322
131-5 Listview控件的图标设置 ---------------------------------- 323
技巧132 调用非模式窗体 ------------------------------------------- 326
技巧133 进度条的制作 --------------------------------------------- 328
133-1 使用进度条控件------------------------------------------ 328
133-2 使用标签控件-------------------------------------------- 330
技巧134 使用TreeView控件显示层次 -------------------------------- 333
技巧135 用户窗体添加图标 ----------------------------------------- 337
技巧136 用户窗体添加最大最小化按纽 ------------------------------- 339
技巧137 禁用窗体标题栏的关闭按钮 --------------------------------- 340
技巧138 屏蔽窗体标题栏的关闭按钮 --------------------------------- 341
技巧139 无标题栏和边框的窗体 ------------------------------------- 343
技巧140 制作年月选择窗体 ----------------------------------------- 344
技巧141 自定义窗体中的鼠标指针类型 ------------------------------- 347
技巧142 调整窗体的显示位置 --------------------------------------- 348
技巧143 由鼠标确定窗体显示位置 ----------------------------------- 350
技巧144 用户窗体的打印 ------------------------------------------- 351
技巧145 使用自定义颜色设置窗体颜色 ------------------------------- 353
技巧146 在窗体中显示图表 ----------------------------------------- 354
146-1 使用Export方法 ----------------------------------------- 354
146-2 使用API函数-------------------------------------------- 356
技巧147 窗体运行时调整控件大小 ----------------------------------- 357
6
EXCEL VBA常用代码实战大全
技巧148 在用户窗体上添加菜单 ------------------------------------- 360
技巧149 在用户窗体上添加工具栏 ----------------------------------- 364
技巧150 使用代码添加窗体及控件 ----------------------------------- 369
技巧151 用户窗体的全屏显示 --------------------------------------- 375
151-1 设置用户窗体为应用程序的大小 ---------------------------- 375
151-2 根据屏幕分辨率进行设置 ---------------------------------- 376
技巧152 在用户窗体上添加状态栏 ----------------------------------- 377
第9章 函数的使用 --------------------------------------------------- 381
技巧153 调用工作表函数求和 --------------------------------------- 381
技巧154 查找最大、最小值 ----------------------------------------- 381
技巧155 不重复值的录入 ------------------------------------------- 383
技巧156 获得当月的最后一天 --------------------------------------- 385
技巧157 四舍五入运算 --------------------------------------------- 386
157-1 极小值修正法-------------------------------------------- 386
157-2 调用工作表函数法 ---------------------------------------- 387
技巧158 使用字符串函数 ------------------------------------------- 387
技巧159 使用日期函数 --------------------------------------------- 389
技巧160 判断是否为数值 ------------------------------------------- 393
技巧161 格式化数值、日期和时间 ----------------------------------- 394
技巧162 个人所得税自定义函数 ------------------------------------- 396
技巧163 人民币大写函数 ------------------------------------------- 398
技巧164 列号转换为列标 ------------------------------------------- 400
技巧165 判断工作表是否为空表 ------------------------------------- 401
技巧166 查找指定工作表 ------------------------------------------- 402
技巧167 查找指定工作簿是否打开 ----------------------------------- 404
技巧168 取得应用程序的安装路径 ----------------------------------- 404
技巧169 数组的使用 ----------------------------------------------- 406
169-1 代码运行时创建数组 -------------------------------------- 406
169-2 文本转换为数组------------------------------------------ 407
169-3 使用动态数组去除重复值 ---------------------------------- 409
第10章 文件操作 ----------------------------------------------------- 412
技巧170 导入文本文件 --------------------------------------------- 412
170-1 使用查询表导入------------------------------------------ 412
170-2 使用Open 语句导入 -------------------------------------- 413
170-3 使用OpenText方法 --------------------------------------- 415
技巧171 将数据写入文本文件 --------------------------------------- 416
171-1 使用Print # 语句 --------------------------------------- 416
171-2 另存为文本文件------------------------------------------ 418
技巧172 文件修改的日期和时间 ------------------------------------- 419
技巧173 查找文件或文件夹 ----------------------------------------- 420
技巧174 获得当前文件夹的名称 ------------------------------------- 422
技巧175 创建和删除文件夹 ----------------------------------------- 422
技巧176 重命名文件或文件夹 --------------------------------------- 423
技巧177 复制指定的文件 ------------------------------------------- 424
7
EXCEL VBA常用代码实战大全
技巧178 删除指定的文件 ------------------------------------------- 425
技巧179 搜索特定的文件 ------------------------------------------- 426
技巧180 使用WSH处理文件 ----------------------------------------- 428
180-1 获取文件信息-------------------------------------------- 428
180-2 查找文件 ----------------------------------------------- 430
180-3 移动文件 ----------------------------------------------- 431
180-4 复制文件 ----------------------------------------------- 431
180-5 删除文件 ----------------------------------------------- 432
180-6 创建文件夹 --------------------------------------------- 433
180-7 复制文件夹 --------------------------------------------- 434
180-8 移动文件夹 --------------------------------------------- 435
180-9 删除文件夹 --------------------------------------------- 435
180-10 导入文本文件------------------------------------------- 436
180-11 创建文本文件 ------------------------------------------- 438
第11章 其他应用 ----------------------------------------------------- 441
技巧181 取得电脑名称 --------------------------------------------- 441
技巧182 取得逻辑盘序列号 ----------------------------------------- 442
技巧183 使用API取得硬盘信息 ------------------------------------- 443
技巧184 使用数字签名 --------------------------------------------- 444
技巧185 暂停代码的运行 ------------------------------------------- 449
技巧186 定时关机 ------------------------------------------------- 450
技巧187 打开指定的网页 ------------------------------------------- 451
技巧188 VBE的操作 ------------------------------------------------ 452
188-1 添加模块和过程------------------------------------------ 452
188-2 建立事件过程-------------------------------------------- 454
188-3 模块的导入与导出 ---------------------------------------- 456
188-4 删除宏代码 --------------------------------------------- 457
技巧189 保护VBA代码 --------------------------------------------- 459
189-1 设置工程密码-------------------------------------------- 459
189-2 设置“工程不可查看” ------------------------------------ 460
技巧190 优化代码 ------------------------------------------------- 462
190-1 关闭屏幕刷新-------------------------------------------- 462
190-2 使用工作表函数------------------------------------------ 464
190-3 使用更快的单元格操作方法 -------------------------------- 465
190-4 使用With语句引用对象 ----------------------------------- 466
190-5 少用激活或选择语句 -------------------------------------- 468
技巧191 取得文件的基本名称 --------------------------------------- 469
技巧192 防止用户中断代码运行 ------------------------------------- 470
技巧193 加班费计算表 --------------------------------------------- 472
技巧194 制作发放条 ----------------------------------------------- 498
技巧195 费用统计表 ----------------------------------------------- 501
技巧196 职工花名册 ----------------------------------------------- 516
技巧197 收据系统 ------------------------------------------------- 529
技巧198 职工考勤系统 --------------------------------------------- 567
8
EXCEL VBA常用代码实战大全
9
EXCEL VBA常用代码实战大全
第1章 Range（单元格）对象
Range对象是Excel应用程序中最常用的对象，一个Range对象代表一个单元格、一行、一列、包含一个或者更多单元格区域（可以是连续的单元格，也可以是不连续的单元格）中选定的单元格，甚至是多个工作表上的一组单元格，在操作Excel 内的任何区域之前都需要将其表示为一个Range对象，然后使用该Range对象的方法和属性。
技巧1 单元格的引用方法
在VBA中经常需要引用单元格或单元格区域区域，主要有以下几种方法。
1-1 使用Range属性
VBA中可以使用Range属性返回单元格或单元格区域，如下面的代码所示。
#001 Sub RngSelect()
#002 ("A3:F6, B1:C5").Select
#003 End Sub
代码解析：
RngSelect过程使用Select方法选中A3:F6，B1:C5单元格区域。
Range属性返回一个Range对象，该对象代表一个单元格或单元格区域，语法如下：
Range(Cell1, Cell2)
参数Cell1是必需的，必须为 A1 样式引用的宏语言，可包括区域操作符（冒号）、相交区域操作符（空格）或合并区域操作符（逗号）。也可包括美元符号（即绝对地址，如“$A$1”）。可在区域中任一部分使用局部定义名称，如Range("B2:LastCell")，其中LastCell为已定义的单元格区域名称。
参数Cell2是可选的，区域左上角和右下角的单元格。
运行Sub RngSelect过程，选中A3:F6, B1:C5单元格区域，如图 1-1所示。
10
EXCEL VBA常用代码实战大全
图 1-1 使用Range属性引用单元格区域
注意 如果没有使用对象识别符，Range属性返回活动表的一个区域，如果活动表不是工作表，则该属性无效。
1-2 使用Cells属性
使用Cells属性返回一个Range对象，如下面的代码所示。
#001 Sub Cell()
#002 Dim icell As Integer
#003 For icell = 1 To 100
#004 (icell, 1).Value = icell
#005 Next
#006 End Sub
代码解析：
Cell过程使用Next语句为工作表中的A1:A100单元格区域填入序号。
Cells属性指定单元格区域中的单元格，语法如下：
Cells(RowIndex, ColumnIndex)
参数RowIndex是可选的，表示引用区域中的行序号。
参数ColumnIndex是可选的，表示引用区域中的列序号。
如果缺省参数，Cells属性返回引用对象的所有单元格。
Cells属性的参数可以使用变量，因此经常应用于在单元格区域中循环。
1-3 使用快捷记号
在VBA中可以将A1引用样式或命名区域名称使用方括号括起来，作为Range属性的快捷方式，这样就不必键入单词“Range”或使用引号，如下面的代码所示。
11
EXCEL VBA常用代码实战大全
#001 Sub Fastmark()
#002 [A1：A5] = 2
#003 [Fast] = 4
#004 End Sub
代码解析：
Fastmark过程使用快捷记号为单元格区域赋值。
第2行代码使用快捷记号将活动工作表中的A1：A5单元格赋值为2。
第3行代码将工作簿中已命名为“Fast”的单元格区域赋值为4。
注意 使用快捷记号引用单元格区域时只能使用固定字符串而不能使用变量。
1-4 使用Offset属性
可以使用Range对象的Offset属性返回一个基于引用的Range对象的单元格区域，如下面的代码所示。
#001 Sub Offset()
#002 ("A1:C3").Offset(3, 3).Select
#003 End Sub
代码解析：
Offset过程使用Range对象的Offset属性选中A1：A3单元格偏移三行三列后的区域。
应用于Range对象的Offset 属性的语法如下：
(RowOffset, ColumnOffset)
参数expression是必需的，该表达式返回一个Range对象。
参数RowOffset是可选的，区域偏移的行数（正值、负值或 0（零））。正值表示向下偏移，负值表示向上偏移，默认值为 0。
参数ColumnOffset是可选的，区域偏移的列数（正值、负值或 0（零））。正值表示向右偏移，负值表示向左偏移，默认值为 0。
运行Offset过程，选中A1：A3单元格偏称三行三列后的区域，如图 1-2所示。
12
EXCEL VBA常用代码实战大全
图 1-2 使用Range对象的Offset属性
1-5 使用Resize属性
使用Range对象的Resize属性调整指定区域的大小，并返回调整大小后的单元格区域，如下面的代码所示。
#001 Sub Resize()
#002 ("A1").Resize(3, 3).Select
#003 End Sub
代码解析：
Resize过程使用Range对象的Resize属性选中A1单元格扩展为三行三列后的区域。
Resize属性的语法如下：
(RowSize, ColumnSize)
参数expression是必需的，返回要调整大小的Range 对象
参数RowSize是可选的，新区域中的行数。如果省略该参数，则该区域中的行数保持不变。
参数ColumnSize是可选的，新区域中的列数。如果省略该参数。则该区域中的列数保持不变。
运行Resize过程，选中A1单元格扩展为三行三列后的区域，如图 1-3所示。
图 1-3 使用Resize属性调整区域大小
13
EXCEL VBA常用代码实战大全
1-6 使用Union方法
使用Union方法可以将多个非连续区域连接起来成为一个区域，从而可以实现对多个非连续区域一起进行操作，如下面的代码所示。
#001 Sub UnSelect()
#002 Union(("A1:D4"), ("E5:H8")).Select
#003 End Sub
代码解析：
UnSelect过程选择单元格A1:D4和E5:H8所组成的区域。Union方法返回两个或多个区域的合并区域，语法如下：
(Arg1, Arg2, ...)
其中参数expression是可选的，返回一个Application对象。
参数Arg1, Arg2, ...是必需的，至少指定两个Range对象。
运行UnSelect过程，选中单元格A1:D4和E5:H8所组成的区域，如图 1-4所示。
图 1-4 使用Union方法将多个非连续区域连接成一个区域
1-7 使用UsedRange属性
使用UsedRange属性返回指定工作表上已使用单元格组成的区域，如下面的代码所示。
#001 Sub UseSelect()
#002
#003 End Su
代码解析：
UseSelect过程使用UsedRange属性选择工作表上已使用单元格组成的区域，包括空单元格。如工作表中已使用A1单元格和D8单元格，运行UseSelect过程将选择A1到D8单元格区域，如图 1-5所示。
14
EXCEL VBA常用代码实战大全
图 1-5 使用UsedRange属性选择已使用区域
1-8 使用CurrentRegion属性
使用CurrentRegion属性返回指定工作表上当前的区域，如下面的代码所示。
#001 Sub CurrentSelect()
#002 ("A5").
#003 End Sub
代码解析：
CurrentSelect过程使用CurrentRegion属性选择工作表上A5单元格当前的区域，当前区域是一个边缘是任意空行和空列组合成的范围。
运行CurrentSelect过程将选择A5到B6单元格区域，如图 1-6所示。
图 1-6 CurrentRegion属性选择当前的区域
技巧2 选定单元格区域的方法
2-1 使用Select方法
在VBA中一般使用Select方法选定单元格或单元格区域，如下面的代码所示。
15
EXCEL VBA常用代码实战大全
#001 Sub RngSelect()
#002 te
#003 ("A1：B10").Select
#004 End Sub
代码解析：
RngSelect过程使用Select方法选定Sheet3中的A1：B10单元格区域，Select方法应用于Range对象时语法如下：
(Replace)
参数expression是必需的，一个有效的对象。
参数Replace是可选的，要替换的对象。
使用Select方法选定单元格时，单元格所在的工作表必需为活动工作表，所以在第2行代码中先使用Activate方法使Sheet3成为活动工作表，否则Select方法有可能出错，显示如图 2-1所示的错误提示。
图 2-1 Select方法无效提示
2-2 使用Activate方法
还可以使用Activate方法选定单元格或单元格区域，如下面的代码所示。
#001 Sub RngActivate()
#002 te
#003 ("A1:B10").Activate
#004 End Sub
代码解析：
RngActivate过程使用Activate方法选定Sheet3中的A1：B10单元格区域，Activate方法应用于Range对象时语法如下：
te
使用Activate方法选定单元格时，单元格所在的工作表也必需为活动工作表，否则Activate方法有可能出错，显示如图 2-2所示的错误提示。
16
EXCEL VBA常用代码实战大全
图 2-2 Activate方法无效提示
2-3 使用Goto方法
使用Goto方法无需使单元格所在的工作表成为活动工作表，如下面的代码所示。
#001 Sub RngGoto()
#002 Reference:=("A1：B10"), scroll:=True
#003 End Sub
代码解析：
RngGoto过程使用Goto方法选定Sheet3中的A1：B10单元格区域，并滚动工作表以显示该单元格。
Goto方法选定任意工作簿中的任意区域或任意Visual Basic过程，并且如果该工作簿未处于活动状态，就激活该工作簿，语法如下：
(Reference, Scroll)
参数expression是必需的，返回一个Application 对象。
参数Reference是可选的，Variant类型，指定目标。可以是Range对象、包含R1C1-样式记号的单元格引用的字符串或包含 Visual Basic 过程名的字符串。如果省略本参数，目标将是最近一次用Goto方法选定的区域。
参数Scroll是可选的，Variant类型，如果该值为True，则滚动窗口直至目标区域的左上角单元格出现在窗口的左上角。如果该值为False，则不滚动窗口。默认值为False。
技巧3 获得指定行、列中的最后一个非空单元格
使用VBA对工作表进行操作时，经常需要定位到指定行或列中最后一个非空单元格，此时可以使用Range对象的End属性，在取得单元格对象后便能获得该单元格的相关属性， 17
EXCEL VBA常用代码实战大全
如单元格地址、行列号、数值等，如下面的代码所示。
#001 Sub LastRow()
#002 Dim rng As Range
#003 Set rng = ("A65536").End(xlUp)
#004 MsgBox "A列中最后一个非空单元格是" & s(0, 0) _
#005 & ",行号" & & ",数值" &
#006 Set rng = Nothing
#007 End Sub
代码解析：
LastRow过程使用消息框显示工作表中A列最后非空单元格的地址、行号和数值。
End属性返回一个Range对象，该对象代表包含源区域的区域尾端的单元格。等同于按键
(Direction)
参数expression是必需的，一个有效的对象。
参数Direction是可选的，所要移动的方向，可以为表格 3-1所示的XlDirection 常量之一。
常量
xlDown
xlToRight
xlToLeft
xlUp
值
-4121
-4161
-4159
-4162
描述
向下
向右
向左
向上
表格 3-1 XlDirection 常量
Range对象的End属性返回的是一个Range对象，因此可以直接使用该对象的属性和方法。
运行LastRow过程结果如图 3-1所示。
18
EXCEL VBA常用代码实战大全
图 3-1 获得A列最后一个非空单元格
通过修改相应的参数，能够获得指定行中最后一个非空单元格，如下面的代码所示。
#001 Sub LastColumn()
#002 Dim rng As Range
#003 Set rng = ("IV1").End(xlToLeft)
#004 MsgBox "第一行中最后一个非空单元格是" & s(0, 0) _
#005 & ",列号" & & ",数值" &
#006 Set rng = Nothing
#007 End Sub
代码解析：
LastColumn过程使用消息框显示工作表中第一行最后一个非空单元格的地址、列号和数值，如图 3-2所示。
图 3-2 获得第一行最后一个非空单元格
19
EXCEL VBA常用代码实战大全
技巧4 定位单元格
在Excel中使用定位对话框可以选中工作表中特定的单元格区域，而在VBA中则使用SpecialCells方法，如下面的代码所示。
#001 Sub SpecialAddress()
#002 Dim rng As Range
#003 Set rng = lCells(xlCellTypeFormulas)
#004
#005 MsgBox "工作表中有公式的单元格为： " & s
#006 Set rng = Nothing
#007 End Sub
代码解析：
SpecialAddress过程使用SpecialCells方法选中工作表中有公式的单元格，并用消息框显示其地址。
SpecialCells方法返回一个Range对象，该对象代表与指定类型及值相匹配的所有单元格，语法如下：
lCells(Type, Value)
参数expression是必需的，返回一个有效的对象。
参数Type是必需的，要包含的单元格，可为表格 4-1所列的XlCellType常量之一。
常量
xlCellTypeAllFormatConditions
xlCellTypeAllValidation
xlCellTypeBlanks
xlCellTypeComments
xlCellTypeConstants
xlCellTypeFormulas
xlCellTypeLastCell
xlCellTypeSameFormatConditions
xlCellTypeSameValidation
xlCellTypeVisible
值
-4172
-4174
4
-4144
2
-4123
11
-4173
-4175
12
描述
任意格式单元格
含有验证条件的单元格
空单元格
含有注释的单元格
含有常量的单元格
含有公式的单元格
使用区域中最后的单元格
含有相同格式的单元格
含有相同验证条件的单元格
所有可见单元格
表格 4-1 XlCellType常量
第3行代码将SpecialCells方法的Type参数设置为xlCellTypeFormulas，返回的是含 20
EXCEL VBA常用代码实战大全
有公式的单元格，通过修改相应的参数可以返回不同的单元格。
参数Value是可选的，如果Type参数为xlCellTypeConstants或xlCellTypeFormulas，
此参数可用于确定结果中应包含哪几类单元格。将某几个值相加可使此方法返回多种类型的单元格。如果省略将选定所有常量或公式，可为表格 4-2所列的 XlSpecialCellsValue常量之一。
常量
xlErrors
xlLogical
xlNumbers
xlTextValues
值
16
4
1
2
描述
错误
逻辑值
数字
文本
表格 4-2 XlSpecialCellsValue常量
第5行代码使用消息框显示工作表中含有公式单元格的地址。SpecialCells方法返回的是Range对象，因此可以直接使用该对象的属性和方法。
运行SpecialAddress过程结果如图 4-1所示。
图 4-1 SpecialCells方法
技巧5 查找单元格
5-1 使用Find方法
在Excel中使用查找对话框可以查找工作表中特定内容的单元格，而在VBA中则使用Find方法，如下面的代码所示。
#001 Sub RngFind()
21
EXCEL VBA常用代码实战大全
#002 Dim StrFind As String
#003 Dim Rng As Range
#004 StrFind = InputBox("请输入要查找的值:")
#005 If Trim(StrFind) <> "" Then
#006 With ("A:A")
#007 Set Rng = .Find(What:=StrFind, _
#008 After:=.Cells(.), _
#009 LookIn:=xlValues, _
#010 LookAt:=xlWhole, _
#011 SearchOrder:=xlByRows, _
#012 SearchDirection:=xlNext, _
#013 MatchCase:=False)
#014 If Not Rng Is Nothing Then
#015 Rng, True
#016 Else
#017 MsgBox "没有找到该单元格!"
#018 End If
#019 End With
#020 End If
#021 End Sub
代码解析：
RngFind过程使用Find方法在工作表Sheet1的A列中查找InputBox函数对话框中所输入的值，并查找该值所在的第一个单元格。
第6到第13行代码在工作表Sheet1的A列中查找InputBox函数对话框中所输入的值。应用于Range对象的Find方法在区域中查找特定信息，并返回Range对象，该对象代表用于查找信息的第一个单元格。如果未发现匹配单元格，就返回Nothing，语法如下：
(What, After, LookIn, LookAt, SearchOrder, SearchDirection,
MatchCase, MatchByte, SerchFormat)
参数expression是必需的，该表达式返回一个Range对象。
参数What是必需的，要搜索的数据，可为字符串或任意数据类型。
参数After是可选的，表示搜索过程将从其之后开始进行的单元格，必须是区域中的单个单元格。查找时是从该单元格之后开始的，直到本方法绕回到指定的单元格时，才对其进行搜索。如果未指定本参数，搜索将从区域的左上角单元格之后开始。
22
EXCEL VBA常用代码实战大全
在本例中将After参数设置为A列的最后一个单元格，所以查找时从A1单元格开始搜索。
参数LookIn是可选的，信息类型。
参数LookAt是可选的，可为XlLookAt常量的xlWhole 或xlPart之一。
参数SearchOrder是可选的，可为XlSearchOrder常量的xlByRows或xlByColumns之一。
参数SearchDirection是可选的，搜索的方向，可为XlSearchDirection常量的xlNext或xlPrevious之一。
参数MatchCase是可选的，若为True，则进行区分大小写的查找。默认值为False。
参数MatchByte是可选的，仅在选择或安装了双字节语言支持时使用。若为True，则双字节字符仅匹配双字节字符。若为False，则双字节字符可匹配其等价的单字节字符。
参数SerchFormat是可选的，搜索的格式。
每次使用Find方法后，参数LookIn、LookAt、SearchOrder 和MatchByte的设置将保存。如果下次调用Find方法时不指定这些参数的值，就使用保存的值。因此每次使用该方法时请明确设置这些参数。
如果工作表的A列中存在重复的数值，那么需要使用FindNext方法或FindPrevious方法进行重复搜索，如下面的代码所示。
#001 Sub RngFindNext()
#002 Dim StrFind As String
#003 Dim Rng As Range
#004 Dim FindAddress As String
#005 StrFind = InputBox("请输入要查找的值:")
#006 If Trim(StrFind) <> "" Then
#007 With ("A:A")
#008 Set Rng = .Find(What:=StrFind, _
#009 After:=.Cells(.), _
#010 LookIn:=xlValues, _
#011 LookAt:=xlWhole, _
#012 SearchOrder:=xlByRows, _
#013 SearchDirection:=xlNext, _
#014 MatchCase:=False)
#015 If Not Rng Is Nothing Then
#016 FindAddress = s
23
EXCEL VBA常用代码实战大全
#017 Do
#018 ndex = 6
#019 Set Rng = .FindNext(Rng)
#020 Loop While Not Rng Is Nothing And s <>
FindAddress
#021 End If
#022 End With
#023 End If
#024 End Sub
代码解析：
RngFindNext过程在工作表Sheet1的A列中查找InputBox函数对话框中所输入的值，并将查到单元格底色设置成黄色。
第8行到第17行代码使用Find方法在工作表Sheet1的A列中查找。
第16行代码将查找到的第一个单元格地址赋给字符串变量FindAddress。
第18行代码将查找到的单元格底色设置成黄色。
第19行代码使用FindNext方法进行重复搜索。FindNext方法继续执行用Find方法启动的搜索。查找下一个匹配相同条件的单元格并返回代表单元格的Range对象，语法如下：
xt(After)
参数expression是必需的，返回一个Range对象。
参数After是可选的，指定一个单元格，查找将从该单元格之后开始。
第20行代码如果查找到的单元格地址等于字符串变量FindAddress所记录的地址，说明A列已搜索完毕，结束查找过程。
运行RngFindNext过程，在InputBox函数输入框中输入“196.01”后结果如图 5-1所示。
图 5-1 使用FindNext方法重复搜索
还可以使用FindPrevious方法进行重复搜索，FindPrevious方法的语法如下：
24
EXCEL VBA常用代码实战大全
evious(After)
FindPrevious方法和FindNext方法唯一的区别是FindPrevious方法查找匹配相同条件的前一个单元格而FindNext方法是查找匹配相同条件的下一个单元格。
5-2 使用Like运算符
使用Like运算符可以进行更为复杂的模式匹配查找，如下面的代码所示。
#001 Sub RngLike()
#002 Dim rng As Range
#003 Dim a As Integer
#004 a = 1
#005 With Sheet2
#006 .Range("A:A").ClearContents
#007 For Each rng In .Range("B1:E1000")
#008 If Like "*a*" Then
#009 .Range("A" & a) =
#010 a = a + 1
#011 End If
#012 Next
#013 End With
#014 End Sub
代码解析：
RngLike过程使用Next语句和Like运算符在单元格区域B1:E10000中搜索含有“a”字符的单元格，找到匹配单元格以后将单元格的值写入到A列中。
第6行代码使用ClearContents方法清除A列区域的数据。
第7行代码使用Next语句在单元格区域B1:E10000中循环。
第8行代码使用Like运算符在单元格区域B1:E10000中搜索含有“a”字符的单元格。
Like运算符用来比较两个字符串，语法如下：
result = string Like pattern
参数string是必需的，字符串表达式。
参数pattern是必需的，字符串表达式。
如果string与pattern匹配，则result为True；如果不匹配，则result为False。但是如果string或pattern 中有一个为Null，则result 为 Null。
25
EXCEL VBA常用代码实战大全
参数pattern可以使用通配符、字符串列表或字符区间的任何组合来匹配字符串。表格
5-1列出pattern中允许的字符以及它们与什么进行匹配。
pattern中的字符
?
*
#
[charlist]
[!charlist]
符合string中的字符
任何单一字符
零个或多个字符
任何一个数字 (0–9)
charlist中的任何单一字符
不在charlist中的任何单一字符
表格 5-1 pattern中的匹配字符串
第9行代码将找到的匹配单元格的值写入到A列中。
运行RngLike过程结果如图 5-2所示。
图 5-2 使用Like运算符进行模式匹配查找
技巧6 替换单元格内字符串
如果需要替换单元格内指定的字符串，那么使用Range对象的Replace方法，如下面的代码所示。
#001 Sub RngReplace()
#002 Range("A1:A5").Replace "通州", "南通"
#003 End Sub
代码解析：
26
EXCEL VBA常用代码实战大全
RngReplace过程将工作表A1:A5单元格中的“通州”字符串替换成“南通”字符串。
应用于Range对象的Replace方法替换指定区域内单元格中的字符，语法如下：
e(What, Replacement, LookAt, SearchOrder, MatchCase,
MatchByte, SearchFormat, ReplaceFormat)
其中参数expression是必需的，返回一个Range对象。
参数What是必需的，要搜索的字符串。
参数Replacement是必需的，替换的字符串。
运行RngReplace过程前工作表如图 6-1所示，运行RngReplace过程后结果如图 6-2所示。
图 6-1 替换前单元格
图 6-2 替换后单元格
技巧7 复制单元格区域
在实际操作中，经常需要复制指定的单元格区域到另外一个单元格区域。要复制指定单元格区域到其他位置，使用Range对象的Copy方法，如下面的代码所示。
#001 Sub RangeCopy()
#002 yAlerts = False
27
EXCEL VBA常用代码实战大全
#003 ("A1"). ("A1")
#004 yAlerts = True
#005 End Sub
代码解析：
RangeCopy过程将如图 7-1所示的Sheet1工作表中A1单元格的当前区域复制到Sheet2工作表中以A1单元格为左上角单元格的区域，如图 7-2所示。
图 7-1 需复制的数据表
图 7-2 复制结果
Range对象的Copy方法的语法如下：
Copy(Destination)
参数Destination表示复制单元格区域的目标区域，如果省略该参数，Excel将把该区域复制到剪贴板中。
使用Copy方法复制单元格区域时，也复制了该单元格区域的格式，如图 7-2所示。
复制单元格区域时，如果目标区域为非空单元格区域，Excel将显示如图 7-3所示的消息框提示是否替换单元格内容，可以设置yAlerts属性值为False，使复制时不出现该消息框。
图 7-3 替换对话框
第2行代码通常复制单元格区域的操作不会将单元格区域的列宽大小同时复制，如图
7-2所示。如果希望在复制单元格区域的同时，也复制源区域的列宽大小，可以使用下面的代码。
#001 Sub CopyWithSameColumnWidths()
28
EXCEL VBA常用代码实战大全
#002 ("A1").
#003 With ("A1")
#004 .PasteSpecial xlPasteColumnWidths
#005 .PasteSpecial xlPasteAll
#006 End With
#007 yMode = False
#008 End Sub
代码解析：
第4行代码使用Range对象的PasteSpecial方法选择性粘贴剪贴板中的Range对象的列宽。
第5行代码粘贴剪贴板中的Range对象全部内容。
第7行代码取消应用程序复制模式。
应用于Range对象的PasteSpecial方法将剪贴板中的Range对象粘贴到指定区域，在粘贴时可以有选择的粘贴对象的部分属性。其语法如下：
PasteSpecial(Paste, Operation, SkipBlanks, Transpose)
参数Paste指定要粘贴的区域部分，可为表格 7-1所列的XlPasteType常量之一。
常量
xlPasteAll
xlPasteAllExceptBorders
xlPasteColumnWidths
xlPasteComments
xlPasteFormats
xlPasteFormulas
xlPasteFormulasAndNumberFormats
xlPasteValidation
xlPasteValues
xlPasteValuesAndNumberFormats
值
-4104
7
8
-4144
-4122
-4123
11
6
-4163
12
描述
全部（默认值）
边框除外
列宽
批注
格式
公式
公式和数字格式
有效性验证
数值
值和数字格式
表格 7-1 XlPasteType 常量
参数Operation指定粘贴操作。可为表格 7-2所列的XlPasteSpecialOperation常量之一。
常量
xlPasteSpecialOperationNone
xlPasteSpecialOperationAdd
值
-4142
2
29
描述
无（默认值）
加
EXCEL VBA常用代码实战大全
常量
xlPasteSpecialOperationSubtract
xlPasteSpecialOperationMultiply
xlPasteSpecialOperationDivide
值
3
4
5
描述
减
乘
除
表格 7-2 XlPasteSpecialOperation常量
参数SkipBlanks指示是否跳过空单元格，若参数值为True，则不将剪贴板上区域中的空白单元格粘贴到目标区域中。默认值为False。
参数Transpose指示是否进行转置，若参数值为True，则粘贴区域时转置行和列。默认值为False。
运行CopyWithSameColumnWidths过程后，Sheet3工作表如图 7-4所示，目标区域的各列列宽与源区域一致。
图 7-4 粘贴列宽后的复制结果
注意 使用PasteSpecial方法时指定xlPasteAll(粘贴全部)，不会粘贴列宽。
技巧8 仅复制数值到另一区域
如果在复制单元格区域时，仅希望复制单元格区域的数值，有下面几种方法。
8-1 使用选择性粘贴
使用选择性粘贴功能并指定粘贴数值，如下面的代码所示。
#001 Sub CopyPasteSpecial()
#002 ("A1").
#003 ("A1").PasteSpecial Paste:=xlPasteValues
#004 yMode = False
#005 End Sub
30
EXCEL VBA常用代码实战大全
代码解析：
CopyPasteSpecial过程复制工作表Sheet1中A1单元格的当前区域的数值到工作表Sheet2的A1单元格所在区域中。
第2行代码将如图 8-1所示的Sheet1中A1单元格的当前区域进行复制。
图 8-1 需复制的数据表
第3行代码使用选择性粘贴功能并指定粘贴数值，选择性粘贴数值仅复制了单元格区域的数值，单元格区域的格式（背景颜色、字体对齐格式和边框等）不会被复制，复制结果如图 8-2所示。
图 8-2 复制单元格区域数值
8-2 直接赋值的方法
除了使用Copy方法外，还可以使用直接赋值的方法，如下面的代码所示。
#001 Sub GetValueResize()
#002 With ("A1").CurrentRegion
#003 ("A1").Resize(., .).Value
= .Value
#004 End With
#005 End Sub
代码解析：
GetValueResize过程将工作表Sheet1中的A1单元格的当前区域的数值赋予工作表Sheet3的A1单元格所在的单元格区域。
在对单元格区域直接赋值时，应保证源区域大小与目标区域的大小一致，如果源区域为动态的单元格区域，可使用Resize方法确定目标区域。
运行GetValueResize过程，赋值结果如图 8-2所示。
31
EXCEL VBA常用代码实战大全
技巧9 单元格自动进入编辑状态
当光标选择单元格时无需双击，自动进入编辑状态，如下面的代码所示。
#001 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
#002 If = 3 And = 1 Then
#003 If Target <> "" Then
#004 ys "{F2}"
#005 End If
#006 End If
#007 End Sub
代码解析：
工作表的SelectionChange事件过程，当选择工作表C列有数据单元格时自动进入编辑状态。
第2、3行代码设置SelectionChange事件的触发条件，利用Target参数的Column属性和Count属性将事件的触发条件限制在C列并且只有在选择一个单元格时才发生。
第4行代码使用SendKeys方法发送一个F2键到应用程序，等同于选择单元格后按F2键，使单元格进入编辑状态。关于SendKeys方法请参阅技巧12-4。技巧10 禁用单元格拖放功能
在工作表中可以拖放单元格右下角的小十字对单元格内容进行复制等操作，如果不希望用户进行此操作可以禁用单元格拖放功能，如下面的代码所示。
#001 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
#002 If Not ect(Target, Range("A1:A15")) Is Nothing
Then
#003 agAndDrop = False
#004 Else
#005 agAndDrop = True
#006 End If
32
EXCEL VBA常用代码实战大全
#007 End Sub
代码解析：
工作表的SelectionChange事件过程，在指定的单元格区域禁用单元格的拖放功能。
CellDragAndDrop 属性设置单元格的拖放功能，如果允许使用单元格拖放功能，则该值为True。
为了不影响其他工作表，应在工作表的Deactivate事件中恢复单元格的拖放功能，如下面的代码所示。
#001 Private Sub Worksheet_Deactivate()
#002 agAndDrop = True
#003 End Sub
代码解析：
工作表的Deactivate事件过程，恢复单元格的拖放功能。
工作表的Deactivate事件当工作表从活动状态转为非活动状态时产生，语法如下：
Private Sub object_Deactivate()
参数object代表Worksheet对象。
当选择工作表“Sheet1”的A1：A15单元格时将禁用单元格的拖放功能，如图 10-1所示。
图 10-1 禁用单元格的拖放功能
技巧11 单元格格式操作
11-1 单元格字体格式设置
在VBA中可以对单元格的字体格式进行各种设置，如下面的代码所示。
33
EXCEL VBA常用代码实战大全
#001 Public Sub RngFont()
#002 With Range("A1").Font
#003 .Name = "华文彩云"
#004 .FontStyle = "Bold"
#005 .Size = 18
#006 .ColorIndex = 3
#007 .Underline = 2
#008 End With
#009 End Sub
代码解析：
RngFont过程对单元格A1的字体格式进行设置。
其中第3行代码设置字体为“华文彩云”，应用于Font对象的Name属性返回或设置对象的名称。
第4行代码设置字体为加粗，FontStyle属性返回或设置字体样式。设置为“Bold” 加粗字体，设置为“Italic”倾斜字体，也可以设置成“Bold Italic”。
第5行代码设置字体的大小为18磅，Size属性返回或设置字体大小。
第6行代码设置字体的颜色为红色，应用于Font对象的ColorIndex属性返回或设置字体的颜色，该颜色可指定为当前调色板中颜色的编号，如图 11-1所示。
图 11-1 调色板中颜色的编号
第7行代码设置字体为单下划线类型，Underline属性返回或设置应用于字体的下划线类型，可为表格 11-1所列的XlUnderlineStyle常量之一。
常量
xlUnderlineStyleNone
xlUnderlineStyleSingle
xlUnderlineStyleDouble
值
-4142
2
-4119
描述
无
单下划线
双下划线
34
EXCEL VBA常用代码实战大全
xlUnderlineStyleSingleAccounting
xlUnderlineStyleDoubleAccounting
4
5
会计用单下划线
会计用双下划线
表格 11-1 XlUnderlineStyle常量
运行RngFont过程结果如图 11-2所示。
图 11-2 单元格字体设置
11-2 设置单元格内部格式
设置单元格的Interior属性可以对单元格的内部格式进行设置，如下面的代码所示。
#001 Sub RngInterior()
#002 With Range("A1").Interior
#003 .ColorIndex = 3
#004 .Pattern = xlPatternCrissCross
#005 .PatternColorIndex = 6
#006 End With
#007 End Sub
代码解析：
RngInterior过程对A1单元格的内部格式进行设置。
第2行代码使用Interior属性返回单元格对象的内部。
第3行代码设置单元格边框内部的颜色为红色。应用于Interior对象的ColorIndex属性返回或设置边框内部的颜色，可指定为如图 11-1所示的当前调色板中颜色的编号或为XlColorIndex 常量之一：xlColorIndexAutomatic （自动填充）、xlColorIndexNone （无内部填充）。
第4行代码设置单元格设置内部图案为十字图案。应用于Interior对象的Pattern属性返回或者设置内部图案。
第5行代码设置单元格设置内部图案的颜色为黄色。应用于Interior对象的PatternColorIndex属性返回或设置内部图案的颜色，可指定为如图 11-1所示的当前调色板中颜色的编号XlColorIndex常量之一：xlColorIndexAutomatic （自动填充）、xlColorIndexNone （无内部填充）。
运行RngInterior过程结果如图 11-3所示。
35
EXCEL VBA常用代码实战大全
图 11-3 设置单元格内部格式
11-3 为单元格区域添加边框
我们为单元格区域添加边框时往往通过录制宏获取代码，但宏录制器生成的代码分别设置单元格区域的每个边框，因此代码多且效率低。使用Range对象的Borders集合可以快速的对单元格区域的每个边框应用相同的格式，而Range对象的BorderAround方法则可以快速地为单元格区域添加一个外边框，如下面的代码所示。
#001 Sub AddBorders()
#002 Dim rng As Range
#003 Set rng = Range("B4:G10")
#004 With s
#005 .LineStyle = xlContinuous
#006 .Weight = xlThin
#007 .ColorIndex = 5
#008 End With
#009 Around xlContinuous, xlMedium, 5
#010 Set rng = Nothing
#011 End Sub
代码解析：
AddBorders过程为单元格区域B4:G10设置内部统一边框并添加一个加粗外边框。
第4行到第8行代码使用Borders属性引用单元格区域的Borders集合，其中第5行代码设置其边框样式线条的样式，第6行代码设置边框线条的粗细，第7行代码设置边框的颜色。
应用于Range对象的Borders集合代表Range对象的4个边框（左边框、右边框、顶部边框和底部边框）的4个Border对象组成的集合，这4个边框既可单独返回，也可作为一个组同时返回。
第9行代码使用BorderAround方法为单元格区域添加一个加粗外边框。
应用于Range对象的BorderAround方法向单元格区域添加整个区域的外边框，并设 36
EXCEL VBA常用代码实战大全
置该边框的相关属性，其语法如下：
BorderAround(LineStyle, Weight, ColorIndex, Color)
其中LineStyle参数设置边框线条的样式，Weight参数设置边框线条的粗细，ColorIndex
设置边框颜色，Color参数以RGB值指定边框的颜色。
注意 指定Color参数可以设置颜色为当前调色板之处的其它颜色，不能同时指定ColorIndex参数和Color参数。
运行AddBorders过程，效果如图 11-4所示。
图 11-4 设置单元格区域边框
如果需要在单元格区域中应用多种边框格式，则需分别设置各边框格式，如下面的代码所示。
#001 Sub BordersDemo()
#002 Dim rng As Range
#003 Set rng = ("B4:G10")
#004 With s(xlInsideHorizontal)
#005 .LineStyle = xlDot
#006 .Weight = xlThin
#007 .ColorIndex = 5
#008 End With
#009 With s(xlInsideVertical)
#010 .LineStyle = xlContinuous
#011 .Weight = xlThin
#012 .ColorIndex = 5
#013 End With
#014 Around xlContinuous, xlMedium, 5
#015 Set rng = Nothing
#016 End Sub
代码解析：
37
EXCEL VBA常用代码实战大全
BordersDemo过程代码为单元格区域内部边框在水平和垂直方向上应用不同格式，并为区域添加一个加粗外边框。
Borders(index)属性返回单个Border对象，其Index参数取值可为表格 11-2所列的XlBordersIndex常量之一：
常量
xlDiagonalDown
xlDiagonalUp
xlEdgeBottom
xlEdgeLeft
xlEdgeRight
xlEdgeTop
xlInsideHorizontal
xlInsideVertical
值
5
6
9
7
10
8
12
11
描述
斜下边框
斜上边框
底部边框
左边框
右边框
顶部边框
内部水平
内部垂直
表格 11-2 XlBordersIndex常量
运行BordersDemo过程效果如图 1-1所示。
图 11-5 应用不同格式内部边框
11-4 灵活设置单元格的行高列宽
一般情况下单元格的行高列宽都是以磅为单位进行设置的，也可以使用英寸和厘米计量单位设置单元格的行高列宽，如下面的代码 所示。
#001 Sub RngToPoints()
#002 With Range("A1")
#003 .RowHeight = etersToPoints(2)
#004 .ColumnWidth = etersToPoints(1.5)
#005 End With
#006 With Range("A2")
#007 .RowHeight = ToPoints(1.2)
38
EXCEL VBA常用代码实战大全
#008 .ColumnWidth = ToPoints(0.3)
#009 End With
#010 End Sub
代码解析：
RngToPoints过程以英寸和厘米计量单位设置单元格的行高列宽。
第3、4行代码使用CentimetersToPoints方法以厘米为计量单位设置A1单元格的行高列宽。CentimetersToPoints方法将计量单位从厘米转换为磅（一磅等于 0.035 厘米），语法如下：
etersToPoints(Centimeters)
参数expression是必需的，返回一个Application对象。
参数Centimeters是必需的，指定要转换为磅值的厘米值。
第5、6行代码使用InchesToPoints方法以英寸为计量单位设置A2单元格的行高列宽。InchesToPoints方法将计量单位从英寸转换为磅，语法如下：
ToPoints(Inches)
参数expression是必需的，返回一个Application对象。
参数Inches是必需的，指定要转换为磅值的英寸值。
运行RngToPoints过程结果如图 11-6所示。
图 11-6 灵活设置单元格的行高列宽
技巧12 单元格中的数据有效性
12-1 在单元格中建立数据有效性
在单元格中建立数据有效性可以使用Add方法，如下面的代码所示。
#001 Sub Validation()
39
EXCEL VBA常用代码实战大全
#002 With Range("A1:A10").Validation
#003 .Delete
#004 .Add Type:=xlValidateList, _
#005 AlertStyle:=xlValidAlertStop, _
#006 Operator:=xlBetween, _
#007 Formula1:="1,2,3,4,5,6,7,8"
#008 End With
#009 End Sub
代码解析：
使用Add方法在A1：A10单元格中建立数据有效性。
第3行代码删除已建立的数据有效性，防止代码运行出错。
第4行到第7行代码使用Add方法建立数据有效性。应用于Validation对象的Add方法的语法如下：
(Type, AlertStyle, Operator, Formula1, Formula2)
参数expression是必需的，返回一个Validation对象。
参数Type是必需的，数据有效性类型。
参数AlertStyl是可选的，有效性检验警告样式。
参数Operator是可选的，数据有效性运算符。
参数Formula1是可选的，数据有效性公式的第一部分。
参数Formula2是可选的，当Operator为xlBetween或xlNotBetween时，数据有效性公式的第二部分（其他情况下，此参数被忽略）。
Add 方法所要求的参数依有效性检验的类型而定，如表格 12-1所示。
数据有效性类型
xlValidateCustom
参数
Formula1 必需，忽略 Formula2。Formula1 必须包含一个表达式，数据项有效时该表达式取值为 True，而数据项无效时取值为 False。
xlInputOnly
xlValidateList
能使用 AlertStyle、Formula1 或 Formula2 参数。
Formula1 必需，忽略 Formula2。Formula1 必须包含以逗号分隔的取值列表，或引用此列表的工作表。
xlValidateWholeNumberxlValidateDatexlValidateDecimalxlValidateTextLength
xlValidateTime
、、、或
必须指定 Formula1 或 Formula2 之一，或两者均指定。
表格 12-1 数据有效性类型
40
EXCEL VBA常用代码实战大全
12-2 判断单元格是否存在数据有效性
在VBA中没有专门的属性判断单元格是否存在数据有效性设置，可以使用Validation对象的有效性类型和错误陷阱来判断，如下面的代码所示。
#001 Sub Validation()
#002 On Error GoTo Line
#003 If Range("A2"). >= 0 Then
#004 MsgBox "单元格有数据有效性!"
#005 Exit Sub
#006 End If
#007 Line:
#008 MsgBox "单元格没有数据有效性!"
#009 End Sub
代码解析：
Validation过程使用Validation对象的有效性类型和错误陷阱来判断A2单元格中是否存在数据有效性。
第6行代码，如果A2单元格中存在数据有效性，Type参数值就会大于等于0，否则就会发生错误，使用On Error GoTo捕捉到错误后转移到第8行代码，显示一个消息框。
12-3 动态的数据有效性
利用VBA可以在单元格中建立动态的数据有效性，如下面的代码所示。
#001 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
#002 If = 1 And = 1 And > 1 Then
#003 With tion
#004 .Delete
#005 .Add Type:=xlValidateList, _
#006 AlertStyle:=xlValidAlertStop, _
#007 Operator:=xlBetween, _
#008 Formula1:="主机,显示器"
#009 End With
#010 End If
#011 End Sub
41
EXCEL VBA常用代码实战大全
#012 Private Sub Worksheet_Change(ByVal Target As Range)
#013 If = 1 And > 1 And = 1 Then
#014 With (0, 1).Validation
#015 .Delete
#016 Select Case Target
#017 Case "主机"
#018 .Add Type:=xlValidateList, _
#019 AlertStyle:=xlValidAlertStop, _
#020 Operator:=xlBetween, _
#021 Formula1:="Z286,Z386,Z486,Z586"
#022 Case "显示器"
#023 .Add Type:=xlValidateList, _
#024 AlertStyle:=xlValidAlertStop, _
#025 Operator:=xlBetween, _
#026 Formula1:="三星17,飞利浦15,三星15,飞利浦17"
#027 End Select
#028 End With
#029 End If
#030 End Sub
代码解析：
第1行到第11行代码，工作表的SelectionChange事件，当选择工作表的A列单元格时，在A2以下的单元格中建立动态的数据有效性。
其中第2行代码，利用SelectionChange事件的Target参数来限制事件的触发条件。
第3行到第9行代码使用Add方法在A列单元格中建立数据有效性。应用于Validation对象的Add方法请参阅技巧12-1。第12行到第30行代码，工作表的Change事件，当工作表A列单元格内容改变时，在B列单元格中建立动态的数据有效性。
其中第16行到第27行代码，根据A列单元格的内容在B列对应的单元格中建立数据有效性，其Formula1参数的值根据A列单元格的内容而变化，使之达到动态数据有效性的效果，如图 12-1、图 12-2所示。
42
EXCEL VBA常用代码实战大全
图 12-1 动态数据有效性1
图 12-2 动态数据有效性2
12-4 自动展开数据有效性下拉列表
选择工作表单元格时自动展开数据有效性的下拉列表，如下面的代码所示。
#001 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
#002 If = 5 Then ys "%{down}"
#003 End Sub
代码解析：
当选择工作表的E列中有数据有效性的单元格时使用SendKeys方法发送Alt+向下键，打开数据有效性的下拉列表。
应用于Application对象的SendKeys方法将击键发送给活动应用程序，语法如下：
ys(Keys, Wait)
参数expression是可选的，该表达式返回一个Application对象。
参数Keys是必需的，要发送的键或者组合键，以文本方式表示。
Keys参数可以指定任何单个键或与Alt、Ctrl 或Shift的组合键（或者这些键的组合）。每个键可用一个或多个字符表示。例如，"a" 表示字符 a，或者 "{ENTER}" 表示 Enter。
若要指定在按相应键时不会显示的字符（例如，Enter 或 Tab），请使用如表格 12-2所列的代码来表示相应的键，表中的每个代码表示键盘上的一个键。
键
Backspace
代码
{BACKSPACE} 或 {BS}
43
EXCEL VBA常用代码实战大全
Break
Caps Lock
Clear
Delete 或 Del
End
Enter
Enter（数字小键盘）
Esc
F1 到 F15
Help
Home
Ins
Num Lock
Page Down
Page Up
Return
Scroll Lock
Tab
向上键
向下键
向右键
向左键
{BREAK}
{CAPSLOCK}
{CLEAR}
{DELETE} 或 {DEL}
{END}
~（波形符）
{ENTER}
{ESCAPE} 或 {ESC}
{F1} 到 {F15}
{HELP}
{HOME}
{INSERT}
{NUMLOCK}
{PGDN}
{PGUP}
{RETURN}
{SCROLLLOCK}
{TAB}
{UP}
{DOWN}
{RIGHT}
{LEFT}
表格 12-2 按键代码
技巧13 单元格中的公式
13-1 在单元格中写入公式
使用Range对象的Formula属性可以在单元格区域中写入公式，如下面的代码所示。
#001 Sub rngFormula()
44
EXCEL VBA常用代码实战大全
#002 ("C1:C10").Formula = "=SUM(A1+B1)"
#003 End Sub
代码解析：
应用于Range对象的Formula属性返回或设置A1样式表示的Range对象的公式，语法如下：
a
参数expression是必需的，返回一个Range对象。
还可以使用FormulaR1C1属性返回或设置以R1C1-样式符号表示的公式，如下面的代码所示。
#001 Sub rngFormulaRC()
#002 ("C1:C10").FormulaR1C1 = "=SUM(RC[-2]+RC[-1])"
#003 End Sub
如果需要在单元格中写入数组公式则使用Range对象的FormulaArray属性。如下面的代码所示。
#001 Sub RngFormulaArray()
#002 ("C1").FormulaArray = "=A1:A2*B1:B2"
#003 End Sub
Range对象的FormulaArray属性返回或设置单元格区域的数组公式。
13-2 检查单元格是否含有公式
使用单元格的HasFormula属性检查单元格是否含有公式,如下面的代码所示。
#001 Private Sub CommandButton1_Click()
#002 Select Case mula
#003 Case True
#004 MsgBox "公式单元格!"
#005 Case False
#006 MsgBox "非公式单元格!"
#007 Case Else
#008 MsgBox "公式区域：" & lCells(xlCellTypeFormulas,
23).Address(0, 0)
#009 End Select
#010 End Sub
45
EXCEL VBA常用代码实战大全
代码解析：
工作表中按钮的单击过程，检查所选择的单元格区域是否含有公式。
第2行代码返回所选择单元格区域的HasFormula属性值。如果区域中所有单元格均包含公式，则该值为True；如果所有单元格均不包含公式，则该值为False。
第3、4行代码，如果返回True，说明区域中所有单元格均包含公式。
第5、6行代码，如果返回False，说明区域中所有单元格均不包含公式。
第7、8行代码，如果是混合区域，则显示包含公式的单元格地址，如图 13-1所示。
图 13-1 显示包含公式的单元格地址
13-3 判断单元格公式是否存在错误
当需要获取的单元格的值由公式返回时，公式返回的结果可能是一个错误文本，包含#NULL!、#DIV/0!、#VALUE!、#REF!、#NAME?、#NUM!、#N/A等。此时，当单元格公式返回结果为错误文本时，如果试图通过Value属性来获得公式的返回结果，将得到类型不匹配的错误信息，如图 13-2所示。
图 13-2 公式错误导致的消息
通过Range对象的Value属性的返回结果是否为错误类型，来判断公式是否存在错误，如下面的代码所示。
#001 Sub FormulaIsError()
#002 If r(Range("A1").Value) = True Then
#003 MsgBox "A1单元格错误类型为:" & Range("A1").Text
46
EXCEL VBA常用代码实战大全
#004 Else
#005 MsgBox "A1单元格公式结果为" & Range("A1").Value
#006 End If
#007 End Sub
代码解析“
FormulaIsError过程代码判断单元格A1中公式结果是否为错误，如果为错误则显示该错误类型，否则显示公式的结果，如图 13-3所示。
第2行代码使用IsError函数返回Boolean值，指出表达式是否为一个错误值，如果表达式表示一个错误，则IsError函数返回True，否则返回False。
图 13-3 显示公式错误结果
13-4 取得单元格中公式的引用单元格
如果需要取得单元格中公式的引用单元格对象，可以使用Range对象的Precedents属性，如下面的代码所示。
#001 Sub RngPrecedent()
#002 Dim rng As Range
#003 Set rng = ("C1").Precedents
#004 MsgBox "公式所引用的单元格有：" & s
#005 Set rng = Nothing
#006 End Sub
代码解析：
在工作表的C1单元格中写有公式“SUM(“A1:B1”)”，RngPrecedent过程使用Range对象的Precedents属性取得其引用的单元格A1:B1。
Precedents属性返回一个Range对象，该对象代表单元格的所有引用单元格。如果有若干引用单元格，那么该区域可能是多个的选定区域（Range 对象的联合）。
47
EXCEL VBA常用代码实战大全
运行RngPrecedent过程结果如图 13-4所示。
图 13-4 取得引用单元格
13-5 将单元格中的公式转换为数值
工作表中如果存在过多的公式将影响操作速度，将单元格中的函数与公式的结果转换为数值，可以提高工作表运算效率，有下面几种方法可以实现。
使用选择性粘贴的方法可以将函数与公式的结果转换为数值，如下面的代码所示。
#001 Sub SpecialPaste()
#002 With Range("A1:A10")
#003 .Copy
#004 .PasteSpecial Paste:=xlPasteValues
#005 End With
#006 yMode = False
#007 End Sub
代码解析：
SpecialPaste过程使用选择性粘贴方法将单元格区域的公式转换为数值。
第3行代码将单元格区域复制到剪贴板中。
应用于Range对象的Copy方法将单元格区域复制到指定的区域或剪贴板中，语法如下：
(Destination)
参数expression是必需的，该表达式返回一个Range对象。
参数Destination是可选的，指定区域要复制到的目标区域。如果省略该参数，Microsoft
Excel 将把该区域复制到剪贴板中。
第4行代码将剪贴板中的Range对象仅复制值到单元格区域中。
应用于Range对象的PasteSpecial方法将剪贴板中的Range对象粘贴到指定区域中，语法如下：
pecial(Paste, Operation, SkipBlanks, Transpose)
参数expression是必需的，该表达式返回一个Range对象。
48
EXCEL VBA常用代码实战大全
参数Paste是可选的，指定要粘贴的区域部分。在本例中设置为xlPasteValues，仅复制值到单元格区域中。
使用Value属性可以将函数与公式的结果转换为数值，如下面的代码所示。
#001 Sub UseValue()
#002 Range("A1:A10").Value = Range("A1:A10").Value
#003 End Sub
代码解析：
UseValue过程使用Value属性将函数与公式的结果转换为数值。
使用Formula属性可以将函数与公式的结果转换为数值，如下面的代码所示。
#001 Sub UseFormula()
#002 Range("A1").Formula = Range("A1").Value
#003 End Sub
代码解析：
UseFormula过程Formula属性将函数与公式的结果转换为数值。当Formula属性值为非公式时，返回的结果与Value属性一致。
技巧14 单元格中的批注
14-1 判断单元格是否存在批注
在VBA中，可以利用Range对象的Comment属性判断单元格是否存在批注，如下面的代码所示。
#001 Sub HasComment()
#002 If Range("A1").Comment Is Nothing Then
#003 MsgBox "A1单元格中没有批注!"
#004 Else
#005 MsgBox "A1单元格中批注内容为：" & Chr(13) & Range("A1").
#006 End If
#007 End Sub
49
版权声明：本文标题：EXCEL VBA常用代码实战大全 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.roclinux.cn/b/1705986103a496764.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论