EXCEL_VBA常用代码实战大全

VBA常用技巧代码解析yuanzhuping

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

VBA常用技巧代码解析yuanzhuping

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 使用For...Next 语句 ---------------------------------------------------------------- 68

23-2 使用For Each...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

VBA常用技巧代码解析yuanzhuping

技巧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

VBA常用技巧代码解析yuanzhuping

技巧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 使用WshShell.Popup方法显示消息框--------------------------------------- 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

VBA常用技巧代码解析yuanzhuping

技巧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

VBA常用技巧代码解析yuanzhuping

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

VBA常用技巧代码解析yuanzhuping

技巧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

VBA常用技巧代码解析yuanzhuping

技巧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

VBA常用技巧代码解析yuanzhuping

9

VBA常用技巧代码解析yuanzhuping

第1章Range（单元格）对象

Range对象是Excel应用程序中最常用的对象，一个Range对象代表一个单元格、一行、一列、包含一个或者更多单元格区域（可以是连续的单元格，也可以是不连续的单元格）中选定的单元格，甚至是多个工作表上的一组单元格，在操作Excel 内的任何区域之前都需要将其表示为一个Range对象，然后使用该Range对象的方法和属性。

技巧1 单元格的引用方法

在VBA中经常需要引用单元格或单元格区域区域，主要有以下几种方法。

1-1 使用Range属性

VBA中可以使用Range属性返回单元格或单元格区域，如下面的代码所示。

#001 Sub RngSelect()

#002 Sheet1.Range("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

VBA 常用技巧代码解析 yuanzhuping

11

图 1-1 使用Range 属性引用单元格区域 注意 如果没有使用对象识别符，Range 属性返回活动表的一个区域，如果活动表不是工作表，则该属性无效。

1-2 使用Cells 属性

使用Cells 属性返回一个Range 对象，如下面的代码所示。 #001 Sub Cell()

#002 Dim icell As Integer

#003 For icell = 1 To 100

#004 Sheet2.Cells(icell, 1).Value = icell

#005 Next

#006 End Sub

代码解析：

Cell 过程使用For...Next 语句为工作表中的A1:A100单元格区域填入序号。 Cells 属性指定单元格区域中的单元格，语法如下：

Cells(RowIndex, ColumnIndex)

参数RowIndex 是可选的，表示引用区域中的行序号。

参数ColumnIndex 是可选的，表示引用区域中的列序号。

如果缺省参数，Cells 属性返回引用对象的所有单元格。

Cells 属性的参数可以使用变量，因此经常应用于在单元格区域中循环。

1-3 使用快捷记号

在VBA 中可以将A1引用样式或命名区域名称使用方括号括起来，作为Range 属性的快捷方式，这样就不必键入单词“Range ”或使用引号，如下面的代码所示。

VBA常用技巧代码解析yuanzhuping #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 Sheet3.Range("A1:C3").Offset(3, 3).Select

#003 End Sub

代码解析：

Offset过程使用Range对象的Offset属性选中A1：A3单元格偏移三行三列后的区域。

应用于Range对象的Offset 属性的语法如下：

expression.Offset(RowOffset, ColumnOffset)

参数expression是必需的，该表达式返回一个Range对象。

参数RowOffset是可选的，区域偏移的行数（正值、负值或0（零））。正值表示向下偏移，负值表示向上偏移，默认值为0。

参数ColumnOffset是可选的，区域偏移的列数（正值、负值或0（零））。正值表示向右偏移，负值表示向左偏移，默认值为0。

运行Offset过程，选中A1：A3单元格偏称三行三列后的区域，如图1-2所示。

12

VBA 常用技巧代码解析 yuanzhuping

13

图 1-2 使用Range 对象的Offset 属性

1-5 使用Resize 属性

使用Range 对象的Resize 属性调整指定区域的大小，并返回调整大小后的单元格区域，如下面的代码所示。 #001 Sub Resize()

#002 Sheet4.Range("A1").Resize(3, 3).Select

#003 End Sub

代码解析：

Resize 过程使用Range 对象的Resize 属性选中A1单元格扩展为三行三列后的区域。 Resize 属性的语法如下：

expression.Resize(RowSize, ColumnSize)

参数expression 是必需的，返回要调整大小的Range 对象

参数RowSize 是可选的，新区域中的行数。如果省略该参数，则该区域中的行数保持不变。

参数ColumnSize 是可选的，新区域中的列数。如果省略该参数。则该区域中的列数保持不变。

