E某cel+vba基础入门教学教育资料.doc

《E某cel+vba基础入门教学教育资料.doc》由会员分享，可在线阅读，更多相关《E某cel+vba基础入门教学教育资料.doc（22页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、VBA 入门系列讲座入门系列讲座1.1 VBA 是什么是什么 直到 90 年代早期,使应用程序自动化还是充满挑战性的领域.对每个需要自动 化的应用程序,人们不得不学习一种不同的自动化语言.例如:可以用 excel 的宏语 言来使 excel 自动化,使用 word BASIC 使 word 自动化,等等.微软决定让它开发 出来的应用程序共享一种通用的自动化语言-Visual Basic For Application(VBA),可以认为 VBA 是非常流行的应用程序开发语言 VASUAL BASIC 的子集.实际上 VBA 是”寄生于”VB 应用程序的版本.VBA 和 VB 的区别 包括如下几

2、个方面: 1.VB 是设计用于创建标准的应用程序,而 VBA 是使已有的应用程序 (excel 等)自动化 2.VB 具有自己的开发环境,而 VBA 必须寄生于已有的应用程序. 3.要运行 VB 开发的应用程序,用户不必安装 VB,因为 VB 开发出的应用 程序是可执行文件(*.EXE),而 VBA 开发的程序必须依赖于它的”父”应 用程序,例如 excel. 尽管存在这些不同,VBA 和 VB 在结构上仍然十分相似.事实上,如果你已经了 解了 VB,会发现学习 VBA 非常快.相应的,学完 VBA 会给学习 VB 打下坚实的基 础.而且,当学会在 excel 中用 VBA 创建解决方案后,即

3、已具备在 word access OUTLOOK FOXPRO PROWERPOINT 中用 VBA 创建解决方案的大部分知识. VBA 一个关键特征是你所学的知识在微软的一些产品中可以相互转化. VBA 可以称作 excel 的“遥控器”. VBA 究竟是什么?更确切地讲,它是一种自动化语言,它可以使常用的程序自动 化,可以创建自定义的解决方案. 此外,如果你愿意,还可以将 excel 用做开发平台实现应用程序. 1.2 Excel 环境中基于应用程序自动化的优点环境中基于应用程序自动化的优点也许你想知道 VBA 可以干什么?使用 VBA 可以实现的功能包括:1.使重复的任务自动化. 2.自

4、定义 excel 工具栏,菜单和界面. 3.简化模板的使用. 4.自定义 excel,使其成为开发平台. 5.创建报表. 6.对数据进行复杂的操作和分析.用 excel 作为开发平台有如下原因:1.excel 本身功能强大,包括打印,文件处理,格式化和文本编辑. 2.excel 内置大量函数. 3.excel 界面熟悉. 4.可连接到多种数据库. 用其他语言开发应用程序,一半的工作是编写一些基本功能的模块,包括文件的 打开和保存,打印,复制等.而用 excel 作为开发平台,则由于 excel 已经具备这些基 本功能,你要做的只是使用它. 1.3 录制简单的宏录制简单的宏 在介绍学习 VBA

5、之前，应该花几分钟录制一个宏。 新术语：“宏” ，指一系列 excel 能够执行的 VBA 语句。 以下将要录制的宏非常简单，只是改变单元格颜色。请完成如下步骤： 1)打开新工作簿，确认其他工作簿已经关闭。 2)选择 A1 单元格。调出“常用”工具栏。 3)选择“工具”“宏”“录制新宏” 。 4)输入“改变颜色”作为宏名替换默认宏名，单击确定,注意，此时状态栏中 显示“录制” ，特别是“停止录制”工具栏也显示出来。替换默认宏名主要是便 于分别这些宏。 宏名最多可为 255 个字符，并且必须以字母开始。其中可用的字符包括： 字母、数字和下划线。宏名中不允许出现空格。通常用下划线代表空 格。 5)

