计算机应用-12

1、Excel与数据处理与数据处理 第第3版版认识认识n在学校，一般都要学习类似于在学校，一般都要学习类似于C语言、语言、VB、VFP之类的编之类的编程语言；一般都要编写类似于排序、查询之类的程序。但程语言；一般都要编写类似于排序、查询之类的程序。但工后，绝大多数人都不会再用这样的程序了。然而，这对工后，绝大多数人都不会再用这样的程序了。然而，这对Excel是个例外！这些程序不仅能减化繁杂的工作，而且是个例外！这些程序不仅能减化繁杂的工作，而且还能使工作自动化。还能使工作自动化。n在在EXCEL中：中：nVBA能够使那些枯燥反复的工作变得高效、准确而自动化。能够使那些枯燥反复的工作变得高效、准确而

2、自动化。n若想建立自已的办公自动化数据管理系统，需要用到若想建立自已的办公自动化数据管理系统，需要用到VBA程序。程序。n若想成为一个真正的若想成为一个真正的Excel专家，需要很好地掌握专家，需要很好地掌握VBA程序设计。程序设计。本章本章学习目标学习目标1 1、了解、了解ExcelExcel宏宏的基本知识的基本知识2 2、掌握宏的、掌握宏的录制录制、编写编写和运行方法和运行方法3 3、了解、了解VBAVBA程序的程序的基本知识基本知识4 4、掌握、掌握VBAVBA的基本的基本数据类型数据类型5 5、掌握、掌握VBAVBA宏宏与与函数函数的编写和调用方法的编写和调用方法6 6、掌握、掌握VB

3、AVBA条件条件、循环循环程序的设计方法程序的设计方法7 7、了解用、了解用VBAVBA变量的变量的作用域作用域8 8、了解、了解ExcelExcel的对象模型的对象模型9 9、了解了解VBAVBA自动宏自动宏12.1 宏宏1.宏的概念宏的概念n宏是用宏是用VBA程序设计语言编写或录制的程序设计语言编写或录制的程序程序，其中保存有一系，其中保存有一系列列Excel 的命令，可以被多次重复使用。宏可以的命令，可以被多次重复使用。宏可以自动执行自动执行复杂复杂的任务，减少完成任务所需的步骤。的任务，减少完成任务所需的步骤。nVBA即即Visual Basic for Applications，它是

4、，它是Visual Basic的的一个派生体，它有针对性地对一个派生体，它有针对性地对Visual Basic进行了优化和设置。进行了优化和设置。两者的主要区别在于：两者的主要区别在于：Visual Basic开发的应用程序可以开发的应用程序可以独立独立在在Windows系统中运行，而用系统中运行，而用VBA开发的程序只能开发的程序只能在提供它的在提供它的应用程序中运行应用程序中运行。n在在Excel中，可以用中，可以用Excel提供的提供的宏录制工具录制宏录制工具录制宏程序，也可宏程序，也可以使用它提供的以使用它提供的“Visual Basic 编辑器编辑器”直接编写宏。直接编写宏。12.1

5、.1 录制宏录制宏n宏录制器宏录制器 n宏录制器是宏录制器是Excel提供的一种软件工具，它能够将用户提供的一种软件工具，它能够将用户的操作过程的操作过程记录记录下来，并自动将所记录的操作转换成下来，并自动将所记录的操作转换成为为VBA程序代码程序代码。n对于经常对于经常重复重复进行的操作过程，可以通过宏录制器将进行的操作过程，可以通过宏录制器将它它记录记录下来，当需要再次进行这些操作时，只需要运下来，当需要再次进行这些操作时，只需要运行录制的宏，行录制的宏，Excel就能自动完成这些重复的操作。就能自动完成这些重复的操作。n说明：当录制宏的工作开始后，所有的操作步骤都将说明：当录制宏的工作开

6、始后，所有的操作步骤都将被记录在宏中，所以应被记录在宏中，所以应尽量减少尽量减少不必要的或错误的操不必要的或错误的操作，如果在录制宏时出现失误，更正失误的操作也会作，如果在录制宏时出现失误，更正失误的操作也会记录在宏中。记录在宏中。 12.1.1 录制宏录制宏n录制宏的案例录制宏的案例n【例例12.1】某班主任共管理某班主任共管理10个班级的学生，要为每个班级的学生，要为每个班级建立学生档案，档案表结构如图所示。个班级建立学生档案，档案表结构如图所示。12.1.1 录制宏录制宏n通过通过“视图视图”建立学生档案宏建立学生档案宏1、单击“视图视图”|“宏”的下箭头，选择“录制宏录制宏”命令，弹出

7、“录制新宏录制新宏”对话框输入宏名输入宏名指定执行宏的指定执行宏的快捷键快捷键和和保存位置保存位置12.1.1 录制宏录制宏n通过通过“开发工具开发工具”录制宏录制宏单击单击“开发工开发工具具”单击单击“录制宏录制宏”12.1.1 录制宏录制宏n3.输入表格内容输入表格内容（1）单击）单击A1单元格，在其中输入单元格，在其中输入“2001级学生档案级学生档案”；（2）单击）单击A2单元格，在其中输入单元格，在其中输入“学号学号”；（3）单击）单击B2单元格，在其中输入单元格，在其中输入“姓名姓名”；（4）单击）单击C2单元格，在其中输入单元格，在其中输入“班级班级”；（5）单击）单击D2单元格