运行Resize 过程，选中A1单元格扩展为三行三列后的区域，如图 1-3

所示。

图 1-3 使用Resize 属性调整区域大小

VBA 常用技巧代码解析 yuanzhuping

14 1-6 使用Union 方法

使用Union 方法可以将多个非连续区域连接起来成为一个区域，从而可以实现对多个非连续区域一起进行操作，如下面的代码所示。 #001 Sub UnSelect()

#002 Union(Sheet5.Range("A1:D4"), Sheet5.Range("E5:H8")).Select

#003 End Sub

代码解析：

UnSelect 过程选择单元格A1:D4和E5:H8所组成的区域。Union 方法返回两个或多个区域的合并区域，语法如下：

expression.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 fbc04e572b160b4e767fcfbfedRange.Select

#003 End Su

代码解析：

UseSelect 过程使用UsedRange 属性选择工作表上已使用单元格组成的区域，包括空单元格。如工作表中已使用A1单元格和D8单元格，运行UseSelect 过程将选择A1到D8单元格区域，如图 1-5所示。

VBA 常用技巧代码解析 yuanzhuping

15

图 1-5 使用UsedRange 属性选择已使用区域

1-8 使用CurrentRegion 属性

使用CurrentRegion 属性返回指定工作表上当前的区域，如下面的代码所示。 #001 Sub CurrentSelect()

#002 Sheet7.Range("A5").CurrentRegion.Select

#003 End Sub

代码解析：

CurrentSelect 过程使用CurrentRegion 属性选择工作表上A5单元格当前的区域，当前区域是一个边缘是任意空行和空列组合成的范围。

运行CurrentSelect 过程将选择A5到B6单元格区域，如图

1-6所示。

图 1-6 CurrentRegion 属性选择当前的区域

技巧2 选定单元格区域的方法

2-1 使用Select 方法

在VBA 中一般使用Select 方法选定单元格或单元格区域，如下面的代码所示。

VBA 常用技巧代码解析 yuanzhuping

16 #001 Sub RngSelect()

#002 Sheet3.Activate

#003 Sheet3.Range("A1：B10").Select

#004 End Sub

代码解析：

RngSelect 过程使用Select 方法选定Sheet3中的A1：B10单元格区域，Select 方法应用于Range 对象时语法如下：

expression.Select(Replace)

参数expression 是必需的，一个有效的对象。

参数Replace 是可选的，要替换的对象。

使用Select 方法选定单元格时，单元格所在的工作表必需为活动工作表，所以在第2行代码中先使用Activate 方法使Sheet3成为活动工作表，否则Select 方法有可能出错，

显示如图 2-1所示的错误提示。

图 2-1 Select 方法无效提示

2-2 使用Activate 方法

还可以使用Activate 方法选定单元格或单元格区域，如下面的代码所示。

#001 Sub RngActivate()

#002 Sheet3.Activate

#003 Sheet3.Range("A1:B10").Activate

#004 End Sub

代码解析：

RngActivate 过程使用Activate 方法选定Sheet3中的A1：B10单元格区域，Activate 方法应用于Range 对象时语法如下：

expression.Activate

使用Activate 方法选定单元格时，单元格所在的工作表也必需为活动工作表，否则Activate 方法有可能出错，显示如图 2-2所示的错误提示。

VBA 常用技巧代码解析 yuanzhuping

17

图 2-2 Activate 方法无效提示

2-3 使用Goto 方法

使用Goto 方法无需使单元格所在的工作表成为活动工作表，如下面的代码所示。 #001 Sub RngGoto()

#002 Application.Goto Reference:=Sheet3.Range("A1：B10"), scroll:=True #003 End Sub

代码解析：

RngGoto 过程使用Goto 方法选定Sheet3中的A1：B10单元格区域，并滚动工作表以显示该单元格。

Goto 方法选定任意工作簿中的任意区域或任意Visual Basic 过程，并且如果该工作簿未处于活动状态，就激活该工作簿，语法如下：

expression.Goto(Reference, Scroll)

参数expression 是必需的，返回一个Application 对象。

参数Reference 是可选的，Variant 类型，指定目标。可以是Range 对象、包含R1C1-样式记号的单元格引用的字符串或包含 Visual Basic 过程名的字符串。如果省略本参数，目标将是最近一次用Goto 方法选定的区域。

