（试题 试卷 真题）Excel 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）数学运算符 &、 + (字符连接符 )、 +(加)、-（减）、 Mod（取余）、 （整除）、*（乘）、/（除）、-（负号）、（指数） 3）逻辑运算符 Not（非）、 And（与）、 Or（或）、 Xor（异或）、 Eqv（相等）、 Imp（隐含） 4）关系运算符 = （相同）、 （不等）、（大于）、=（不小于）、 B And C250 Then x=x-100 或者，可以使用块形式的语法： If condition Then statements ElseIf condition-n Then elseifstatements . Else elsestatements End If 如 1: If Number 10 Then Digits = 1 ElseIf Number 0 then Gosub Routine1 ：Debug.print num：Exit sub Routine1: Num=num/5 Return End sub 4) whilewend语句，只要条件为TRUE，循环就执行，这是以前 VB老语法保留下来的，如下例： while condition while I50 statements I=I+1 wend Wend 二错误语句处理 执行阶段有时会有错误的情况发生，利用On Error语句来处理错误，启动一个错误的处理程序。语法如下： On Error Goto Line 当错误发生时，会立刻转移到line行去 On Error Resume Next 当错误发生时，会立刻转移到发生错误的下一行去 On Erro Goto 0 当错误发生时，会立刻停止过程中任何错误处理过程 第十一节 过程和函数 过程是构成程序的一个模块，往往用来完成一个相对独立的功能。过程可以使程序更清晰、更具结构 性。VBA具有四种过程：Sub 过程、 Function函数、 Property属性过程和 Event事件过程。 一Sub过程 Sub 过程的参数有两种传递方式：按值传递(ByVal)和按地址传递 (ByRef)。如下例： Sub password (ByVal x as integer, ByRef y as integer) If y=100 then y=x+y else y=x-y x=x+100 End sub Sub call_password () Dim x1 as integer Dim y1 as integer x1=12 y1=100 Call password (x1,y1) 调用过程方式：1. Call 过程名 (参数 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 路径 & 文件名 .xls 三.文件打开对话框的使用 Application.GetOpenFilename(fileFilter:=Excel files(*.xls),*.xls,All files (*.*),*.*) 示例 : flag = False Do While Not flag 对话框打开已有 Excel文件 fm = Application.GetOpenFilename(fileFilter:= Excel files(*.xls),*.xls, All files (*.*),*.*) If fm False Then Workbooks.Open fm Set bb = ActiveWorkbook 把 bb变量设为当前活动工作簿对象,打开一工作簿时,该工作簿自动为当前活动工作簿 flag = True End If Loop 1.2 保存与关闭 一.保存 Workbooks(文件.xls).Save 二.另存对话框的使用 Application.GetSaveAsFilename(fileFilter:=Excel files (*.xls),*.xls,All files (*.*),*.*) 示例 : flag = False Do While Not flag 循环要求必须输入文件名或选择文件名 fm = Application.GetSaveAsFilename(fileFilter:= Excel files (*.xls),*.xls,All files (*.*),*.*) If fm False Then activeworkbook.SaveAs fm 当前活动工作簿另存 flag = True End If Loop 三.关闭 1.单个文件关闭 Workbooks(filename.xls).Close 2.所有该 Excel程序打开的文件都关闭 Workbooks.Close 如果文件使用中改动过内容 , 那么该命令使用时会弹出提示对话框 , 询问是否保存 . 如果要想不弹出提示对话框,可以使用第三或第四种文件关闭方法 , 或者如下示例 . 示例 : 关闭工作簿“ Book1.xls”，但不提示用户保存所作修订。“ Book1.xls”中的所有修订都不 会保存。 Application.DisplayAlerts = False 信息警告关闭 Workbooks(BOOK1.XLS).Close Application.DisplayAlerts = True 信息警告开启 四.关闭同时保存 Workbooks(filename.xls).Close savechanges:=True 五.关闭同时不保存 Workbooks(filename.xls).Close savechanges:=False 六 .Excel程序的关闭 Application.Quit 说明 : 使用本方法时，如果有未保存的工作簿处于打开状态，则 Microsoft Excel 将弹出一个对话 框，询问是否要保存所作修改。 为防止这一情况出现，可在使用 Quit 方法前保存所有的工作簿或将 DisplayAlerts 属性设 置为 False。如果该属性为 False，则 Microsoft Excel 退出时，即使存在未保存的工作簿 退出，也不会显示对话框，而且不保存就退出。如果将一个工作簿的 Saved 属性设置为 Tr ue，但是并没有将其保存到磁盘上，则 Microsoft Excel 在退出时不会提示保存该工作簿。 七.工作簿的密码保护与撤销 保护工作簿使其不至被修改。 Workbooks(“工作簿名 ”).Protect (Password, Structure, Windows) Password Variant 类型，可选。为一个字符串，该字符串为工作表或工作簿指定区分大小写的密码。如 果省略本参数，不用密码就可以取消对该工作表或工作簿的保护。否则，必须指定密码，通过密码来取消 对该工作表或工作簿的保护。如果忘记了密码，就无法取消对该工作表或工作簿的保护。最好在安全的地 方保存一份密码及其对应文档名的列表。 Structure Variant 类型，可选。如果为 True，则保护工作簿结构（工作表的相对位置）。默认值为 False。 Windows Variant 类型，可选。如果为 True，则保护工作簿窗口。如果省略本参数，则窗口不受保护 如：Workbooks(“学生档案”).protect ”1234” Workbooks(“学生档案”).Unprotect (Password) Password Variant 类型，可选。指定用于解除工作表或工作簿的保护的密码，此密码是区分大小写的。 如果工作表或工作簿不设密码保护则忽略本参数。如果对工作表省略此参数，而该工作表又设有密码保护， Microsoft Excel 将提示您要输入密码。如果对工作簿省略此参数，而该工作簿又设有密码保护，本方法将 失败。 1.3 示例 示例 :(使用了4个文件,宏程序文件操作了另外三个文件,还涉及到文件打开另存对话框) 一. 在 test.xls文件中编写一个宏程序test,完成以下内容.打开当前目录下 test1.xls 文件,计算 sheet1工作表上单元格 a1到a10的数据剩上 (0-1)的随机数,计算结果保存在一个新 建工作簿的 sheet1的 a1到a10上,且结果还要保存在一个任意打开的 Excel文件下,示例中打开 test2.xls文件, 完成后关闭Excel程序. test.xls 模块中 Sub test() Dim i As Integer, flag As Boolean, fm Dim aa, bb, cc, temp Application.ScreenUpdating = False 屏幕刷新关闭 Application.DisplayAlerts = False 信息警告关闭 Workbooks.Open ThisWorkbook.Path & test1.xls 打开文件 test1.xls Set aa = ActiveWorkbook.Sheets(Sheet1) flag = False Do While Not flag 对话框打开已有 Excel文件 fm = Application.GetOpenFilename(fileFilter:=Excel files (*.xls),*.xls, _ All files (*.*),*.*) If fm False Then Workbooks.Open fm Set bb = ActiveWorkbook flag = True End If Loop Workbooks.Add Set cc = ActiveWorkbook With cc.Sheets(Sheet1) For i = 1 To 10 temp = aa.Cells(i, 1) * Int(10 * Rnd) + 1) 生成 1 到 10 之间的随机数值 .Cells(i, 1) = temp bb.Sheets(1).Cells(i, 1) = temp Next End With flag = False Do While Not flag 循环要求必须输入文件名或选择文件名 fm = Application.GetSaveAsFile name(fileFilter:=Excel files (*.xls),*.xl _ s,All files (*.*),*.*) If fm False Then cc.SaveAs fm flag = True End If Loop bb.Save 保存 Set aa = Nothing: Set bb = Nothing: Set cc = Nothing 设置对象变量为空 Application.Quit 关闭Excel Application.ScreenUpdating = True 屏幕刷新关闭 Application.DisplayAlerts = True 信息警告开启 End Sub 2) 文本文件 2.1 打开与新建 Open 语句 能够对文件输入/输出 (I/O)。 语法 Open pathname For mode Access access lock As #filenumber Len=reclength Open 语句的语法具有以下几个部分： 部分描輀 pathname必要。字符串表达式，指定文件名，该文件名可能还包括目录、文件夹及驱动器。 mode必要。关键字，指定文件方式，有 Append、 Binary、 Input、 Output、或 Random 方 式。如果未指定方式，则以 Random 访问方式打开文件。 access可选。关键字，说明打开的文件可以进行的操作，有 Read、Write、或 Read Write 操 作。 lock可选。关键字，说明限定于其它进程打开的文件的操作，有 Shared、Lock Read、Lock Write、和 Lock Read Write 操作。 filenumber必要。一个有效的文件号，范围在 1 到 511 之间。使用 FreeFile 函数可得到下一个 可用的文件号。 reclength可选。小于或等于 32，767（字节）的一个数。对于用随机访问方式打开的文件，该值 就是记录长度。对于顺序文件，该值就是缓冲字符数。 说明 对文件做任何 I/O 操作之前都必须先打开文件。 Open 语句分配一个缓冲区供文件进行 I/O 之用， 并决定缓冲区所使用的访问方式。 新建 如果 pathname 指定的文件不存在，那么，在用 Append、 Binary、 Output、或 Random 方 式打开文件时，可以建立这一文件。 如果文件已由其它进程打开，而且不允许指定的访问类型，则 Open 操作失败，而且会有错误发生。 如果 mode 是 Binary 方式，则 Len 子句会被忽略掉。 重要 在 Binary、 Input 和 Random 方式下可以用不同的文件号打开同一文件，而不必先将该文件关闭。 在 Append 和 Output 方式下，如果要用不同的文件号打开同一文件，则必须在打开文件之前先关闭该 文件。 2.2 读入与写出 2.2.1读入 Input #filenumber, varlist 从已打开的顺序文件中读出数据并将数据指定给变量 Get #filenumber, recnumber, varname 将一个已打开的磁盘文件读入一个变量之中 2.2.2写入 Write #filenumber, outputlist 将数据写入顺序文件,以双引号数据逗号,分隔数据 Print #filenumber, outputlist 将格式化显示的数据写入顺序文件中 Put #filenumber, recnumber, varname 将一个变量的数据写入磁盘文件中。 2.3关闭 Close filenumberlist 关闭 Open 语句所打开的输入/输出 (I/O) 文件 注意 : 如果今后想用 Input # 语句读出文件的数据，就要用 Write # 语句而不用 Print # 语句将数据写入文件。 因为在使用 Write # 时，将数据域分界就可确保每个数据域的完整性，因此可用 Input # 再将数据读出来。 使用 Write # 还能确保任何地区的数据都被正确读出。Write 与 Print # 语句不同，当要将数据写入文件 时，Write # 语句会在项目和用来标记字符串的引号之间插入逗号。Write # 语句在将 outputlist 中的最后 一个字符写入文件后会插入一个新行字符，即回车换行符，(Chr(13) + Chr(10) 2.4其他文件函数 LOF(filenumber) 返回一个 Long，表示用 Open 语句打开的文件的大小，该大小以字节为 单位。 EOF(filenumber) 返回一个 Integer，它包含 Boolean 值 True，表明已经到达为 Random 或顺序 Input 打开的文件的结尾。 Loc(filenumber) 返回一个 Long，在已打开的文件中指定当前读/写位置 Seek(filenumber) 返回一个 Long，在 Open 语句打开的文件中指定当前的读/写位置 2.5 示例 要求: 打开一文本文件 test1.txt, 已知其内容为空格分隔 , 要求把其中每行首个数据写入Excel 宏程序文件 的表1中, 再把数据写入新建文件 test2.ini中 Sub test() Dim Fm, i As Long, j As Long, k As Long Dim TT, T1 On Error Resume Next Fm = Application.GetOpenFilename(Text Files (*.txt), *.txt) If Fm = False Then Exit Sub 取消选择文件则退出 k = FreeFile Open Fm For Input As #k 以随机只读的方式打开文件 f2 = FreeFile Open c:test2.ini For Output As #f2 以随机方式新建一个不存在的文件 j = 1 W ithWo rksheets(sheet1) Do While Not EOF(k) 循环读至文件最后一行 Line Input #k, TT 读入一行数据并将其赋予某变量 T1 = Split(TT) 以文本中空格来分开这个字符串并赋值给变量,请参考 split函数帮助 .Cells(j, 1) = T1(0) Print #f2, T1(0) j = j + 1 Loop EndW ith Close #k 关闭文件 Close #f2 关闭文件 End Sub 3) Access文件 使用 VBA来访问数据库，其实就是通过一定方法借助数据库引擎来访问，关键是使用什么方法来使 用引擎。目前访问 ACCESS数据库常用的有 DAO和 ADO方法，DAO就是 Database Access Object(数据库 访问对象 )的英文缩写， DAO是老式的 , 它目前还在使用的原因是向下兼容和ADO在一些地方还没有取代 DAO的功能。 ADO是 ActiveX Data Object(数据控件对象)的英文缩写，是目前较新和功能较强的方法。 通过 DAO或ADO可以访问多种类型的数据库，包括 Access、SQL Server、 Oracle等，也可访问应用 程序的文件，如Excel文件、文本文件及 Email和 NEWS的文件。实际上利用控件来链接数据库，其概念 大致相同，首先都需创建链接，其后用 Open方法产生各种类型的数据集对象，再对数据集对象操作来使 用数据库内容。 3.1 DAO使用的步骤及方法 (1). 引用 DAO类型库 从 VBE的”工具/引用”菜单中选择可引用“Microsoft DAO 3.6 Object Library”项 . 设置DAO数据类型变量 (2). 定义 DAO对象变量 1) Dim db As Database, Database(数据库 )变量对应于 Access数据库 . 2) Dim rs As RecordSet, RecordSet(记录集)变量对应于 Access数据库的一个表或子表 . 多定义为全局变量，以供程序多处使用。 (3). 打开数据库 Set db = OpenDatabase (Thisworkbooks.path & 数据库名礀.mdb ) 用 DAO怎么样打开有密码的 ACCESS 数据库 Set dbTemp = OpenDatabase(C:db.mdb, False, False, ;PWD=12345) (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属性 用来记录目前数据记录的数量