8、，在其中输入单元格，在其中输入“性别性别”；（6）单击）单击E2单元格，在其中输入单元格，在其中输入“籍贯籍贯”；（7）单击）单击F2单元格，在其中输入单元格，在其中输入“寝室寝室”；（8）单击）单击G2单元格，在其中输入单元格，在其中输入“电话号码电话号码”；（13）选择）选择A1:G1，然后单击工具条中的跨列居中按钮。，然后单击工具条中的跨列居中按钮。（14）单击“视图视图”|“宏”的下箭头，选择“停止录制录制”命令。或击Excel最下边状态栏中的停止录制停止录制按钮。此时应当尽量避此时应当尽量避免不必要的操作，免不必要的操作，每次操作都将被每次操作都将被转换成宏代码！转换成宏代码！12.

9、1.2 编辑查看录制宏编辑查看录制宏n停止录制，查看录制的宏停止录制，查看录制的宏n选择选择“视图视图”|“宏宏”|“停止录制停止录制”n选择选择“视图视图”|“宏宏”|“查看宏查看宏”菜单项，会显示菜单项，会显示“宏宏”对话框。选中其中的对话框。选中其中的“学生档案学生档案”，单击，单击“编辑编辑” 。12.1.2 编辑查看录制宏编辑查看录制宏在在Excel编程环境中看编程环境中看见的程序代码见的程序代码12.1.2 编辑查看录制宏编辑查看录制宏n宏代码宏代码n1Sub 学生档案学生档案()n2 学生档案学生档案 Macron3宏由宏由 dk 录制，时间录制，时间: 2005-4-20n4

10、快捷键快捷键: Ctrl+sn5Range(A1).Selectn6ActiveCell.FormulaR1C1 = 2001级一班学生档案级一班学生档案n7Range(A2).Selectn8ActiveCell.FormulaR1C1 = 学号学号n9Range(B2).Selectn10ActiveCell.FormulaR1C1 = 姓名姓名n11Range(C2).Selectn12ActiveCell.FormulaR1C1 = 班级班级“n13Range(D2).Selectn14ActiveCell.FormulaR1C1 = 性别性别n15Range(E2).Selectn1

11、6ActiveCell.FormulaR1C1 = 籍贯籍贯n17Range(F2).Selectn18ActiveCell.FormulaR1C1 = 寝室寝室12.1.2 编辑查看录制宏编辑查看录制宏n19Range(G2).Selectn20ActiveCell.FormulaR1C1 = 电话号码电话号码n21Range(A1:G1).Selectn22 With Selectionn23.HorizontalAlignment = xlCentern24.VerticalAlignment = xlBottomn25.WrapText = Falsen26.Orientation =

12、 0n27.AddIndent = Falsen28.ShrinkToFit = Falsen29End Withn30 Selection.Mergen31End Subn31End Sub12.1.2 编辑查看录制宏编辑查看录制宏5、宏的结构、宏的结构Sub 宏名宏名( ) 命令代码命令代码1 命令代码命令代码2 End Sub6、With的结构的结构With Selection End With12.1.3 宏的宏的安全性设置安全性设置nExcel 2007的安全设置的安全设置nOffice 2007采用了比早期版本更加安全的宏采用了比早期版本更加安全的宏运行方式。具有宏代码和没有宏代码

13、的程序被运行方式。具有宏代码和没有宏代码的程序被分别保存在不同类型的文件中，用户通过文件分别保存在不同类型的文件中，用户通过文件扩展名就可判断文档中是否具有宏代码。扩展名就可判断文档中是否具有宏代码。n在在Excel 2007中，没有宏代码的工作薄被保中，没有宏代码的工作薄被保存在存在.xlsx文件中，具有宏代码的工作薄只能被文件中，具有宏代码的工作薄只能被保存在保存在.xlsm类型或与早期版本兼容的类型或与早期版本兼容的.xls文文件类型中。件类型中。 12.1.3 宏的宏的安全性设置安全性设置nExcel 2007信任中心信任中心nExcel2007在默认情况下不再允许任何在默认情况下不再