参数Scroll 是可选的，Variant 类型，如果该值为True ，则滚动窗口直至目标区域的左上角单元格出现在窗口的左上角。如果该值为False ，则不滚动窗口。默认值为False 。

技巧3 获得指定行、列中的最后一个非空单元格

使用VBA 对工作表进行操作时，经常需要定位到指定行或列中最后一个非空单元格，此时可以使用Range 对象的End 属性，在取得单元格对象后便能获得该单元格的相关属性，

VBA常用技巧代码解析yuanzhuping

如单元格地址、行列号、数值等，如下面的代码所示。

#001 Sub LastRow()

#002 Dim rng As Range

#003 Set rng = Sheet1.Range("A65536").End(xlUp)

#004 MsgBox "A列中最后一个非空单元格是" & rng.Address(0, 0) _

#005 & ",行号" & rng.Row & ",数值" & rng.Value

#006 Set rng = Nothing

#007 End Sub

代码解析：

LastRow过程使用消息框显示工作表中A列最后非空单元格的地址、行号和数值。

End属性返回一个Range对象，该对象代表包含源区域的区域尾端的单元格。等同于按键、、或，语法如下：expression.End(Direction)

参数expression是必需的，一个有效的对象。

参数Direction是可选的，所要移动的方向，可以为表格3-1所示的XlDirection 常量之一。

表格3-1 XlDirection 常量

Range对象的End属性返回的是一个Range对象，因此可以直接使用该对象的属性和方法。

运行LastRow过程结果如图3-1所示。

18

VBA 常用技巧代码解析 yuanzhuping

19

图 3-1 获得A 列最后一个非空单元格

通过修改相应的参数，能够获得指定行中最后一个非空单元格，如下面的代码所示。 #001 Sub LastColumn()

#002 Dim rng As Range

#003 Set rng = Sheet1.Range("IV1").End(xlToLeft)

#004 MsgBox "第一行中最后一个非空单元格是" & rng.Address(0, 0) _

#005 & ",

列号" & rng.Column & ",数值" & rng.Value

#006 Set rng = Nothing

#007 End Sub

代码解析：

LastColumn 过程使用消息框显示工作表中第一行最后一个非空单元格的地址、列号和数值，如图 3-2所示。

图 3-2 获得第一行最后一个非空单元格

VBA常用技巧代码解析yuanzhuping 技巧4 定位单元格

在Excel中使用定位对话框可以选中工作表中特定的单元格区域，而在VBA中则使用SpecialCells方法，如下面的代码所示。

#001 Sub SpecialAddress()

#002 Dim rng As Range

#003 Set rng = fbc04e572b160b4e767fcfbfedRange.SpecialCells(xlCellTypeFormulas)

#004 rng.Select

#005 MsgBox "工作表中有公式的单元格为： " & rng.Address

#006 Set rng = Nothing

#007 End Sub

代码解析：

SpecialAddress过程使用SpecialCells方法选中工作表中有公式的单元格，并用消息框显示其地址。

SpecialCells方法返回一个Range对象，该对象代表与指定类型及值相匹配的所有单元格，语法如下：

expression.SpecialCells(Type, Value)

参数expression是必需的，返回一个有效的对象。

参数Type是必需的，要包含的单元格，可为表格4-1所列的XlCellType常量之一。

表格4-1 XlCellType常量

第3行代码将SpecialCells方法的Type参数设置为xlCellTypeFormulas，返回的是含

20

VBA 常用技巧代码解析 yuanzhuping

21 有公式的单元格，通过修改相应的参数可以返回不同的单元格。

参数Value 是可选的，如果Type 参数为xlCellTypeConstants 或xlCellTypeFormulas ， 此参数可用于确定结果中应包含哪几类单元格。将某几个值相加可使此方法返回多种类型的单元格。如果省略将选定所有常量或公式，可为表格 4-2所列的 XlSpecialCellsValue 常量之一。

表格 4-2 XlSpecialCellsValue 常量

第5行代码使用消息框显示工作表中含有公式单元格的地址。SpecialCells 方法返回的是Range 对象，因此可以直接使用该对象的属性和方法。

运行SpecialAddress 过程结果如图 4-1所示。

图 4-1 SpecialCells 方法

技巧5 查找单元格

5-1 使用Find 方法

在Excel 中使用查找对话框可以查找工作表中特定内容的单元格，而在VBA 中则使用Find 方法，如下面的代码所示。

#001 Sub RngFind()

本文来源：https://www.bwwdw.com/article/gdbq.html