6、选择“格式”的“单元格” ，选择“图案”选项中的红色,单击“确定” 。 6)单击“停止录制”工具栏按钮，结束宏录制过程。 如果“停止录制”工具栏开始并未出现，请选择“工具”“宏” “停止录制” 。 录制完一个宏后就可以执行它了。 1.4 执行宏执行宏 当执行一个宏时，excel 按照宏语句执行的情况就像 VBA 代码在对 excel 进 行“遥控” 。但 VBA 的“遥控”不仅能使操作变得简便，还能使你获得一些使 用 excel 标准命令所无法实现的功能。而且，一旦熟悉了 excel 的“遥控” ，你 都会奇怪自己在没有这些“遥控”的情况下，到底是怎么熬过来的。要执行刚 才录制的宏，可以按以下

7、步骤进行： 1）选择任何一个单元格，比如 A3。 2）选择“工具”“宏”“宏” ，显示“宏”对话框。 3）选择“改变颜色” ，选择“执行” ，则 A3 单元格的颜色变为红色。试着选 择其它单元格和几个单元格组成的区域，然后再执行宏，以便加深印象。 1.5 查看录制的代码查看录制的代码 到底是什么在控制 excel 的运行呢?你可能有些疑惑.好,让我们看看 VBA 的语 句吧. 1）选择“工具”“宏”“宏” ，显示“宏”对话框。 2）单击列表中的“改变颜色”,选择“编辑”按钮。 此时，会打开 VBA 的编辑器窗口（VBE） 。关于该编辑器，以后再详细说明， 先将注意力集中到显示的代码上。代码如下

8、：（日期和姓名会有不同） Sub 改变颜色() 改变颜色 Macro xw 记录的宏 2000-6-10 With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End Sub 将来会十分熟悉这种代码，虽然现在它们看上去像一种奇怪的外语。学习 VBA 或编程语言在某种程度上比较像在学习一种外语。 Sub 改变颜色():这是宏的名称。 中间的以“ ”开头的五行称为“注释” ，它 在录制宏时自动产生。 以 With 开头到 End With 结束的结构是

9、 With 结构语句， 这段语句是宏的主要部分。注意单词“selection” ，它代表“突出显示的区域” （即：选定区域） 。With Selection.Interior ：它读作“选择区域的的内部”.这整 段语句设置该区域内部的一些“属性” 。 其中： .ColorIndex = 3: 将该内部设为红色。注意：有一小圆点，它的作用在于简化语 句，小圆点代替出现在 With 后的词,它是 With 结构的一部分。另外：红色被数 字化为 3.(红色警戒是否可称作：3 号警戒，嗯？)有兴趣的话，你将 3 改为其他 数字试试看。 .Pattern = xlSolid：设置该区域的内部图案。由于是

10、录制宏，所以，虽然你并未 设置这一项，宏仍然将其记录下来(因为在“图案”选项中有此一项，只是你为 曾设置而已)。xlSolid 表示纯色。 .PatternColorIndex = xlAutomatic：表示内部图案底纹颜色为自动配色。 End With:结束 With 语句。 End Sub:整个宏的结束语整个宏的结束语 1.6 编辑录制的代码的代码 在上一节，我们录制了一个宏并查看了代码，代码中有两句实际上并不起作 用。哪两句？现在，在宏中作一个修改，删除多余行，直到和下面代码相同： Sub 改变颜色() 改变颜色 Macro xw 记录的宏 2000-6-10 With Selecti

11、on.Interior .ColorIndex = 3 End With End Sub 完成后，在工作表中试验一下。你会发现结果和修改前的状况一样。在 With 语句前加入一行: Range(“A5”).Select 试着运行该宏,则无论开始选择哪个单元格,宏运行结果都是使 A5 单元格变 红. 现在可以看到，编辑录制的宏同样非常简单。需要编辑宏是因为以下三个方面的原因。一：在录制中出错而不得不修改。二：录制的宏中有多余的语句需 要删除，提高宏的运行速度。三：希望增加宏的功能。比如：加入判断或循环 等无法录制的语句。 1.7 录制宏的局限性录制宏的局限性 希望自动化的许多 excel 过程大