14、允许任何VBA程序的执程序的执行。要使宏程序运行，用户必须采取一些必要的措施，行。要使宏程序运行，用户必须采取一些必要的措施，在在“信任中心信任中心”中对宏的执行方式进行安全性配置中对宏的执行方式进行安全性配置. (1）单击）单击Office按钮按钮，然后单击，然后单击“Excel选项选项”。 （2）在弹出的）在弹出的“Excel选项选项”对话框中单击对话框中单击“信任信任中心中心”标签，然后单击其中的标签，然后单击其中的“信任中心设置信任中心设置”，Excel会弹出会弹出“信任中心”设置对话框，单击其中的设置对话框，单击其中的“宏设置宏设置” 12.1.3 宏的宏的安全性设置安全性设置n设置

15、受信任位置设置受信任位置 单击单击“受信任位置受信任位置”单击单击“添加新位置添加新位置”在此输入受信任的磁盘在此输入受信任的磁盘目录，凡存入此目录中目录，凡存入此目录中的的Excel工作薄，其中的工作薄，其中的宏都会被认为是安全的。宏都会被认为是安全的。Excel就不给出警告信息就不给出警告信息就执行它就执行它12.1.4 宏的保存宏的保存n保存在个人宏工作簿保存在个人宏工作簿“Personal.xls”中。中。n保存在专门保存宏的保存在专门保存宏的“新的工作簿新的工作簿”中。中。n 保存在建立宏的当前工作薄中。保存在建立宏的当前工作薄中。12.1.5 宏的执行宏的执行n通过快捷键运行宏通过

16、快捷键运行宏n通过对话框运行宏通过对话框运行宏n通过自定义工具按钮运行宏通过自定义工具按钮运行宏n通过图形控件或窗体命令按钮运行宏通过图形控件或窗体命令按钮运行宏12.2 VBA程序设计基础程序设计基础n1、数据类型的概念、数据类型的概念n在计算机中，数据也是按不同的类别进行运算在计算机中，数据也是按不同的类别进行运算和保存的，人们称之为数据类型。同种类型的和保存的，人们称之为数据类型。同种类型的数据占用相同大小的存储空间，相互之间可以数据占用相同大小的存储空间，相互之间可以进行计算、比较或赋值等操作；不同类型的数进行计算、比较或赋值等操作；不同类型的数据占用的存储空间大小不一定相同，且相互之

17、据占用的存储空间大小不一定相同，且相互之间不能进行计算和比较等操作间不能进行计算和比较等操作 。n2、VBA的数据类型的数据类型（1）常量）常量 n数值常量数值常量 ，如：，如：1，2，65，98.65等等12.2 VBA程序设计基础程序设计基础n字符及字符串常量字符及字符串常量 n字符类型的常量称为字符常量，字符常量要用定界字符类型的常量称为字符常量，字符常量要用定界符双引号（符双引号（“ ”）界定。例如，）界定。例如，“d”、“5”、“A”等都等都是字符常量。是字符常量。n日期常量日期常量 日期常量要用定界符（日期常量要用定界符（# #）界定。例如，）界定。例如， # 1 Jul 98 #

18、 、 # 12/2/2000 #等都是日期常量。等都是日期常量。n符号常量符号常量 nConst 常量名常量名=常量值常量值n例如，例如，Const PI=3.14，Const ABC=OK ! China! 12.2 VBA程序设计基础程序设计基础n（2）变量）变量n变量是在程序运行期间其值可以发生变化的数据。变量是在程序运行期间其值可以发生变化的数据。n例如：例如：1 Dim A,B As Integer2 A=13 B=24 A=4+b5 A=3A在本程序段中有在本程序段中有3个不同的值，这就是变！个不同的值，这就是变！12.2 VBA程序设计基础程序设计基础nVBA数值数据类型数值数据

19、类型 数据类型存储空间数 据 范 围Byte1 字节0255Boolean2字节True 或 falseInteger 2 字节-32 76832 767Long4 字节-2 147 483 6482 147 483 647Single4 字节负数：-3.402823E38-1.401298E-45正数： 1.401298E-453.402823E38Double8 字节负数：1.79769313486232E308-4.94065645841247E-324正数：4.94065645841247E-3241.79769313486232E308Currency8 字节-92233720368

20、5477.5808922337203685477.5807Date8字节0100年1月1日到9999年12月31日Object4字节任何对象引用String字符长度分为定长和可变长度两种，可变长可达020亿个字符，定长最多65536个字符Variant(数字)16字节任何数字，最大达到double的数值范围Variant(字符)22+字符串长度约020亿12.2 VBA程序设计基础程序设计基础n字符串类型字符串类型n在在VBA中有两种类型的字符串，变长与定长的字符串。中有两种类型的字符串，变长与定长的字符串。 Dim s1 As String /变长类型Dim s2 As String *10

21、 /定长类型s1=dddkdk s2=d1234567890sssss 12.2 VBA程序设计基础程序设计基础n布尔类型布尔类型n布尔类型是比较运算或逻辑运算的结果值，它布尔类型是比较运算或逻辑运算的结果值，它只有两个取值：只有两个取值：True和和False。True是比较结是比较结果为真时的值，果为真时的值，False是比较结果为假时的值。是比较结果为假时的值。 Dim A As Boolean Dim B As Boolean A=35 B=True12.2 VBA程序设计基础程序设计基础n日期类型日期类型n日期型数据用于保存日期，占日期型数据用于保存日期，占8个字节的存储个字节的存储

22、空间，以浮点数值形式保存日期，可以表示的空间，以浮点数值形式保存日期，可以表示的日期范围从公元日期范围从公元100年年1月月1日到公元日到公元9999 年年12月月31日，而时间可以从日，而时间可以从0:00:00 到到 23:59:59。 Dim d1,d2 As Dated1=# 1 Jul 98 # D1的取值是1998年7月1号d2=# 12/2/2000 # D2的值是2000年12月2号12.2 VBA程序设计基础程序设计基础n变体数据类型变体数据类型nVariant 是一种特殊的数据类型，除了定长是一种特殊的数据类型，除了定长 String 数据及用户定义类型外，它可以包含任数据

23、及用户定义类型外，它可以包含任何种类的数据。何种类的数据。 Dim ar As Variantar = 12ar = string typear = abc & arar = 12.23在本例中，在本例中，ar的类型是不定的！的类型是不定的！12.2.2 数组数组n数组数组 n在在VBA中，可以声明一个数组来代表一组具有相同数据类中，可以声明一个数组来代表一组具有相同数据类型的数据，它就是数组。型的数据，它就是数组。假设一个班有假设一个班有20个同学，每个同学有个同学，每个同学有5门课程，可以定义一个门课程，可以定义一个20行行5列的二维数组来保存他们的成绩列的二维数组来保存他们的成绩

24、Dim stu(1 To 20, 1 To 5) As Single这条命令定义了一个二维表格，如下所示。这条命令定义了一个二维表格，如下所示。stu(1,1)=78stu(1,2)=90stu(1,3)=87stu(1,4)=88stu(1,5)=76数组访问方法数组访问方法7812.2.3 对象、属性对象、属性和方法和方法n对象、属性和方法对象、属性和方法 n计算机程序设计中的对象是从现实世界中抽象出来计算机程序设计中的对象是从现实世界中抽象出来的，它与现实世界中的对象具有相同的含义。的，它与现实世界中的对象具有相同的含义。n对象具有属性和方法两种特性。对象具有属性和方法两种特性。年龄：年

25、龄：30学历：硕士学历：硕士身高：身高：173cm体重：体重：69公斤公斤说自己的年龄说自己的年龄学习学习授课授课李立李立属性属性方法方法对象属性语法规则对象属性语法规则李立李立.年龄年龄=32李立李立.体重体重=70方法的调用也要按这种语法规则方法的调用也要按这种语法规则李立李立.学习学习李立李立.授课授课12.2.3 对象、属性对象、属性和方法和方法nExcel中的主要对象中的主要对象n在在Excel 中，从应用程序到工作薄、工作表、图表及中，从应用程序到工作薄、工作表、图表及其中的各个元素，如单元格、快速访问工具条、选项其中的各个元素，如单元格、快速访问工具条、选项卡、各命令按钮、窗体以

26、及数据透视表等都是对象。卡、各命令按钮、窗体以及数据透视表等都是对象。 Excel对象示例对象示例12.2.3 对象、属性对象、属性和方法和方法宏代码中的宏代码中的对象对象 Sub 宏宏4()1 Range(E6).Select2 ActiveCell.FormulaR1C1 = eqw3 Range(E6:E7).Select4 Selection.Font.Bold = True5 Selection.Font.Italic = True6 With Selection.Font7 .Name = 华文楷体华文楷体8 .Size = 129 .Shadow = False10 .Under

27、line = xlUnderlineStyleNone11 .ColorIndex = xlAutomatic12 End With13 Selection.Font.ColorIndex = 314 End Sub12.3 子程序子程序1.子程序的两种结构子程序的两种结构子程序是子程序是VBA的最小程序单位，它必须独立存在，的最小程序单位，它必须独立存在，但在一个子程序中可以调用另外一个子程序。但在一个子程序中可以调用另外一个子程序。它有两种形式，第一种没有参数，第二种有它有两种形式，第一种没有参数，第二种有参数参数 Sub 子程序名子程序名 子程序代码子程序代码 End SubSub 子程

28、序名子程序名(p1, p2, p3) 子程序代码子程序代码 End Sub12.3 子程序子程序2、子程序的调用形式、子程序的调用形式1）直接调用）直接调用n直接调用子程序名，如果有参数，则在子程序后面直接调用子程序名，如果有参数，则在子程序后面直接写上调用参数；直接写上调用参数；2）用）用Call命令调用命令调用n在在Call命令的后面写上了程序的名字，如果子程序命令的后面写上了程序的名字，如果子程序有参数，则必须将参数写在括号中。有参数，则必须将参数写在括号中。 12.3 子程序子程序3、子程序调用举例、子程序调用举例1 Sub Main()2 HouseCalc 99800, 43100

29、3 Call HouseCalc(380950, 49500)4 CircleArea (4)5 CircleArea 46 Message7 End SubSub HouseCalc(price As Single, wage As Single) If 3*wage 1500 Then rate=0.1 Else rate=0.05 如果如果salary的值为的值为2000，则，则rate=0.1 如果如果salary的值为的值为100， 则则rate=0.0512.5.1 条件条件语句语句n形式形式2If 条件条件 Then 语句组语句组1 Else 语句组语句组2 End IfFunc

30、tion abc (a, b)If a b Then t = a a = b b = tEnd Ifabc=aEnd Function例如例如12.5.1 条件条件语句语句n形式形式3If 条件条件1 Then 语句组语句组1ElseIf 条件条件2 Then 语句组语句组2 ElseIf 条件条件n Then 语句组语句组nElse 语句组语句组n+1End If 12.5.1 条件条件语句语句3、条件函数案例、条件函数案例n【例例12.4】某汽车出租公司可为顾客提供运送货物的业某汽车出租公司可为顾客提供运送货物的业务，根据货物的重量及路程可对运费进行适当的优惠。设务，根据货物的重量及路程可

31、对运费进行适当的优惠。设运费运费F（单位为元），重量（单位为元），重量P（单位为吨），路程（单位为吨），路程S（公里）（公里）及优惠系数（及优惠系数（D）之间的关系式为：）之间的关系式为：F=P*S*W*(1-D)。n优惠系数优惠系数D与路程远近的关系如下，编写计算折扣与路程远近的关系如下，编写计算折扣的函数的函数1000S%101000S750%7750S500%5500S250%2250S0D12.5.1 条件条件语句语句n计算折扣的函数Function d(s) If s = 1000 Then d = 0.1 ElseIf s = 750 Then d = 0.07 ElseIf s

32、= 500 Then d = 0.05 ElseIf s = 250 Then d = 0.02 Else d = 0 End IfEnd Function12.5.1 条件条件语句语句n调用自定义函数计算调用自定义函数计算12.5.2 分分情况选择语句情况选择语句n分情况选择语句分情况选择语句 Select Case语句的语法结构如下。语句的语法结构如下。Select Case 测试表测试表达式达式Case 表达式表达式1 语句组语句组1Case 表达式表达式2 语句组语句组2 Case Else 语句组语句组n End Select 12.5.2 分分情况选择语句情况选择语句nSelect

33、 案例案例n【例例12.5】某学校的职工人事数据存在某学校的职工人事数据存在Excel工作表工作表中。现要按职称提升每位职工的工资，各种职称的工中。现要按职称提升每位职工的工资，各种职称的工资增长情况如下：资增长情况如下：教授教授150、副教授、副教授130、讲师、讲师100、助教助教80、高级工程师、高级工程师150、工程师、工程师140、助工、助工90。n用用select语句编写计算增加工资的语句编写计算增加工资的 函数。函数。12.5.2 分分情况选择语句情况选择语句n编写的编写的Seclect 函数函数Function AddSalary(职称职称) 职称作为一个参数职称作为一个参数

34、Select Case 职称职称 Case 教授教授, 高级工程师高级工程师 AddSalary = 150 Case 副教授副教授 AddSalary = 130 Case 讲师讲师 AddSalary = 100 Case 助教助教 AddSalary = 80 Case 工程师工程师 AddSalary = 140 Case 助工助工 AddSalary = 90 End SelectEnd Function12.5.2 分分情况选择语句情况选择语句n【例例12.6】 某教师讲授了一门考查课程，他平时及期末某教师讲授了一门考查课程，他平时及期末考试都用百分制衡量学生的成绩，设该教师的成绩

35、表如图考试都用百分制衡量学生的成绩，设该教师的成绩表如图12.20所示。学校要求最后的成绩都用等级制。百分制与所示。学校要求最后的成绩都用等级制。百分制与等级制的转换规则为：等级制的转换规则为：60分以下为不及格，分以下为不及格，6070分为分为及格，及格，7080分为中等，分为中等，8090分为良，分为良，90100分分为优。编写一个百分制与等级制的转换函数实现成绩的等为优。编写一个百分制与等级制的转换函数实现成绩的等级转换。级转换。 12.5.2 分分情况选择语句情况选择语句n编写的成绩转换函数编写的成绩转换函数Function change(score) Select Case scor

36、e Case Is = 90 change = 优优 Case Is = 80 change = 良良 Case Is = 70 change = 中中 Case Is = 60 change = 及格及格 Case Else change = 不及格不及格 End SelectEnd Function12.6 VBA循环结构循环结构n在计算机中，一些被重复执行的语句是通过循在计算机中，一些被重复执行的语句是通过循环来完成的。环来完成的。n12.6.1 ForNext循环结构循环结构 For counter = start To end step 步长步长 循环语句循环语句1 循环语句循环语句

37、2 循环语句循环语句3 循环语句循环语句nNext counter12.6.1 For循环结构循环结构nFor循环的工作过程如下：循环的工作过程如下：（1）计算出）计算出start，end和和s的值（这三项都可以是变量、的值（这三项都可以是变量、表达式或常数），并把表达式或常数），并把start的计算结果值赋给循环控的计算结果值赋给循环控制变量制变量x。（2）比较）比较x与与end的计算结果，如果的计算结果，如果x的值超过了的值超过了end的的计算结果就停止循环，执行计算结果就停止循环，执行Next 后面的语句。否则，后面的语句。否则，执行第执行第3步。步。（3）执行循环语句组中的语句。）执行

38、循环语句组中的语句。（4）将）将x中的值增加一个步长值中的值增加一个步长值s，然后执行第，然后执行第2步。步。12.6.1 For循环结构循环结构nFor循环案例循环案例 【例例12.7】某公司职工档案数据保存在某公司职工档案数据保存在Excel工工作表中，如下图所示。该公司共有作表中，如下图所示。该公司共有1 234名职名职工，每月要从工资表中扣除一定的住房公积金，工，每月要从工资表中扣除一定的住房公积金，假设住房公积金按以下的百分比扣除。假设住房公积金按以下的百分比扣除。 n编写计算公积金比例的宏程序。编写计算公积金比例的宏程序。2000S%102000S1500%71500S1200%5

39、1200S800%2800S500%1500S0D12.6.1 For循环结构循环结构Function countrate(salary As Double)这个函数计算公积金的百分比这个函数计算公积金的百分比 Dim rate As Double If salary 2000 Then rate = 0.1 ElseIf salary 1500 Then rate = 0.07 ElseIf salary 1200 Then rate = 0.05 ElseIf salary 1000 Then rate = 0.02 ElseIf salary 800 Then rate = 0.01 E

40、lse rate = 0 End If countrate = rateEnd Function数函的例比金积公算计数函的例比金积公算计12.6.1 For循环结构循环结构n计算所有职工工积金的宏计算所有职工工积金的宏Sub CountData()For i = 3 To 1236 从工作表的第3行开始计算。第3行中的是第1位职工工资 r = countrate(Cells(i, 2) 计算出第i位职工的公积金比例 Cells(i, 3) = r 将第i位职工的公积金比例填入本行的第3列中 Cells(i, 3).Style = Percent 将公积金比例的格式设置为百分比 Cells(i,

41、 4) = r * Cells(i, 2) 计算第1位职工的公积金并填入本行的第4列中 Cells(i, 5) = Cells(i, 2) - Cells(i, 4) 计算第i位职工的应发工资并填入第5列中NextEnd Sub12.6.1 For循环结构循环结构n调用宏计算公积金调用宏计算公积金12.6.1 For循环结构循环结构nFor EachNext循环循环n在在Excel中有许多集合类型的数据，如中有许多集合类型的数据，如Workbooks、Windows、Addins、Worksheets、Charts、Names、Comments、Files、Forms、Drives、Range

42、、Selection、Shapes等，为等，为了方便对集合元素的重复处理，了方便对集合元素的重复处理，Excel提供了提供了For EachNext循环。循环。nFor Each.Next 主要是针对集合或数组的循环，使用它可以在主要是针对集合或数组的循环，使用它可以在不知道集合或数组中元素个数的情况下，对于数组或集合中的每不知道集合或数组中元素个数的情况下，对于数组或集合中的每个元素，重复执行一组语句。其语法形式如下：个元素，重复执行一组语句。其语法形式如下：For Each element In group语句组语句组Next n其中其中group是集合或数组的名称，是集合或数组的名称，e

43、lement是用来遍历集合或数是用来遍历集合或数组中所有元素的变量。如果组中所有元素的变量。如果group是一个集合，则是一个集合，则element 可以可以是是 Variant 变量或对象变量。如果变量或对象变量。如果group是数组，则是数组，则element只只能是一个能是一个 Variant 变量。变量。 12.6.1 For循环结构循环结构n下面的宏会针对数组的每个元素进行循环，下面的宏会针对数组的每个元素进行循环，将每个数组元素的值设置成它的下标值。将每个数组元素的值设置成它的下标值。Sub setArray() Dim TestArray(10) As Integer, I As

44、 Variant For Each I In TestArray TestArray(I) = I Next IEnd Sub12.6.1 For循环结构循环结构n下面的宏将显示出活动工作薄中每个工作下面的宏将显示出活动工作薄中每个工作表的名称。表的名称。Sub msgSheetName() Dim s As Worksheet For Each s In ActiveWorkbook.Worksheets MsgBox s.Name Next sEnd Sub 12.6.1 For循环结构循环结构n下面的宏将关闭活动工作薄之外的所有打下面的宏将关闭活动工作薄之外的所有打开工作薄。开工作薄。S

45、ub closeWorkbook() Dim w As Workbook For Each w In Workbooks If w.Name ActiveWorkbook.Name Then w.Close NextEnd Sub12.6.1 For循环结构循环结构【例例12.8】用用For Each循环设计一个循环设计一个Sub过程，计算工作表中任意选定区域的数据过程，计算工作表中任意选定区域的数据总和，如图总和，如图12.22所示。所示。12.6.1 For循环结构循环结构n求任意区域总和的宏如下求任意区域总和的宏如下Sub sumSelection() For Each x In Sel

46、ection s = s + x Next MsgBox s End Sub12.6.2 WhileWend循环结构循环结构n在事先知道循环次数时，用在事先知道循环次数时，用For循环方便。如果事先循环方便。如果事先并不知道循环次数，但知道执行或结束循环的条件，并不知道循环次数，但知道执行或结束循环的条件，则用则用DoLoop循环或循环或WhileWend循环。循环。nWhile循环的语法结构如下循环的语法结构如下 While 判定条件判定条件 语句语句1 语句语句2 Wend 12.6.3 DoLoop循环结构循环结构 n3 DoLoop循环结构循环结构 第一种结构为：第一种结构为：Do W

47、hile 判定条件判定条件 Loop第二种结构为：第二种结构为：Do Loop While 判定条件判定条件第三种结构为：第三种结构为：Do Until判定条件判定条件 Loop 第四种结构为：第四种结构为：Do Loop Until 判定条件判定条件12.6.3 DoLoop循环结构循环结构n案例案例n【例例12.9】在上节的在上节的For循环实例中，假设并循环实例中，假设并不知道有多少位职工，在其他条件都相同的条不知道有多少位职工，在其他条件都相同的条件下，现用件下，现用WhileWend循环结构来计算每循环结构来计算每位职工的住房公积金比例、应交公积金和实际位职工的住房公积金比例、应交公

48、积金和实际发放工资。发放工资。n改写的函数如下改写的函数如下 12.6.3 DoLoop循环结构循环结构n用用While循环改定的函数循环改定的函数Sub CountDataB()i = 3 第第1位职工在第位职工在第3行行While Not (IsEmpty(Cells(i, 2) 从工作表的第从工作表的第3行开始计算行开始计算 r = countrate(Cells(i, 2) 计算出第计算出第i位职工的公积金比例位职工的公积金比例 Cells(i, 3) = r 把第把第i位职工的公积金比例填入本行的第位职工的公积金比例填入本行的第3列中列中 Cells(i, 3).Style = Pe

49、rcent 把公积金比例的格式设置为百分比把公积金比例的格式设置为百分比 Cells(i, 4) = r * Cells(i, 2) 计算第计算第1位职工的公积金并填入本行的第位职工的公积金并填入本行的第4列中列中 Cells(i, 5) = Cells(i, 2) - Cells(i, 4) 计算第计算第i位职工的应发工资并填入本行第位职工的应发工资并填入本行第5列中列中 i = i + 1 计算下一位职工的公积金计算下一位职工的公积金WendEnd Sub12.6.3 DoLoop循环结构循环结构n用用do循环改写的函数循环改写的函数Sub CountDataC ( )i = 3 Do r

50、 = countrate(Cells(i, 2) Cells(i, 3) = r Cells(i, 3).Style = Percent Cells(i, 4) = r * Cells(i, 2) Cells(i, 5) = Cells(i, 2) - Cells(i, 4) i = i + 1Loop Until (IsEmpty(Cells(i, 2)End Sub 12.6.4 数组在循环数组在循环中的应用中的应用n循环与数组循环与数组 n循环的主要功能是进行循环的主要功能是进行VBA代码的重复执行，代码的重复执行，数组的主要功能是存储大量的数据。数组的主要功能是存储大量的数据。n在计算

51、机应用中，数组和循环相结合能够解决在计算机应用中，数组和循环相结合能够解决许多实际问题。例如大批量数据的排序、统计、许多实际问题。例如大批量数据的排序、统计、检索等功能。检索等功能。 n案例案例12.6.4 数组在循环数组在循环中的应用中的应用n【例例12.10】某学校每年对新生都要进行一次英语某学校每年对新生都要进行一次英语入学测试，以了解每位学生的英语能力。考试完毕后，入学测试，以了解每位学生的英语能力。考试完毕后，要统计每个分数段的人数，并根据学生的成绩将他们要统计每个分数段的人数，并根据学生的成绩将他们分为分为A、B、C若干个班，第若干个班，第125名同学进入名同学进入A班，班，第第2

52、650名同学进入名同学进入B班，其余的以此类推。该校每班，其余的以此类推。该校每年招收的人数都有细小的差别。假设学生的英语测试年招收的人数都有细小的差别。假设学生的英语测试成绩如下页图示。成绩如下页图示。n 编写一个宏完成以下功能。编写一个宏完成以下功能。n自动统计出各分数段的人数，并将统计结果保存在自动统计出各分数段的人数，并将统计结果保存在一个新的工作表中。一个新的工作表中。n对所有的考试成绩进行排序。对所有的考试成绩进行排序。n根据排名对学生进行分班，每个班根据排名对学生进行分班，每个班25名学生，每个名学生，每个班保存在一个新的工作表中。班保存在一个新的工作表中。12.6.4 数组在循

53、环数组在循环中的应用中的应用n成绩表成绩表n本例程序代码较多，不便本例程序代码较多，不便PPT演示，可直接查看演示，可直接查看Excel中的宏。中的宏。12.7 模块与变量模块与变量的作用域的作用域1.VBA变量的作用域变量的作用域n变量的作用域是指变量的有效范围。变量的作用域是指变量的有效范围。n局部变量局部变量n在在VBA宏或函数内部定义的变量，只能在定义它宏或函数内部定义的变量，只能在定义它的函数或宏范围内可用，这种变量称局部变量。的函数或宏范围内可用，这种变量称局部变量。n模块级变量模块级变量 n如果一个变量在模块的开始部分定义，且不包含如果一个变量在模块的开始部分定义，且不包含在任何

54、宏或函数体内，则在本模块内的所有宏或在任何宏或函数体内，则在本模块内的所有宏或函数中都可以使用它函数中都可以使用它 。n全局变量全局变量 n在应用程序的各个模块之间都可共用的变量。在应用程序的各个模块之间都可共用的变量。 12.7 模块与变量模块与变量的作用域的作用域n2、变量的定义方式、变量的定义方式n宏或函数的私有变量，可直接在宏或函数体内定义。宏或函数的私有变量，可直接在宏或函数体内定义。n在模块的声明段中用在模块的声明段中用Dim语句声明一个模块级变量。语句声明一个模块级变量。n在模块的声明段中（或模块内），使用在模块的声明段中（或模块内），使用Public 语句，语句，而非而非Dim

55、语句来声明一个公共变量。声明方式如下。语句来声明一个公共变量。声明方式如下。nPublic 变量名表变量名表 As 数据类型数据类型n在宏或函数的名称前也可以加上在宏或函数的名称前也可以加上Public关键字，这样关键字，这样的宏或函数就是全局宏（全局函数）。例如的宏或函数就是全局宏（全局函数）。例如，Public Ma, Ya, Ta As Integer。 12.7 模块与变量模块与变量的作用域的作用域n3、变量类型的案例、变量类型的案例n【例例12.11】有一个教师的档案表（部分数据），有一个教师的档案表（部分数据），如图所示。编写宏将其中的男教师和女教师分别保存如图所示。编写宏将其中的

56、男教师和女教师分别保存在新工作表中。在新工作表中。12.7 模块与变量模块与变量的作用域的作用域n插入两模块，在模块插入两模块，在模块1中的程序如下中的程序如下Public N声明一个全局变量用于保存教师总人数声明一个全局变量用于保存教师总人数Public tea(100,5) As Variant声明一个全局数组保存教师的所有数据声明一个全局数组保存教师的所有数据，Sub readData()Dim i,j As Integer定义两个只能在本宏内部分使用的局部变量定义两个只能在本宏内部分使用的局部变量Worksheets(教师总表教师总表).Activate 激活教师工作表激活教师工作表N

57、=1While Not IsEmpty(Cells (N,1)如果教师工作表中的第一列数据非空如果教师工作表中的第一列数据非空，就将，就将For i=1 To 5该行的数据读入全局数组中该行的数据读入全局数组中tea(N,i)=Cells(N,i)Next iN=n+1读入一个教师的数据后，教师的人数增加一个读入一个教师的数据后，教师的人数增加一个WendEnd Sub12.7 模块与变量模块与变量的作用域的作用域n分离出男教师的宏分离出男教师的宏Dim i,j,k As IntegerSub man()Sheets.Add 插入一个工作表，在其中保存男教师的数据插入一个工作表，在其中保存男教

58、师的数据Active Sheet.Name=男教师男教师修改工作表的名称为修改工作表的名称为“男教师男教师”For j=1 To 5Cells(1,j)=tea(1,j)读入第一行数据即表头到二维数组的第一行读入第一行数据即表头到二维数组的第一行Next jk=2k统计男教师的人数统计男教师的人数For i=2 To N从数组的第从数组的第2行开始分析数据行开始分析数据If tea(i,3)=男男Then如果数组的第如果数组的第3列中的数据为列中的数据为“男男”For j=1 To 5Cells(k,j)=tea(i,j) 将将“男男”教师的数据从数组读入工作表教师的数据从数组读入工作表Nex

59、t jk=k+1End IfNext iEnd Sub12.7 模块与变量模块与变量的作用域的作用域n制定调用宏的工作表制定调用宏的工作表12.8 VBA自动宏自动宏n1、概述、概述n所谓自动宏就是指在某一特定时间自动执行的宏程序所谓自动宏就是指在某一特定时间自动执行的宏程序nExcel VBA提供了两个非常有用的自动运行宏，一个提供了两个非常有用的自动运行宏，一个是宏是宏Auto_Open，另一个是宏，另一个是宏Auto_Close。nAuto_Open宏，在打开它所在的工作簿时，它就会自宏，在打开它所在的工作簿时，它就会自动运行。动运行。 Auto_Close会在关闭它所在的工作薄时自会在

60、关闭它所在的工作薄时自动运行。动运行。n 这两个宏常被用来完成一些自动化工作，如打开工作这两个宏常被用来完成一些自动化工作，如打开工作薄时启动自定义菜单，打开必备的工作薄或工作表，薄时启动自定义菜单，打开必备的工作薄或工作表，或初始化一些数据，如此等等或初始化一些数据，如此等等。关闭工作薄时，。关闭工作薄时，完成一些结束工作，如保存数据，删除不必要的工作完成一些结束工作，如保存数据，删除不必要的工作表等。表等。12.8 VBA自动宏自动宏n案例案例-Auto_open【例例12.12】在例在例12.11的教师档案管理宏中设计自动宏，的教师档案管理宏中设计自动宏，在工作薄打开时自动读取教师工作表数据到全