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

121-1 121-2 技巧122 技巧123 技巧124 技巧125 技巧126 技巧127 技巧128

128-1 128-2 技巧129

129-1 129-2 技巧130 技巧131

131-1 131-2 131-3 131-4 131-5 技巧132 技巧133

133-1 133-2 技巧134 技巧135 技巧136 技巧137 技巧138 技巧139 技巧140 技巧141 技巧142 技巧143 技巧144 技巧145 技巧146

146-1 146-2 技巧147 使用FormControlType属性遍历图形 --------------------------------------- 284 使微调框最小变动量小于1 ----------------------------------------------------- 285 不打印工作表中的控件 ---------------------------------------------------------- 287 设置控件格式 --------------------------------------------------------------------- 287 设置控件的printobjcet属性 --------------------------------------------------- 289 在框架中使用滚动条 ------------------------------------------------------------- 289 使用多页控件 ----------------------------------------------------------------------- 291 标签文字垂直居中对齐 ---------------------------------------------------------- 293 使用TabStrip控件 ----------------------------------------------------------------- 295 显示GIF动画图片 ---------------------------------------------------------------- 297 播放Flash文件 --------------------------------------------------------------------- 300 在工作表中添加窗体控件 ------------------------------------------------------- 302 使用AddFormControl方法 ---------------------------------------------------- 303 使用Add方法 -------------------------------------------------------------------- 305 在工作表中添加ActiveX控件 ------------------------------------------------- 307 使用Add方法 -------------------------------------------------------------------- 308 使用AddOLEObject方法 ------------------------------------------------------ 310 使用spreadsheet控件 ------------------------------------------------------------- 311 使用Listview控件 ----------------------------------------------------------------- 314 使用Listview控件显示数据列表 -------------------------------------------- 314 在Listview控件中使用复选框 ----------------------------------------------- 317 调整Listview控件的行距 ----------------------------------------------------- 319 在Listview控件中排序 --------------------------------------------------------- 322 Listview控件的图标设置 ------------------------------------------------------- 323 调用非模式窗体 -------------------------------------------------------------------- 326 进度条的制作 ----------------------------------------------------------------------- 328 使用进度条控件 ------------------------------------------------------------------ 328 使用标签控件 --------------------------------------------------------------------- 330 使用TreeView控件显示层次 --------------------------------------------------- 333 用户窗体添加图标 ----------------------------------------------------------------- 337 用户窗体添加最大最小化按纽 ------------------------------------------------- 339 禁用窗体标题栏的关闭按钮 ---------------------------------------------------- 340 屏蔽窗体标题栏的关闭按钮 ---------------------------------------------------- 341 无标题栏和边框的窗体 ---------------------------------------------------------- 343 制作年月选择窗体 ----------------------------------------------------------------- 344 自定义窗体中的鼠标指针类型 ------------------------------------------------- 347 调整窗体的显示位置 ------------------------------------------------------------- 348 由鼠标确定窗体显示位置 ------------------------------------------------------- 350 用户窗体的打印 -------------------------------------------------------------------- 351 使用自定义颜色设置窗体颜色 ------------------------------------------------- 353 在窗体中显示图表 ----------------------------------------------------------------- 354 使用Export方法 ----------------------------------------------------------------- 354 使用API函数 --------------------------------------------------------------------- 356 窗体运行时调整控件大小 ------------------------------------------------------- 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(\#003 End Sub

代码解析：

RngSelect过程使用Select方法选中A3:F6，B1:C5单元格区域。

Range属性返回一个Range对象，该对象代表一个单元格或单元格区域，语法如下：

Range(Cell1, Cell2)

参数Cell1是必需的，必须为 A1 样式引用的宏语言，可包括区域操作符（冒号）、相交区域操作符（空格）或合并区域操作符（逗号）。也可包括美元符号（即绝对地址，如“$A$1”）。可在区域中任一部分使用局部定义名称，如Range(\，其中LastCell为已定义的单元格区域名称。

参数Cell2是可选的，区域左上角和右下角的单元格。

运行Sub RngSelect过程，选中A3:F6, B1:C5单元格区域，如图 1-1所示。

10

VBA常用技巧代码解析 yuanzhuping

图 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”或使用引号，如下面的代码所示。

11

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(\#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

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

1-5 使用Resize属性

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

#001 Sub Resize()

#002 Sheet4.Range(\#003 End Sub

代码解析：

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

expression.Resize(RowSize, ColumnSize)

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

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

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

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

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

13

VBA常用技巧代码解析 yuanzhuping

1-6 使用Union方法

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

#001 Sub UnSelect()

#002 Union(Sheet5.Range(\#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 Sheet6.UsedRange.Select #003 End Su

代码解析：

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

14

VBA常用技巧代码解析 yuanzhuping

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

1-8 使用CurrentRegion属性

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

#001 Sub CurrentSelect()

#002 Sheet7.Range(\#003 End Sub

代码解析：

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

运行CurrentSelect过程将选择A5到B6单元格区域，如图 1-6所示。

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

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

2-1 使用Select方法

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

15

VBA常用技巧代码解析 yuanzhuping

#001 Sub RngSelect() #002 Sheet3.Activate

#003 Sheet3.Range(\：B10\#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(\#004 End Sub

代码解析：

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

expression.Activate

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

16

VBA常用技巧代码解析 yuanzhuping

图 2-2 Activate方法无效提示

2-3 使用Goto方法

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

#001 Sub RngGoto()

#002 Application.Goto Reference:=Sheet3.Range(\：B10\#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属性，在取得单元格对象后便能获得该单元格的相关属性，

17

VBA常用技巧代码解析 yuanzhuping

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

#001 Sub LastRow() #002 Dim rng As Range

#003 Set rng = Sheet1.Range(\

#004 MsgBox \列中最后一个非空单元格是\#005 & \行号\数值\#006 Set rng = Nothing #007 End Sub

代码解析：

LastRow过程使用消息框显示工作表中A列最后非空单元格的地址、行号和数值。 End属性返回一个Range对象，该对象代表包含源区域的区域尾端的单元格。等同于按键、、或，语法如下：

expression.End(Direction)

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

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

常量 xlDown xlToRight xlToLeft xlUp 值 -4121 -4161 -4159 -4162 描述 向下 向右 向左 向上 表格 3-1 XlDirection 常量

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

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

18

VBA常用技巧代码解析 yuanzhuping

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

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

#001 Sub LastColumn() #002 Dim rng As Range

#003 Set rng = Sheet1.Range(\

#004 MsgBox \第一行中最后一个非空单元格是\#005 & \列号\数值\#006 Set rng = Nothing #007 End Sub

代码解析：

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

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

19

VBA常用技巧代码解析 yuanzhuping

技巧4 定位单元格

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

#001 Sub SpecialAddress() #002 Dim rng As Range

#003 Set rng = Sheet1.UsedRange.SpecialCells(xlCellTypeFormulas) #004 rng.Select

#005 MsgBox \工作表中有公式的单元格为： \#006 Set rng = Nothing #007 End Sub

代码解析：

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

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

expression.SpecialCells(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

VBA常用技巧代码解析 yuanzhuping

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

参数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

VBA常用技巧代码解析 yuanzhuping

#002 Dim StrFind As String #003 Dim Rng As Range

#004 StrFind = InputBox(\请输入要查找的值:\#005 If Trim(StrFind) <> \#006 With Sheet1.Range(\

#007 Set Rng = .Find(What:=StrFind, _

#008 After:=.Cells(.Cells.Count), _ #009 LookIn:=xlValues, _ #010 LookAt:=xlWhole, _ #011 SearchOrder:=xlByRows, _ #012 SearchDirection:=xlNext, _ #013 MatchCase:=False) #014 If Not Rng Is Nothing Then #015 Application.Goto 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，语法如下：

expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SerchFormat)

参数expression是必需的，该表达式返回一个Range对象。 参数What是必需的，要搜索的数据，可为字符串或任意数据类型。

参数After是可选的，表示搜索过程将从其之后开始进行的单元格，必须是区域中的单个单元格。查找时是从该单元格之后开始的，直到本方法绕回到指定的单元格时，才对其进行搜索。如果未指定本参数，搜索将从区域的左上角单元格之后开始。

22

VBA常用技巧代码解析 yuanzhuping

在本例中将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) <> \#007 With Sheet1.Range(\

#008 Set Rng = .Find(What:=StrFind, _

#009 After:=.Cells(.Cells.Count), _ #010 LookIn:=xlValues, _ #011 LookAt:=xlWhole, _ #012 SearchOrder:=xlByRows, _ #013 SearchDirection:=xlNext, _ #014 MatchCase:=False) #015 If Not Rng Is Nothing Then #016 FindAddress = Rng.Address

23

VBA常用技巧代码解析 yuanzhuping

#017 Do

#018 Rng.Interior.ColorIndex = 6 #019 Set Rng = .FindNext(Rng)

#020 Loop While Not Rng Is Nothing And Rng.Address <> 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对象，语法如下：

expression.FindNext(After)

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

参数After是可选的，指定一个单元格，查找将从该单元格之后开始。

第20行代码如果查找到的单元格地址等于字符串变量FindAddress所记录的地址，说明A列已搜索完毕，结束查找过程。

运行RngFindNext过程，在InputBox函数输入框中输入“196.01”后结果如图 5-1所示。

图 5-1 使用FindNext方法重复搜索

还可以使用FindPrevious方法进行重复搜索，FindPrevious方法的语法如下：

24

VBA常用技巧代码解析 yuanzhuping

expression.FindPrevious(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(\

#007 For Each rng In .Range(\#008 If rng.Text Like \#009 .Range(\#010 a = a + 1 #011 End If #012 Next #013 End With #014 End Sub

代码解析：

RngLike过程使用For Each...Next语句和Like运算符在单元格区域B1:E10000中搜索含有“a”字符的单元格，找到匹配单元格以后将单元格的值写入到A列中。

第6行代码使用ClearContents方法清除A列区域的数据。

第7行代码使用For Each...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

VBA常用技巧代码解析 yuanzhuping

参数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(\通州\南通\#003 End Sub

代码解析：

26

VBA常用技巧代码解析 yuanzhuping

RngReplace过程将工作表A1:A5单元格中的“通州”字符串替换成“南通”字符串。 应用于Range对象的Replace方法替换指定区域内单元格中的字符，语法如下：

expression.Replace(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 Application.DisplayAlerts = False

27

VBA常用技巧代码解析 yuanzhuping

#003 Sheet1.Range(\#004 Application.DisplayAlerts = 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所示的消息框提示是否替换单元格内容，可以设置Application.DisplayAlerts属性值为False，使复制时不出现该消息框。

图 7-3 替换对话框

第2行代码通常复制单元格区域的操作不会将单元格区域的列宽大小同时复制，如图 7-2所示。如果希望在复制单元格区域的同时，也复制源区域的列宽大小，可以使用下面的代码。

#001 Sub CopyWithSameColumnWidths()

28

VBA常用技巧代码解析 yuanzhuping

#002 Sheet1.Range(\#003 With Sheet3.Range(\

#004 .PasteSpecial xlPasteColumnWidths #005 .PasteSpecial xlPasteAll #006 End With

#007 Application.CutCopyMode = 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

描述 无（默认值） 加 VBA常用技巧代码解析 yuanzhuping

常量 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 Sheet1.Range(\

#003 Sheet2.Range(\#004 Application.CutCopyMode = False #005 End Sub

30

VBA常用技巧代码解析 yuanzhuping

代码解析：

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 Sheet1.Range(\

#003 Sheet3.Range(\= .Value

#004 End With #005 End Sub

代码解析：

GetValueResize过程将工作表Sheet1中的A1单元格的当前区域的数值赋予工作表Sheet3的A1单元格所在的单元格区域。

在对单元格区域直接赋值时，应保证源区域大小与目标区域的大小一致，如果源区域为动态的单元格区域，可使用Resize方法确定目标区域。

运行GetValueResize过程，赋值结果如图 8-2所示。

31

VBA常用技巧代码解析 yuanzhuping

技巧9 单元格自动进入编辑状态

当光标选择单元格时无需双击，自动进入编辑状态，如下面的代码所示。

#001 Private Sub Worksheet_SelectionChange(ByVal Target As Range) #002 If Target.Column = 3 And Target.Count = 1 Then #003 If Target <> \

#004 Application.SendKeys \#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 Application.Intersect(Target, Range(\Then

#003 Application.CellDragAndDrop = False #004 Else

#005 Application.CellDragAndDrop = True #006 End If

32

VBA常用技巧代码解析 yuanzhuping

#007 End Sub

代码解析：

工作表的SelectionChange事件过程，在指定的单元格区域禁用单元格的拖放功能。 CellDragAndDrop 属性设置单元格的拖放功能，如果允许使用单元格拖放功能，则该值为True。

为了不影响其他工作表，应在工作表的Deactivate事件中恢复单元格的拖放功能，如下面的代码所示。

#001 Private Sub Worksheet_Deactivate() #002 Application.CellDragAndDrop = True #003 End Sub

代码解析：

工作表的Deactivate事件过程，恢复单元格的拖放功能。

工作表的Deactivate事件当工作表从活动状态转为非活动状态时产生，语法如下：

Private Sub object_Deactivate()

参数object代表Worksheet对象。

当选择工作表“Sheet1”的A1：A15单元格时将禁用单元格的拖放功能，如图 10-1所示。

图 10-1 禁用单元格的拖放功能

技巧11 单元格格式操作

11-1

单元格字体格式设置

在VBA中可以对单元格的字体格式进行各种设置，如下面的代码所示。

33

VBA常用技巧代码解析 yuanzhuping

#001 Public Sub RngFont() #002 With Range(\#003 .Name = \华文彩云\#004 .FontStyle = \#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

VBA常用技巧代码解析 yuanzhuping

xlUnderlineStyleSingleAccounting xlUnderlineStyleDoubleAccounting 4 5 会计用单下划线 会计用双下划线 表格 11-1 XlUnderlineStyle常量

运行RngFont过程结果如图 11-2所示。

图 11-2 单元格字体设置

11-2 设置单元格内部格式

设置单元格的Interior属性可以对单元格的内部格式进行设置，如下面的代码所示。

#001 Sub RngInterior()

#002 With Range(\#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

VBA常用技巧代码解析 yuanzhuping

图 11-3 设置单元格内部格式

11-3 为单元格区域添加边框

我们为单元格区域添加边框时往往通过录制宏获取代码，但宏录制器生成的代码分别设置单元格区域的每个边框，因此代码多且效率低。使用Range对象的Borders集合可以快速的对单元格区域的每个边框应用相同的格式，而Range对象的BorderAround方法则可以快速地为单元格区域添加一个外边框，如下面的代码所示。

#001 Sub AddBorders() #002 Dim rng As Range

#003 Set rng = Range(\#004 With rng.Borders

#005 .LineStyle = xlContinuous #006 .Weight = xlThin #007 .ColorIndex = 5 #008 End With

#009 rng.BorderAround 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

VBA常用技巧代码解析 yuanzhuping

置该边框的相关属性，其语法如下：

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 = Sheet2.Range(\#004 With rng.Borders(xlInsideHorizontal) #005 .LineStyle = xlDot #006 .Weight = xlThin #007 .ColorIndex = 5 #008 End With

#009 With rng.Borders(xlInsideVertical) #010 .LineStyle = xlContinuous #011 .Weight = xlThin #012 .ColorIndex = 5 #013 End With

#014 rng.BorderAround xlContinuous, xlMedium, 5 #015 Set rng = Nothing #016 End Sub

代码解析：

37

VBA常用技巧代码解析 yuanzhuping

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(\

#003 .RowHeight = Application.CentimetersToPoints(2) #004 .ColumnWidth = Application.CentimetersToPoints(1.5) #005 End With

#006 With Range(\

#007 .RowHeight = Application.InchesToPoints(1.2)

38

VBA常用技巧代码解析 yuanzhuping

#008 .ColumnWidth = Application.InchesToPoints(0.3) #009 End With #010 End Sub

代码解析：

RngToPoints过程以英寸和厘米计量单位设置单元格的行高列宽。

第3、4行代码使用CentimetersToPoints方法以厘米为计量单位设置A1单元格的行高列宽。CentimetersToPoints方法将计量单位从厘米转换为磅（一磅等于 0.035 厘米），语法如下：

expression.CentimetersToPoints(Centimeters)

参数expression是必需的，返回一个Application对象。 参数Centimeters是必需的，指定要转换为磅值的厘米值。

第5、6行代码使用InchesToPoints方法以英寸为计量单位设置A2单元格的行高列宽。InchesToPoints方法将计量单位从英寸转换为磅，语法如下：

expression.InchesToPoints(Inches)

参数expression是必需的，返回一个Application对象。 参数Inches是必需的，指定要转换为磅值的英寸值。 运行RngToPoints过程结果如图 11-6所示。

图 11-6 灵活设置单元格的行高列宽

技巧12 单元格中的数据有效性

12-1

在单元格中建立数据有效性

在单元格中建立数据有效性可以使用Add方法，如下面的代码所示。

#001 Sub Validation()

39

VBA常用技巧代码解析 yuanzhuping

#002 With Range(\#003 .Delete

#004 .Add Type:=xlValidateList, _ #005 AlertStyle:=xlValidAlertStop, _ #006 Operator:=xlBetween, _ #007 Formula1:=\#008 End With #009 End Sub

代码解析：

使用Add方法在A1：A10单元格中建立数据有效性。 第3行代码删除已建立的数据有效性，防止代码运行出错。

第4行到第7行代码使用Add方法建立数据有效性。应用于Validation对象的Add方法的语法如下：

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

VBA常用技巧代码解析 yuanzhuping

12-2 判断单元格是否存在数据有效性

在VBA中没有专门的属性判断单元格是否存在数据有效性设置，可以使用Validation对象的有效性类型和错误陷阱来判断，如下面的代码所示。

#001 Sub Validation() #002 On Error GoTo Line

#003 If Range(\#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 Target.Column = 1 And Target.Count = 1 And Target.Row > 1 Then #003 With Target.Validation #004 .Delete

#005 .Add Type:=xlValidateList, _ #006 AlertStyle:=xlValidAlertStop, _ #007 Operator:=xlBetween, _ #008 Formula1:=\主机,显示器\#009 End With #010 End If #011 End Sub

41

VBA常用技巧代码解析 yuanzhuping

#012 Private Sub Worksheet_Change(ByVal Target As Range)

#013 If Target.Column = 1 And Target.Row > 1 And Target.Count = 1 Then #014 With Target.Offset(0, 1).Validation #015 .Delete

#016 Select Case Target #017 Case \主机\

#018 .Add Type:=xlValidateList, _ #019 AlertStyle:=xlValidAlertStop, _ #020 Operator:=xlBetween, _

#021 Formula1:=\#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

VBA常用技巧代码解析 yuanzhuping

图 12-1 动态数据有效性1

图 12-2 动态数据有效性2

12-4 自动展开数据有效性下拉列表

选择工作表单元格时自动展开数据有效性的下拉列表，如下面的代码所示。

#001 Private Sub Worksheet_SelectionChange(ByVal Target As Range) #002 If Target.Column = 5 Then Application.SendKeys \#003 End Sub

代码解析：

当选择工作表的E列中有数据有效性的单元格时使用SendKeys方法发送Alt+向下键，打开数据有效性的下拉列表。

应用于Application对象的SendKeys方法将击键发送给活动应用程序，语法如下：

expression.SendKeys(Keys, Wait)

参数expression是可选的，该表达式返回一个Application对象。 参数Keys是必需的，要发送的键或者组合键，以文本方式表示。

Keys参数可以指定任何单个键或与Alt、Ctrl 或Shift的组合键（或者这些键的组合）。每个键可用一个或多个字符表示。例如，\表示字符 a，或者 \表示 Enter。

若要指定在按相应键时不会显示的字符（例如，Enter 或 Tab），请使用如表格 12-2所列的代码来表示相应的键，表中的每个代码表示键盘上的一个键。

键 Backspace Break 代码 {BACKSPACE} 或 {BS} {BREAK} 43

VBA常用技巧代码解析 yuanzhuping

Caps Lock Clear Delete 或 Del End Enter Enter（数字小键盘） Esc F1 到 F15 Help Home Ins Num Lock Page Down Page Up Return Scroll Lock Tab 向上键 向下键 向右键 向左键 {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()

#002 Sheet1.Range(\

44

VBA常用技巧代码解析 yuanzhuping

#003 End Sub

代码解析：

应用于Range对象的Formula属性返回或设置A1样式表示的Range对象的公式，语法如下：

expression.Formula

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

还可以使用FormulaR1C1属性返回或设置以R1C1-样式符号表示的公式，如下面的代码所示。

#001 Sub rngFormulaRC()

#002 Sheet2.Range(\#003 End Sub

如果需要在单元格中写入数组公式则使用Range对象的FormulaArray属性。如下面的代码所示。

#001 Sub RngFormulaArray()

#002 Sheet3.Range(\#003 End Sub

Range对象的FormulaArray属性返回或设置单元格区域的数组公式。

13-2

检查单元格是否含有公式

使用单元格的HasFormula属性检查单元格是否含有公式,如下面的代码所示。

#001 Private Sub CommandButton1_Click() #002 Select Case Selection.HasFormula #003 Case True

#004 MsgBox \公式单元格!\#005 Case False

#006 MsgBox \非公式单元格!\#007 Case Else

#008 MsgBox \公式区域：\& Selection.SpecialCells(xlCellTypeFormulas, 23).Address(0, 0)

#009 End Select #010 End Sub

代码解析：

45

VBA常用技巧代码解析 yuanzhuping

工作表中按钮的单击过程，检查所选择的单元格区域是否含有公式。

第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 VBA.IsError(Range(\#003 MsgBox \单元格错误类型为:\#004 Else

46

VBA常用技巧代码解析 yuanzhuping

#005 MsgBox \单元格公式结果为\#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 = Sheet1.Range(\#004 MsgBox \公式所引用的单元格有：\#005 Set rng = Nothing #006 End Sub

代码解析：

在工作表的C1单元格中写有公式“SUM(“A1:B1”)”，RngPrecedent过程使用Range对象的Precedents属性取得其引用的单元格A1:B1。

Precedents属性返回一个Range对象，该对象代表单元格的所有引用单元格。如果有若干引用单元格，那么该区域可能是多个的选定区域（Range 对象的联合）。

运行RngPrecedent过程结果如图 13-4所示。

47

VBA常用技巧代码解析 yuanzhuping

图 13-4 取得引用单元格

13-5 将单元格中的公式转换为数值

工作表中如果存在过多的公式将影响操作速度，将单元格中的函数与公式的结果转换为数值，可以提高工作表运算效率，有下面几种方法可以实现。

使用选择性粘贴的方法可以将函数与公式的结果转换为数值，如下面的代码所示。

#001 Sub SpecialPaste() #002 With Range(\#003 .Copy

#004 .PasteSpecial Paste:=xlPasteValues #005 End With

#006 Application.CutCopyMode = False #007 End Sub

代码解析：

SpecialPaste过程使用选择性粘贴方法将单元格区域的公式转换为数值。 第3行代码将单元格区域复制到剪贴板中。

应用于Range对象的Copy方法将单元格区域复制到指定的区域或剪贴板中，语法如下：

expression.Copy(Destination)

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

参数Destination是可选的，指定区域要复制到的目标区域。如果省略该参数，Microsoft Excel 将把该区域复制到剪贴板中。

第4行代码将剪贴板中的Range对象仅复制值到单元格区域中。

应用于Range对象的PasteSpecial方法将剪贴板中的Range对象粘贴到指定区域中，语法如下：

expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)

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

参数Paste是可选的，指定要粘贴的区域部分。在本例中设置为xlPasteValues，仅复制值到单元格区域中。

48

VBA常用技巧代码解析 yuanzhuping

使用Value属性可以将函数与公式的结果转换为数值，如下面的代码所示。

#001 Sub UseValue()

#002 Range(\#003 End Sub

代码解析：

UseValue过程使用Value属性将函数与公式的结果转换为数值。

使用Formula属性可以将函数与公式的结果转换为数值，如下面的代码所示。

#001 Sub UseFormula()

#002 Range(\#003 End Sub

代码解析：

UseFormula过程Formula属性将函数与公式的结果转换为数值。当Formula属性值为非公式时，返回的结果与Value属性一致。

技巧14 单元格中的批注

14-1

判断单元格是否存在批注

在VBA中，可以利用Range对象的Comment属性判断单元格是否存在批注，如下面的代码所示。

#001 Sub HasComment()

#002 If Range(\#003 MsgBox \单元格中没有批注!\#004 Else

#005 MsgBox \单元格中批注内容为：\& Chr(13) & Range(\#006 End If #007 End Sub

代码解析：

HasComment过程判断A1单元格是否存在批注，并用消息框显示批注信息。

49

VBA常用技巧代码解析 yuanzhuping

Range对象的Comment属性返回一个批注对象，如果指定的单元格不存在批注，该属性返回Nothing。

运行HasComment过程结果如图 14-1所示。

图 14-1 显示批注内容

14-2 为单元格添加批注

如果希望为单元格添加批注，那么可以使用AddComment方法，如下面的代码所示。

#001 Sub Comment_Add() #002 With Range(\

#003 If .Comment Is Nothing Then #004 .AddComment Text:=.Value #005 .Comment.Visible = True #006 End If #007 End With #008 End Sub

代码解析：

Comment_Add判断单元格A1中是否存在批注，如果没有批注则为单元格A1添加批注并将单元格数值作为批注文本，同时显示批注对象。

第4行代码使用Range对象的AddComment方法为单元格添加批注。该方法只有一个参数Text，代表批注文本。如果单元格已经存在批注，则该方法返回一个错误。

第5行代码显示批注对象，Visible属性确定对象是否可视。 当单元格A1中不存在批注时，运行代码后的结果如图 14-2所示。

50

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