excelvba快速上手之宝典.pdf

E x c e l VBA 快速上手之宝典 E x c e l VBA 快速上手之宝典 zhoujibinzhoujibin 2006 年 7 月 写于 2006 年 7 月 写于ExcelHome论坛论坛 目目 录录 第一章 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 passwordByVal x as integer, byref y as integer as boolean If y100 then yxy else yx-y xx100 if y150 then passwordtrue else passwordfalse End Function Sub call_password Dim x1 as integer Dim y1 as integer x112 y1100 if password then ‘调用函数1. 作为一个表达式放在右端 ; 2. 作为参数使用 debug.print x1 end if End sub 三．Property 属性过程和 Event 事件过程 这是 VB 在对象功能上添加的两个过程，与对象特征密切相关，也是 VBA 比较重要组成，技术比较 复杂，可以参考相关书籍。 第十二节内部函数第十二节内部函数 在 VBA 程序语言中有许多内置函数，可以帮助程序代码设计和减少代码的编写工作。 一．测试函数 IsNumericx ‘是否为数字, 返回 Boolean 结果，True or False IsDatex ‘是否是日期, 返回 Boolean 结果，True or False IsEmpty（x） ‘是否为 Empty, 返回 Boolean 结果，True or False IsArrayx ‘指出变量是否为一个数组。 IsErrorexpression ‘指出表达式是否为一个错误值 IsNullexpression ‘指出表达式是否不包含任何有效数据 Null。 IsObjectidentifier ‘指出标识符是否表示对象变量 二．数学函数 SinX、CosX、TanX、Atanx 三角函数，单位为弧度 Logx 返回 x 的自然对数 Expx返回 ex Absx 返回绝对值 Intnumber、Fixnumber 都返回参数的整数部分，区别Int 将 -8.4 转换成 -9，而 Fix 将-8.4 转换成 -8 Sgnnumber 返回一个 Variant Integer，指出参数的正负号 Sqrnumber 返回一个 Double，指定参数的平方根 VarTypevarname 返回一个 Integer，指出变量的子类型 Rnd（x）返回 0-1 之间的单精度数据，x 为随机种子 三．字符串函数 Trimstring 去掉 string 左右两端空白 Ltrimstring 去掉 string 左端空白 Rtrimstring 去掉 string 右端空白 Lenstring 计算 string 长度 Leftstring, x 取 string 左段 x 个字符组成的字符串 Rightstring, x 取 string 右段 x 个字符组成的字符串 Midstring, start,x 取 string 从 start 位开始的 x 个字符组成的字符串 Ucasestring 转换为大写 Lcasestring 转换为小写 Spacex 返回 x 个空白的字符串 Ascstring 返回一个 integer，代表字符串中首字母的字符代码 Chrcharcode 返回 string，其中包含有与指定的字符代码相关的字符 四．转换函数 CBoolexpression 转换为 Boolean 型 CByteexpression 转换为 Byte 型 CCurexpression 转换为 Currency 型 CDateexpression 转换为 Date 型 CDblexpression 转换为 Double 型 CDecexpression 转换为 Decemal 型 CIntexpression 转换为 Integer 型 CLngexpression 转换为 Long 型 CSngexpression 转换为 Single 型 CStrexpression 转换为 String 型 CVarexpression 转换为 Variant 型 Valstring 转换为数据型 Strnumber 转换为 String 五．时间函数 Now 返回一个 Variant Date，根据计算机系统设置的日期和时间来指定日期和时间。 Date 返回包含系统日期的 Variant Date。 Time 返回一个指明当前系统时间的 Variant Date。 Timer 返回一个 Single，代表从午夜开始到现在经过的秒数。 TimeSerialhour, minute, second 返回一个 Variant Date，包含具有具体时、分、秒的时间。 DateDiffinterval, date1, date2[, firstdayofweek[, firstweekofyear]] 返回 Variant Long 的值，表示两个指定 日期间的时间间隔数目 Secondtime 返回一个 Variant Integer，其值为 0 到 59 之间的整数，表示一分钟之中的某个秒 Minutetime 返回一个 Variant Integer，其值为 0 到 59 之间的整数，表示一小时中的某分钟 Hourtime 返回一个 Variant Integer，其值为 0 到 23 之间的整数，表示一天之中的某一钟点 Daydate 返回一个 Variant Integer，其值为 1 到 31 之间的整数，表示一个月中的某一日 Monthdate 返回一个 Variant Integer，其值为 1 到 12 之间的整数，表示一年中的某月 Yeardate 返回 Variant Integer，包含表示年份的整数。 Weekdaydate, [firstdayofweek] 返回一个 Variant Integer，包含一个整数，代表某个日期是星期几 第二章第二章 Excel VBA 常用对象之使用常用对象之使用 第一节第一节 文件的操作文件的操作 1 Excel 文件 1.1 新建与打开 一.新建 Workbooks.Add 二.打开 Workbooks.Open “路径\“ PWD12345“ 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-90A-Z;97-122a-z 异或结果为可见字符则异或 偶数则把异或结果分成两半各自并反序，增加破解难度 ***********************************加密*************************************** Private Function EncryptPlainStr 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 Lenkey 钥匙字符串正向逐个取字符，用其 Asc 码和待加密字符串各字 符的 Asc 码异或操作 NewStr ““ KeyChar Midkey, j, 1 For i 1 To LenPlainStr 取待加密字符串各字符 Char MidPlainStr, i, 1 AscCode AscChar Xor AscKeyChar 对字符的 Asc 码异或操作 If AscCode 48 Or AscCode 65 Or AscCode 97 Then NewStr NewStr ChrAscCode 异或后的 Asc 码是可见字符的 Asc 码，则把异或结 果转成字符，加入异或结果字符串 Else NewStr NewStr Char 异或后的 Asc 码是不可见字符的 Asc 码， 则把原先 字符加入异或结果字符串 End If Next i PlainStr NewStr Next j If LenPlainStr Mod 2 0 Then 异或结果字符串， 其长度为偶数则分左右两半并各自反 序 Side1 StrReverseLeftPlainStr, LenPlainStr / 2 Side2 StrReverseRightPlainStr, LenPlainStr / 2 PlainStr Side1 Side2 合并左右反序字符串 End If Encrypt PlainStr 生成加密结果字符串 End Function ***********************************解密*************************************** Private Function DecryptPlainStr 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 LenPlainStr Mod 2 0 Then 字符串为偶数长度，则分左右两半并各自反序 Side1 StrReverseLeftPlainStr, LenPlainStr / 2 Side2 StrReverseRightPlainStr, LenPlainStr / 2 PlainStr Side1 Side2 合并左右反序后字符串 End If For j Lenkey To 1 Step -1 反顺序逐个取钥匙字符串各字符，用其 Asc 码和待解密 字符串各字符的 Asc 码异或操作 NewStr ““ KeyChar Midkey, j, 1 For i 1 To LenPlainStr 对字符串每个字符的 Asc 码进行异或 Char MidPlainStr, i, 1 AscCode AscChar Xor AscKeyChar 字符的 Asc 码进行异或 If AscCode 48 Or AscCode 65 Or AscCode 97 Then NewStr NewStr ChrAscCode 异或后的 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 Worksheet1.Range″A1A1000″ TotalValue TotalValue ＋ c.Value Next Averague TotalValue / Worksheet1.Range″A1A1000″.Rows.Count 而下面代码程序比上面例子快得多 AveragueApplication.WorksheetFunction.AverageWorksheets1.Range″A1A1000″ 其它函数如 Count,Counta,Countif,Match,Lookup 等等，都能代替相同功能的 VBA 程序代码，提高程序 的运行速度。 方法 2尽量减少使用对象引用，尤其在循环中 每一个 Excel 对象的属性、方法的调用都需要通过 OLE 接口的一个或多个调用，这些 OLE 调用都是 需要时间的，减少使用对象引用能加快 VBA 代码的运行。例如 1．使用 With 语句。 Workbooks1.Sheets1.Range″A1A1000″.Font.Name″Pay″ Workbooks1.Sheets1.Range″A1A1000″.Font.FontStyle″Bold″ ... 则以下语句比上面的快 With Workbooks1.Sheets1.Range″A1A1000″.Font .Name ″Pay″ .FontStyle ″Bold″ ... End With 2．使用对象变量。 如果你发现一个对象引用被多次使用，则你可以将此对象用 Set 设置为对象变量，以减少对对象 的访问。如 Workbooks1.Sheets1.Range″A1″.Value 100 Workbooks1.Sheets1.Range″A2″.Value 200 则以下代码比上面的要快 Set MySheet Workbooks1.Sheets1 MySheet.Range″A1″.Value 100 MySheet.Range″A2″.Value 200 3．在循环中要尽量减少对象的访问。 For k 1 To 1000 Sheets″Sheet1″.Select Cellsk,1.Value Cells1,1.Value Next k 则以下代码比上面的要快 Set Thue Cells1,1.Value Sheets″Sheet1″.Select For k 1 To 1000 Cellsk,1.Value Thue Next k 方法 3减少对象的激活和选择 如果你的通过录制宏来学习 VBA 的，则你的 VBA 程序里一定充满了对象的激活和选择，例如 WorkbooksXXX.Activate、SheetsXXX.Select、RangeXXX.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.ScreenUpdate True 方法 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.DisplayAlerts True 信息警告开启 关闭信息警告后, 保存文档及关闭需要先保存,在关闭 Workbooks“filename.xls“.Save 文件保存 Workbooks“filename.xls“.Close SaveChangesTrue 文件关闭, 不出现是否要保存的窗口,并保存所有对此工作簿的更改。 Workbooks“BOOK1.XLS“.Close SaveChangesFalse 本示例关闭 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 Sub Mytest1 Dim i As Long Dim s As String i Rnd s ati, “.00“ End Sub 过程 MeasureTime 可以测试一个过程的执行速度，因为一般一个过程执行会很快，所以使 用循环， 执行 n 次（第 8 行设置） ，在第 12 行调用测试的过程，通过循环前的时间（第 9 行）和 循 环 后 的 时 间（第 15 行） ，计算总共执行时间（第 17 行） 。使用这个方法，就可以做一些测试，看哪些方法执行效率 更高。另外，由于 Windows 的多任务特定，测试时最好关闭其他无关程序，以获得较准确的测试结果。 方法 8 注意单元格写法 cells1,1 range“a1“.[a1] cells1,1 符合 EXCEL 结构，最快 range“a1“ 有对象，稍稍慢 [A1] 写的快，运行慢 方法 9 不要直呼其名 a Worksheets1.Name aWorksheets“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 Cellsi, j Next j Next i End Sub Sub Test2 Dim i As Long, j As Long, buf As Long, C As Variant C Range“A1CV10000“ For i 1 To 10000 For j 1 To 100 buf Ci, j Next j Next i End Sub 方法 11 注意函数的类型, 尽量少用 Variant 变量，多用整型变量，如多用整型变量函数。 Chr ChrB Command CurDir Date Dir Error at Hex B 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，它是一种快速上手和能用到实际中解决问 题的工具软件。