12、多都可以用录制宏来完成.但是宏记录器存在 以下局限性.通过宏记录器无法完成的工作有: 1)录制的宏无判断或循环能力. 2)人机交互能力差,即用户无法进行输入,计算机无法给出提示. 3)无法显示 excel 对话框. 4)无法显示自定义窗体. 1.8 小结小结 本学时中,你已经掌握了 VBA 的一些基础知识,你会录制宏、编辑宏而且了解 了录制宏的局限性.你很努力.并且已经为将来学习 VBA 甚至 VB 等编程语言打 下了基础.关键是你已经了解了一个谜底,就是说,你了解了什么是编程.下面是些 小练习,做完后才可以去玩哟. 思考: 1)VBA 只能用于 excel 吗? 2)VBA 是基于哪种语言?

13、 3)说说 excel 和 VBA 的关系. 4)为什么要用宏? 原文发表时间：未知在接下来的练习中将说明如何在 VBA 中使用变量,你将要输入你的名字,并用 一个消息框将其显示出来.具体步骤如下: 1)创建一个名为”显示你的名字”的子程序. 2)输入以下代码: Public Sub 显示你的名字() Dim s 名字 As String s 名字 = Inputbox(“请输入你的名字:”) Msgbox “你好”& s 名字 End Sub 3)将鼠标放到过程中的任何地方,按下 F5 键运行过程,会显示一个输入 框. 4)输入你自己的名字并按回车键,会显示一个消息框,显示的文字中包含 你自

14、己的名字. 5)单击”确定”按钮,返回过程中. 在 Dim 语句中不必提供数据类型.如果没有数据类型,变量将被定义为 Variant 类型,因为 VBA 中默认的数据类型是 Variant.你知道这一点后,最初的反应也许 是觉得应该不用自己决定数据类型,而将一切抛给 VBA.这种观念是完全错误的. 你必须决定选择使用何种数据类型。因为 Variant 数据类型占用存储空间较大 （16 或 22 字节）而且它将影响程序的性能。VBA 必须辨别 Variant 类型的变 量中存储了何种类型的数据。 4.4.3 变量命名的惯例变量命名的惯例 下表给出了推荐的变量命名惯例 表 4-2 变量命名的前缀

15、4.4.4 使用数组使用数组 如果你使用过其他编程序语言,可能对数组已经比较熟悉了.数组是具有相同数 据类型并共同享有一个名字的一组变量的集合.数组中的元素通过索引数字加以 区分,定义数组的方法如下: Dim array_name(n) As type (其中 n 是数组元素的个数) 例如,如果要创建保存 10 个学生名字的数组,可以用以下语句: Dim s 学生名字(9) As Integer 注意,括号中的数字是 9 而不是 10.这是因为在默认的情况下,第一个索引数字 是 0.数组在处理相似信息时非常有用.假设要处理 15 门考试成绩,可以创建 15 个 独立的变量,这意味着要使用 15

16、 个 Dim 语句。也可以创建一个数组来保存考试 成绩，具体如下： Dim s 考试成绩（14） As Integer 声明数组时的另一种方法是不给定大小。可以在程序运行时定义其大小。通 过创建动态数组就可以做到。例如，你的程序要创建一表格，可以提示用户输 入表格的行和列的数目。声明动态数组的语法如下： Dim dyn_array() As type 对数组声明后可以在程序运行时用：ReDim 语句指定数组的大小： ReDim dyn_array()(array_size) 参数 array_size 代表数组的新大小。如果要保留数组的数值，请在 ReDim 语 句后使用保留字 Preserv

17、e,具体语法如下： ReDim Preserve dyn_array(array_size) 4.4.5 变量赋值变量赋值 声明变量后就可以给变量赋值。请注意下列语句中为数组变量赋值时 索引数字的使用。 程序清单 4-4 Dim i 人数 As Integer Dim i 考试成绩 As Integer Dim i As Integer i 人数 = inputbox(“输入学生的人数：”) ReDim Preserve i 考试成绩(i 数量) For i = 1 to i 人数 i 考试成绩(i) = inputbox(“输入考试成绩”& i ) Next i4.5 使用常量使用常量 现在

18、,你已经知道变量是存储非静态信息的存储容器.当存储静态信息时可以创 建常量.它可以供程序多次使用而且便于记忆.比如圆周率比 3.1415926 好理解得 多. 要声明常量并设置常量的值,需要使用 const 语句.常量声明后,不能对它赋一个 新的值.例如,假设需要声明一个常量来保存销项税率,可以使用以下语句: const S 销项税率 As Long = 0.17 通常常量声明时用全大写字母以区分变量. 4.6 作用域作用域 到现在为止,已经学习了如何定义变量和常量,但是还不知道在何处定义.可以 在两个地方定义常量和变量: 过程中定义和在模块顶部一个名为”通用声明”的区域内定义.而且定义的位置

