EXCEl高级用户——ExcelVBA快速上手宝典

快 速 上 手 之 宝 典 E x c e l VBA 目 录 第一章 Excel VBA 简明语言之基础 第一节 标识符 第二节 运算符 第三节 数据类型 第四节 变量与常量 第五节 数组使用 第六节 注释和赋值语句 第七节 书写规范 第八节 条件语句 第九节 循环语句 第十节 其他类语句和错误语句处理 第十一节 过程和函数 第十二节 内部函数 第二章 Excel VBA 常用对象之使用 第一节 文件的操作 1 Excel 文件 2 文本文件 3 Access 文件 4 文件其它操作 第二节 工作表操作 1 新建与删除 2 隐藏与显示 3 锁定与保护 第三节 单元格操作 1 如何引用单元格和区域 2 如何处理单元格和区域 3 单元格和区域的定位 4 单元格和区域的保护与锁定 第四节 图表的操作 1 新建及类型 2 设置图表的数据 3 图表格式设置 4 散点图增加系列和文字 5 实例 第三章 Excel VBA 高级使用 第一节 Win API 的使用 1 堀明 API 函数 2 使用 API 函数 3 堀明补充说明 4 实例 第二节 Excel VBA 程序的保密 1 使用动态连接库 DLL 2 获得硬盘物理地址 3 加密与注册 第四章 Excel VBA 优化及结束语 第一节 Excel VBA 优化 第二节 结束语 附录 I Excel VBA 对象框架图 第一章 VBA 语言基础 第一节 标识符 一 定义 标识符是一种标识变量 常量 过程 函数 类等语言构成单位的符号 利用它可以完成对变量 常 量 过程 函数 类等的引用 二 命名规则 1 字母打头 由字母 数字和下划线组成 如 A987b 23Abc 2 字符长度小于 40 Excel2002 以上中文版等 可以用汉字且长度可达 254 个字符 3 不能与 VB 保留关键字重名 如 public private dim goto next with integer single 等 第二节 运算符 定义 运算符是代表 VB 某种运算功能的符号 1 赋值 运算符 2 数学运算符 2 过程 名 参数 1 参数 2 debug print x1 y1 结果是 12 112 y1 按地址传递改变了值 而 x1 按值传递 未改变原值 End sub 二 Function 函数 函数实际是实现一种映射 它通过一定的映射规则 完成运算并返回结果 参数传递也两种 按值传 递 ByVal 和按地址传递 ByRef 如下例 Function password ByVal x as integer byref y as integer as boolean If y 100 then y x y else y x y x x 100 if y 150 then password true else password false End Function Sub call password Dim x1 as integer Dim y1 as integer x1 12 y1 100 if password then 调用函数 1 作为一个表达式放 在 右端 2 作为参数使用 debug print x1 end if End sub 三 Property 属性过程和 Event 事件过程 这是 VB 在对象功能上添加的两个过程 与对象特征密切相关 也是 VBA 比较重要组成 技术比较 复杂 可以参考相关书籍 第十二节内部函数 在 VBA 程序语言中有许多内置函数 可以帮助程序代码设计和减少代码的编写工作 一 测试函数 IsNumeric x 是否为数字 返回 Boolean 结果 True or False IsDate x 是否是日期 返回 Boolean 结果 True or False IsEmpty x 是否为 Empty 返回 Boolean 结果 True or False IsArray x 指出变量是否为一个数组 IsError expression 指出表达式是否为一个错误值 IsNu ll expression 指出表达式是否不包含任何有效数据 Null IsObject identifier 指出标识符是否表示对象变量 二 数学函数 Sin X Cos X Tan X Atan x 三角函数 单位为弧度 Log x 返回 x 的自然对数 Exp x 返回 ex Abs x 返回绝对值 Int number Fix number 都返回参数的整数部分 区别 Int 将 8 4 转换成 9 而 Fix 将 8 4 转换成 8 Sgn number 返回一个 Variant Integer 指出参数的正负号 Sqr number 返回一个 Double 指定参数的平方根 VarType varname 返回一个 Integer 指出变量的子类型 Rnd x 返回 0 1 之间的单精度数据 x 为随机种子 三 字符串函数 Trim string 去掉 string 左右两端空白 Ltrim string 去掉 string 左端空白 Rtrim string 去掉 string 右端空白 Len string 计算 string 长度 Left string x 取 string 左段 x 个字符组成的字符串 Right string x 取 string 右段 x 个字符组成的字符串 Mid string start x 取 string 从 start 位开始的 x 个字符组成的字符串 Ucase string 转换为大写 Lcase string 转换为小写 Space x 返回 x 个空白的字符串 As c string 返回一个 integer 代表字符串中首字母的字符代码 Chr charcode 返回 string 其中包含有与指定的字符代码相关的字符 四 转换函数 CBool expression 转换为 Boolean 型 CByte expression 转换为 Byte 型 CCur expression 转换为 Currency 型 CDate expression 转换为 Date 型 CDbl expression 转换为 Double 型 CDec expression 转换为 Decemal 型 CInt expression 转换为 Integer 型 CLng expression 转换为 Long 型 CSng expression 转换为 Single 型 CStr expression 转换为 String 型 CVar expression 转换为 Variant 型 Va l string 转换为数据型 Str num ber 转换为 String 五 时间函数 Now 返回一个 Variant Date 根据计算机系统设置的日期和时间来指定日期和时间 Date 返回包含系统日期的 Variant Date Time 返回一个指明当前系统时间的 Variant Date Timer 返回一个 Single 代表从午夜开始到现在经过的秒数 TimeSerial hour minute second 返回一个 Variant Date 包含具有具体时 分 秒的时间 DateDiff interval date1 date2 firstdayofweek firstweekofyear 返回 Variant Long 的值 表示两个指定 日期间的时 间间隔数目 Second time 返回一个 Variant Integer 其值为 0 到 59 之间的整数 表示一分钟之中的某个秒 Minute time 返回一个 Variant Integer 其值为 0 到 59 之间的整数 表示一小时中的某分钟 Hour time 返回一个 Variant Integer 其值为 0 到 23 之间的整数 表示一天之中的某一钟点 Day date 返回一个 Variant Integer 其值为 1 到 31 之间的整数 表示一个月中的某一日 Month date 返回一个 Variant Integer 其值为 1 到 12 之间的整数 表示一年中的某月 Year date 返回 Variant Integer 包含表示年份的整数 Weekday date firstdayofweek 返回一个 Variant Integer 包含一个整数 代表某个日期是星期几 第二章 Excel VBA 常用对象之使用 第一节 文件的操作 1 Excel 文件 1 1 新建与打开 一 新建 Workbooks Add 二 打开 Workbooks Open 路径 PWD 1234 5 4 打开表或建立表的子表 A 建立 表的子表 Set rs db OpenRecordset select from 表名 B 打开表 Set RS DB OpenRecordset 表名 dbOpenDynaset Opendatabase 方法是打开数据库并返回此数据库的 database 对象 其语法如下 Set database 的对象变量 opendatabase 路径及数据库名礀 除外性 只读 除外性 由 true 和 false 值所构成 当值为 true 时代表仅允许唯一的使用者使用数据库 只读 由 true 和 false 值所 构成 为 true 代表数据库仅提供读取的服务 Openrecordset 方法用来创建一个新的 recordset 对象 语法为 Set recordset 对象变量 数据库变量 openrecordset 来源 种类 recordset 种类有 5 种 分别为 表 table 动态 集 dynaset 快照集 snapshot 动态 dynamic 正 向 forward only 其中常用的时动态集 dynaset 实际上是引用一个或多个表中数据记录的集合 是功 能最强的数据记录集合类型 也是默认值 5 操作数据库记录 对记录的操作就是使用记录集的对象方法和属性来实现 特附录常用属性和方法如下 记录集对象的属性和方法 rs Recordcount 属性 用来记录目前数据记录的数量 如判断数据库是否为空 rs EOF 属性 是否是记录的尾 rs BOF 属性 是否是记录的头 rs Nomatch 属性 返回上次查找成 功与否 rs Move n 方法 移动到第 n 条记录 rs Movenext 方法 移动到下一条记录 rs MovePrevious 方法 移动到上一条记录 rs Movefirst 方法 移动到第一条记录 rs Lastfirst 方法 移动到最后一条记录 rs Delete 方法 删除当前记录 rs Edit 方法 修 改当前记录 步骤为三步 1 用 edit 方法设置为修改状态 2 将数据分别赋到记录的各字 段 3 用 Updata 方法 把记 录更新到数据库中 rs AddNew 方法 添加记录 添加记录分三步 1 用 AddNew 方法添加一个新的空白记录 2 将数 据分别赋到记录的各字段 3 用 Updata 方法 把记录更新到数据库中去 rs Updata 方法 更新内容到数据库中 rs Findfirst 字段名 65 90 A Z 97 122 a z 异或结果为可见字符则异或 偶数则把异或结果分成两半各自并反序 增加破解难度 加密 Private Function Encrypt PlainStr As String key As String as string Dim Char As String KeyChar As String NewStr As String AscCode As Long Dim i As Integer j As Integer Side1 As String Side2 As String For j 1 To Len key 钥匙字符串正向逐个取字符 用其 Asc 码和待加密字符串各字 符的 Asc 码异或 操作 NewStr KeyChar Mid key j 1 For i 1 To Len PlainStr 取待加密字符串各字符 Char Mid PlainStr i 1 AscCode Asc Char Xor Asc KeyChar 对字符的 Asc 码异或操作 If AscCode 48 Or AscCode 65 Or AscCode 97 Then NewStr NewStr Chr AscCode 异或后的 Asc 码是可见字符的 Asc 码 则把异或结 果转成字符 加入异或结果字符串 Else NewStr NewStr Char 异或后的 Asc 码是不可见字符的 Asc 码 则把原先 字符加入异或结果字符串 End If Next i PlainStr NewStr Next j If Len PlainStr Mod 2 0 Then 异或结果字符串 其长度为偶数则分左右两半并各自反 序 Side1 StrReverse Left PlainStr Len PlainStr 2 Side2 StrReverse Right PlainStr Len PlainStr 2 PlainStr Side1 Side2 合并左右反序字符串 End If Encrypt PlainStr 生成加密结果字符串 End Function 解密 Private Function Decrypt PlainStr As String key As String as string Dim Char As String KeyChar As String NewStr As String AscCode As Long Dim i As Integer j As Integer Side1 As String Side2 As String If Len PlainStr Mod 2 0 Then 字符串为偶数长度 则分左右两半并各自反序 Side1 StrReverse Left PlainStr Len PlainStr 2 Side2 StrReverse Right PlainStr Len PlainStr 2 PlainStr Side1 Side2 合并左右反序后字符串 End If For j Len key To 1 Step 1 反顺序逐个取钥匙字符串各字符 用其 Asc 码和待解密 字符串各字符的 Asc 码异或操作 NewStr KeyChar Mid key j 1 For i 1 To Len PlainStr 对字符串每个字符的 Asc 码进行异或 Char Mid PlainStr i 1 AscCode Asc Char Xor Asc KeyChar 字符的 Asc 码进行异或 If AscCode 48 Or AscCode 65 Or AscCode 97 Then NewStr NewStr Chr AscCode 异或后的 Asc 码是可见字符的 Asc 码 则把异或结 果转成字符 加入异或结果字符串 Else NewStr NewStr Char 异或后的 Asc 码是不可见字符的 Asc 码 则把原先 字符加入异或结果字符串 End If Next i PlainStr NewStr Next j Decrypt PlainStr End Function 第四章 Excel VBA 优化及结束语 第一节 Excel VBA 优化 由于 Microsoft Office 办公套件的广泛应用 以及该软件版本的不断提升 功能不断完善 在 Office 办公套件 平匀上开发出的的 VBA 应用程序越来越多 而 VBA 是一种宏语言 在运行速度上有很大的限制 因此 VBA 编 程的方法直接关系到 VBA 程序运行的效率 本节列举了一些提高 VBA 程序运行效率的方法 方法 1 尽量使用 VBA 原有的属性 方法和 Worksheet 函数 由于 Excel 对象多达百多个 对象的属性 方法 事件多不胜数 对于初学者来说可能对它们 不全部 了解 这就产生了编程者经常编写与 Excel 对象的属性 方法相同功能的 VBA 代码段 而这些代码段的运 行效 率显然与 Excel 对象的属性 方法完成任务的速度相差甚大 例如用 Range 的属性 CurrentRegion 来返 回 Range 对象 该对象代表当前区 当前区指以任意空白行及空白列的组合为边界的区域 同样功能的 VBA 代码需 数十行 因此编程前应尽可能多地了解 Excel 对象的属性 方法 充分利用 Worksheet 函数是提高程序运行速度的极度有效的方法 如求平均工资的例子 For Each c In Worksheet 1 Range A1 A1000 TotalV alue TotalV alue c Value Next AverageValue TotalValue Worksheet 1 Range A1 A1000 Rows Count 而下面代码程序比上面例子快得多 AverageValue Application WorksheetFunction Average Worksheets 1 Range A1 A1000 其它函数如 Count Counta Countif Match Lookup 等等 都能代替相同功能的 VBA 程序代码 提高程序 的运行速度 方法 2 尽量减少使用对象引用 尤其在循环中 每一个 Excel 对象的属性 方法的调用都需要通过 OLE 接口的一个或多个调用 这些 OLE 调用都是 需要时间的 减少使用对象引用能加快 VBA 代码的运行 例如 1 使用 With 语句 Workbooks 1 Sheets 1 Range A1 A1000 Font Name Pay Workbooks 1 Sheets 1 Range A1 A1000 Font FontStyle Bold 则以下语句比上面 的快 With Workbooks 1 Sheets 1 Range A1 A1000 Font Name Pay FontStyle Bold End With 2 使用对象变量 如果你发现一个对象引用被多次使用 则你可以将此对象用 Set 设置为对象变量 以减少对对象 的访问 如 Workbooks 1 Sheets 1 Range A1 Value 100 Workbooks 1 Sheets 1 Range A2 Value 200 则以下代码比上面的要快 Set MySheet Workbooks 1 Sheets 1 MySheet Range A1 Value 100 MySheet Range A2 Value 200 3 在循环中要尽量减少对象的访问 For k 1 To 1000 Sheets Sheet1 Select Cells k 1 Value Cells 1 1 Value Next k 则以下代码比上面的要快 Set TheValue Cells 1 1 Value Sheets Sheet1 Select For k 1 To 1000 Cells k 1 Value TheValue Next k 方法 3 减少对象的激活和选择 如果你的通过录制宏来学习 VBA 的 则你的 VBA 程序里一定充满了对象的激活和选择 例如 Workbooks XXX Activate Sheets XXX Select Range XXX Select 等 但事实上大多数情况下这些操作不 是必需 的 例如 Sheets Sheet3 Select Range A1 Value 100 Range A2 Value 200 可改为 With Sheets Sheet3 Range A1 Value 100 Range A2 Value 200 End With 方法 4 关闭屏幕更新 如果你的 VBA 程序前面三条做得比较差 则关闭屏幕更新是提高 VBA 程序运行速度的最有效的方法 缩短运行时间 2 3 左右 关闭屏幕更新的方法 Application ScreenUpdate False 请不要忘记 VBA 程序运行结束时再将该值设回来 Application ScreenU pdate Tr ue 方法 5 变量类型确定 少用变体变量 Option Explicit 语句 在模块级别中使用 强制显式堀明模块中的所有变量 如果模块中使用了 Option Explicit 则必须使用 Dim Private Public ReDim 或 Static 语句来显式堀明所有的变量 如果 使用 了未堀明的变量名在编译时间会出现错误 如果没有使用 Option Explicit 语句 一般所有未堀明的变 量 都是 Variant 类型的 注意 使用 Option Explicit 可以避免在键入已有变量时出错 在变量的范围不是很清楚的代码中使用 该语句可以避免混乱 方法 6 关闭 Excel 系统提示 本示例关闭所有打开的工作簿 如果某个打开的工作簿有改变 Microsoft Excel 将显示询问是否保存 更改的对话框和相应提示 Workbooks Close 实际开发程序时 需要关闭提示信息对话框 给用户简洁 高效的体验 Application DisplayAlerts False 信息警告关闭 请不要忘 记 VBA 程序运行结束时再将该值设回来 Application DisplayA lerts Tr ue 信息警告开 启 关闭信息警告后 保存文档及关闭需要先保存 在关闭 Workbooks filename xls Save 文件保存 Workbooks filename xls Close SaveChanges True 文件关闭 不出现是否 要保存的窗口 并保存所有对此工作簿的更改 Workbooks BOOK1 XLS Close SaveChanges False 本示例关闭 Book1 xls 并放弃所有对此工作簿的 更改 这样可以提 高程序的简洁性 给用户服务 方法 7 提高关键代码和循环代码的效率 不同方法执行效率的差异 但千万不要因为追求效率而损失了代码的可读性 清晰性 效率的优化必 须是针对关键代码的优化 对于一些在程序执行过程中 只执行很少次数的代码 没有必要牺牲可读性而 进行 优化 对于代码执行效率 千万不要人云亦云 必要时候 自己动手测试一下 结果往往会出 乎 意料 代码执行时间的测算 VBA 和 VB 中 没有专门的代码执行事件测算工具和方法 笔者一般是使用 Timer 函数 其返回值是一个 Single 类型的数值 代表从午夜开始到现在经过的秒数 此数值包括小数部分 但 精确程度在 Windows NT 2000 和 XP 下大概接近 10 毫秒 如果要测试一段代码的执行速度 可以使用如 下方法 Sub MeasureTime Dim Time1 As Single Time2 As Single Dim TotalTime As Single Dim Times As Long Dim i As Long Times 10000 Time1 Timer For i 1 To Times Step 1 Mytest1 Next i Time2 Timer TotalTime Time2 Time1 1000 MsgBox 执行时间 TotalTime 毫秒 次数 Times End Sub SubMy test1 Dim i As Long Dim s As String i Rnd s Format i 00 End Sub 过程 MeasureTime 可以测试一个过程的执行速度 因为一般一个过程执行会很快 所以使 用循环 执行 n 次 第 8 行设置 在第 12 行调用测试的过程 通过循环前的时间 第 9 行 和 循环后的时 间 第 15 行 计算总 共执行时间 第 17 行 使用这个方法 就可以做一些测试 看哪些方法执行效率 更高 另外 由于 Windows 的多 任务特定 测试时最好关闭其他无关程序 以获得较准确的测试结果 方法 8 注意单元格写法 cells 1 1 range a1 a1 cells 1 1 符合 EXCEL 结构 最快 range a1 有对象 稍稍慢 A1 写的快 运行慢 方法 9 不要直呼其名 a Worksheets 1 Name a Worksheets Sheet1 name 方法 10 少用 RANGE 对象 可用数组取代 速度快 5 10 倍 Test2 就比 Test1 快 Sub Test1 Dim i As Long j As Long buf As Long For i 1 To 10000 For j 1 To 100 buf Cells i j N ext j Next i End Sub Sub Test2 Dim i As Long j As Long buf As Long C As Variant C Range A1 CV10000 For i 1 To 10000 For j 1 To 100 buf C i j N ext j Next i End Sub 方法 11 注意函数的类型 尽量少用 Variant 变量 多用整型变量 如多用整型变量函数 Chr ChrB Command CurDir Date Dir Error Format Hex Input InputB LCase LeftB LTrim Mid MidB Oct Right RightB RTrim Space Str String Time Trim UCase 这些字符型函数 就比 chr date space 等快 因为不加后缀类型指定的函数 其返回值是 Variant 类型结 果 第二节 结束语 经过一礼拜的疲劳 没功劳有苦劳 没苦劳总有疲劳 套一句 疯狂石头 的电影对白 我总算结 束了 开始的计划 也算是有头有尾 稍感欣慰 这期间 我要感谢各位 VBA 爱好者的鼓励和支持 你们 的坚持浏览让 我有信心继续写下去 让我感到我写的还有点用 不是瞎写 乱写的 无乱灌水之嫌疑 我对程序开发 以前学过很多东西 可以说是陪着一些计算机开发语言发展走过一段时间的 记得 96 年大学时 我们学汇编语言 那时觉得像天文 觉得难 就学习 Foxbase 数据库 记得考计算机二级时 我上机 得了满分 一下子有信心了 就转而学习 VB5 但学来学去 就是没法突破 后来硕士期间 我学 会了 VB6 解决 了一个实际问题 这下对 VB6 开发及程序开发 有了更深刻的理解 但还是觉得 VB6 没 能有好的办法解决计算机 复杂的问题 比如矢量图的绘制和保存 我就一直搞不会 后来东看西看 学了 很多计算机东西 读博士期间 用 Excel 解决数据计算和作图 由于是要重复作类似的 所以学下了 VBA 突然发现 Excel VBA 是很适合我这种使 用者 需要快速解决工作学习的实际问题 由此我也开发了世界一 流的地化数据投图软件 Geoplot 关于它的论文发表在 Computers Geosciences 上 在开发这个软件期 间 学了很多东西 这些核心的东西我都在这个论坛发过贴了 所以 我隆重推荐大家学习 VBA 由于 它是入 门 学习 进阶都很快速 很容易上手的 是解决实际工作的最佳工具 信不信由你 对于其它开发语 言 我认为目前的开发语言越来越一致了 都是以对象为核心 比如 VB NET VC NET 等 所以学会一 种 VB NET 就可以解决其它开发问题 但普通用户来讲 是不需要学会和精通那些的 只要知道有它们 的 存在 且找一本书学一下 如果真的有机会 有事情需要用到那些 再学习 因为大家都很忙 所以建议 学习 VBA 这种快速解决问题的工具 看我的内容 估计一个礼拜就学会了 再看看论坛上的贴子 我想 大家一个月就能 成为 VBA 中级人员 程序开发 其实还需要一定天赋的 因为核心的算法是要靠一定智 慧的 不同的人 搞出来的 虽然都解决问题 但速度差异很大等 想到那就写到那 胡言乱语 宗旨就是建议大家学些 VBA 它是一种快速上手和能用到实际中解决问 题的工具软件