VBA常用技巧解析
VBA常用技巧
目录
VBA常用技巧 ------------------------------------------------------------------------------------------------------- 1
第1章 Range（单元格）对象 -------------------------------------------------------------------- 10
技巧1 单元格的引用方法 ---------------------------------------------------------------------- 10
1-1 使用Range属性 ----------------------------------------------------------------------- 10 1-2 使用Cells属性 ------------------------------------------------------------------------ 11 1-3 使用快捷记号 -------------------------------------------------------------------------- 11 1-4 使用Offset属性 ----------------------------------------------------------------------- 12 1-5 使用Resize属性 ----------------------------------------------------------------------- 13 1-6 使用Union方法 ----------------------------------------------------------------------- 14 1-7 使用UsedRange属性 ---------------------------------------------------------------- 14 1-8 使用CurrentRegion属性 ------------------------------------------------------------ 15 技巧2 选定单元格区域的方法---------------------------------------------------------------- 15
2-1 使用Select方法 ----------------------------------------------------------------------- 15 2-2 使用Activate方法 -------------------------------------------------------------------- 16 2-3 使用Goto方法 ------------------------------------------------------------------------- 17 技巧3 获得指定行、列中的最后一个非空单元格 -------------------------------------- 17 技巧4 定位单元格 ------------------------------------------------------------------------------- 20 技巧5 查找单元格 ------------------------------------------------------------------------------- 21
5-1 使用Find方法 ------------------------------------------------------------------------- 21 5-2 使用Like运算符 ---------------------------------------------------------------------- 25 技巧6 替换单元格内字符串 ------------------------------------------------------------------- 26 技巧7 复制单元格区域 ------------------------------------------------------------------------- 27 技巧8 仅复制数值到另一区域---------------------------------------------------------------- 30
8-1 使用选择性粘贴 ----------------------------------------------------------------------- 30 8-2 直接赋值的方法 ----------------------------------------------------------------------- 31 技巧9 单元格自动进入编辑状态 ------------------------------------------------------------ 32 技巧10 禁用单元格拖放功能 ----------------------------------------------------------------- 32 技巧11 单元格格式操作 ----------------------------------------------------------------------- 33
11-1 单元格字体格式设置 --------------------------------------------------------------- 33 11-2 设置单元格内部格式 --------------------------------------------------------------- 35 11-3 为单元格区域添加边框 ------------------------------------------------------------ 36 11-4 灵活设置单元格的行高列宽 ------------------------------------------------------ 38 技巧12 单元格中的数据有效性 -------------------------------------------------------------- 39
12-1 在单元格中建立数据有效性 ------------------------------------------------------ 39 12-2 判断单元格是否存在数据有效性 ------------------------------------------------ 41 12-3 动态的数据有效性 ------------------------------------------------------------------ 41 12-4 自动展开数据有效性下拉列表 --------------------------------------------------- 43 技巧13 单元格中的公式 ----------------------------------------------------------------------- 44
1
VBA常用技巧解析
13-1 在单元格中写入公式 --------------------------------------------------------------- 44 13-2 检查单元格是否含有公式 --------------------------------------------------------- 45 13-3 判断单元格公式是否存在错误 --------------------------------------------------- 46 13-4 取得单元格中公式的引用单元格 ------------------------------------------------ 47 13-5 将单元格中的公式转换为数值 --------------------------------------------------- 48 技巧14 单元格中的批注 ----------------------------------------------------------------------- 49
14-1 判断单元格是否存在批注 --------------------------------------------------------- 49 14-2 为单元格添加批注 ------------------------------------------------------------------ 50 14-3 删除单元格中的批注 --------------------------------------------------------------- 51 技巧15 合并单元格操作 ----------------------------------------------------------------------- 52
15-1 判断单元格区域是否存在合并单元格 ----------------------------------------- 52 15-2 合并单元格时连接每个单元格的文本 ----------------------------------------- 53 15-3 合并内容相同的连续单元格 ------------------------------------------------------ 54 15-4 取消合并单元格时在每个单元格中保留内容 -------------------------------- 56 技巧16 高亮显示单元格区域 ----------------------------------------------------------------- 57 技巧17 双击被保护单元格时不显示提示消息框 ---------------------------------------- 58 技巧18 重新计算工作表指定区域 ----------------------------------------------------------- 60 技巧19 录入数据后单元格自动保护 -------------------------------------------------------- 60 技巧20 工作表事件Target参数的使用方法 ---------------------------------------------- 62
20-1 使用单元格的Address 属性 ------------------------------------------------------ 62 20-2 使用Column属性和Row属性 -------------------------------------------------- 63 20-3 使用Intersect方法 ------------------------------------------------------------------- 63
第2章 Worksheet（工作表）对象 --------------------------------------------------------------- 65
技巧21 引用工作表的方式 -------------------------------------------------------------------- 65
21-1 使用工作表的名称 ------------------------------------------------------------------ 65 21-2 使用工作表的索引号 --------------------------------------------------------------- 65 21-3 使用工作表的代码名称 ------------------------------------------------------------ 66 21-4 使用ActiveSheet属性引用活动工作表 ---------------------------------------- 66 技巧22 选择工作表的方法 -------------------------------------------------------------------- 67 技巧23 遍历工作表的方法 -------------------------------------------------------------------- 68
23-1 使用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常用技巧解析
技巧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常用技巧解析
技巧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常用技巧解析
技巧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