19、 就决定了作用域. 在过程中声明,该变量只能在该过程中使用,其他过程中不能使用这个变量和常 量,这中变量成为局部变量或过程级变量；在模块顶部声明的变量称为模块级变 量，该变量在该模块的所有过程中都可以使用。 还有一种等级的作用域称为公共级。公共级变量可以在应用程序的任何过程 中使用，不论过程和变量是否定义在一个模块。这就使得公共级变量在使用中 十分灵活，但是公共级变量在程序运行时一直保留在内存中，这样就占用了系 统资源。要创建公共级变量，可以用 Public 语句，具体语法如下： Public variablename As datatype 要创建公共级常量，具体语法如下： Public c

20、onst CONSTANAME datatype = value 公共级变量或常量必须在模块顶部的“通用声明”区域中定义。 4.7 小结小结 本课的重点是变量和常量。在本课中你学会了如何创建变量来保存非静态的 数据。我们了解了 VBA 的数据类型，我们也学会了创建常量来保存静态的数据。本课的最后部分我们学习了变量和常量的作用域。 4.8 疑难解答疑难解答 1.如果希望在多个位置使用变量的数据，应该在何处定义该变量？ 答：必须在模块顶部的“通用声明”区域内定义。对于模块级变量用 Dim 语 句，对于公共级变量用 Public 语句 2.模块是否独立于工作簿? 答:不,实际上模块是工作簿的一部分,

21、当保存工作簿时,对模块所做的改变也同 时保存下来. 3.为什么不应该将所有变量定义为 Variant 数据类型? 答:Variant 类型的数 据,占用较多的内存,并且影响应用程序的性能. dvnews_page=(5):利用 VBA 设置工作表使用权限一般保护工作表采取的方法是用 excel 菜单中的“保护”命令，有时这尚嫌 不足，比如一些机密文件根本要让某些使用者无法看到，但又需要他来操作工 作簿中的其他表，怎么办？ 可以打开 VBA 编辑器，打开“工程资源管理器” ，双击该工作表，现在出现的 是设置该表的属性的编辑窗口，单击窗口左上的下拉列表框，选择 worksheet , 这时再从该窗

22、口右上方的列表框中选择 Active(“激活”)，这时自动显示如下的 语句块： Private Sub Worksheet_Activate() End Sub 在其中加入代码：(假设用”123”作为密码,Sheet”机密文档”为限制权限文档, sheet”普通文档”为工作簿中你认为任何适合的工作表) If Application.InputBox(“请输入操作权限密码请输入操作权限密码:”) = 123 Then Range(“A1”).Select Else Msgbox “密码错误,即将退出!” Sheets(“普通文档”).Select End if 程序如下： Private Sub

23、 Worksheet_Activate() If Application.InputBox(“请输入操作权限密码请输入操作权限密码:”) = 123 Then Range(“A1”).Select Else MsgBox “密码错误,即将退出!” Sheets(“普通文档”).Select End If End Sub 这样做仍有一个问题，就是越权使用者仍会看到一些文件的片段，即在提示密 码的那段时间。好，你可以这样做，用上述方法选择工作表的 Deactivate 事件, 输入以下代码： Sheets(“机密文档机密文档”).Cells.Font.ColorIndex = 2 这段程序使得此工

24、作表在不被激活时，所有文字为白色。然后，在第一个程序 中的 Range(“A1”).Select 后插入一行，写入以下代码： ActiveSheet.Cells.Font.ColorIndex = 56 这段程序，在你输入正确密码后，将该表所有文字转变为深灰色。 完整的程序 如下： Private Sub Worksheet_Activate() If Application.InputBox(“请输入操作权限密码请输入操作权限密码:”) = 123 Then Range(“A1”).Select Sheets(“机密文档”).Cells.Font.ColorIndex = 56 Else M

25、sgBox “密码错误,即将退出!” Sheets(“普通文档”).Select End If dvnews_page=(6):提高 excel 中 VBA 的效率由于 Microsoft office 办公套件的广泛应用，以及该软件版本的不断提升，功能 不断完善，在 office 办公套件平台上开发出的的 VBA 应用程序越来越多，而 VBA 是一种宏语言，在运行速度上有很大的限制。因此 VBA 编程的方法直接 关系到 VBA 程序运行的效率，本文列举了一些提高 VBA 程序运行效率的方法。方法 1：尽量使用 VBA 原有的属性、方法和 Worksheet 函数 由于 excel 对象多达百

26、多个，对象的属性、方法、事件多不胜数，对于初学 者来说可能对它们不全部了解，这就产生了编程者经常编写与 excel 对象的属 性、方法相同功能的 VBA 代码段，而这些代码段的运行效率显然与 excel 对象 的属性、方法完成任务的速度相差甚大。例如用 Range 的属性 CurrentRegion 来 返回 Range 对象，该对象代表当前区。 （当前区指以任意空白行及空白列的组合 为边界的区域） 。同样功能的 VBA 代码需数十行。因此编程前应尽可能多地了 解 excel 对象的属性、方法。 充分利用 Worksheet 函数是提高程序运行速度的极度有效的方法。如求平均 工资的例子： Fo

27、r Each c In Worksheet(1).Range(A1:A1000) TotalValue = TotalValue 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

28、 程序代码，提高程序的运行速度。 方法 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

29、: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

30、(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 程序里一定充满了对象的 激活和选择，例如

31、 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 程序运 行速度的最有效的方法，缩短运

32、行时间 2/3 左右。关闭屏幕更新的方法： Application.ScreenUpdate = False 请不要忘记 VBA 程序运行结束时再将该值设回来： Application.ScreenUpdate = True 以上是提高 VBA 运行效率的比较有效的几种方法。 dvnews_page=(7):如何在 excel 里使用定时器用过 excel 里的加载宏 “定时保存” 吗？可惜它的源程序是加密的，现在就上传一篇介绍实现它的文档。 在 office 里有个方法是 application.ontime ，具体函数如下： expression.OnTime(EarliestTime,

33、Procedure, LatestTime, Schedule) 如果想进一步了解，请参阅 excel 的帮助。 这个函数是用来安排一个过程在将来的特定时间运行， （可为某个日期的指定时 间，也可为指定的时间段之后） 。通过这个函数我们就可以在 excel 里编写自己 的定时程序了。下面就举两个例子来说明它。 1.在下午 17:00:00 的时候显示一个对话框。 Sub Run_it() Application.OnTime TimeValue(“17:00:00”), “Show_my_msg” 设置定时器在 17:00:00 激活，激活后运行 Show_my_msg 。 End Sub S

34、ub Show_my_msg() msg = MsgBox(“现在是 17:00:00 ！”, vbInformation, “自定义信息”) End Sub 2.模仿 excel 97 里的 “自动保存宏”,在这里定时 5 秒出现一次 Sub auto_open() MsgBox “欢迎你，在这篇文档里，每 5 秒出现一次保存的提示！”, vbInformation, “请注意！” Call runtimer 打开文档时自动运行打开文档时自动运行 End Sub Sub runtimer() Application.OnTime Now + TimeValue(“00:00:05”), “s

35、aveit” Now + TimeValue(“00:15:00”) 指定在当前时间过 5 秒钟开始运行 Saveit 这个过程。 End Sub Sub SaveIt() msg = MsgBox(“朋友，你已经工作很久了，现在就存盘吗？” & Chr(13) _ & “选择是：立刻存盘” & Chr(13) _ & “选择否：暂不存盘” & Chr(13) _ & “选择取消：不再出现这个提示”, vbYesNoCancel + 64, “休息一会吧！”) 提示用户保存当前活动文档。 If msg = vbYes Then ActiveWorkbook.Save Else If msg = vbCancel Then Exit Sub Call runtimer 如果用户没有选择取消就再次调用 Runtimer End Sub 以上只是两个简单的例子，有兴趣的话，可以利用 Application.Ontime 这个函数 写出更多更有用的定时程序。