Excel的使用第10章.ppt

《Excel的使用第10章.ppt》由会员分享，可在线阅读，更多相关《Excel的使用第10章.ppt（99页珍藏版）》请在三一办公上搜索。

1、第10章 宏与VBA,前言,若想使那些枯燥反复的工作变得高效、准确而自动化，请认真学习本章的内容。若想建立自已的办公自动化数据管理系统，请认真学习本章的内容。若想成为一个真正的Excel专家，不但要学习本章的内容，而且还不够！,本章学习目标,1、了解Excel宏的基本知识2、掌握宏的录制、编写和运行方法3、了解VBA程序的基本知识4、掌握VBA的基本数据类型5、掌握VBA宏与函数的编写和调用方法6、掌握VBA条件、循环程序的设计方法7、掌握VBA窗体的设计方法8、掌握VBA的对话框和菜单程序的设计方法9、了解用VBA和EXCEL相结合开发应用程序的方法,10.1 Excel宏,宏的概念宏是用户

2、用VBA程序设计语言编写或录制的程序，其中保存有一系列Excel 的命令，可以被多次重复使用。宏可以自动执行复杂的任务，减少完成任务所需的步骤。VBA即Visual Basic for Applications，它是Visual Basic的一个派生体，它有针对性地对Visual Basic进行了优化和设置。两者的主要区别在于：Visual Basic开发的应用程序可以独立在Windows系统中运行，而用VBA开发的程序只能在提供它的应用程序中运行。在Excel中，可以用Excel提供的宏录制工具录制宏程序，也可以使用它提供的“Visual Basic 编辑器”直接编写宏。,10.1 Exce

3、l宏,2、录制宏 宏录制器是Excel提供的一种软件工具，它能够将用户的操作过程记录下来，并自动将所记录的操作转换成为VBA程序代码。对于经常重复进行的操作过程，可以通过宏录制器将它记录下来，当需要再次进行这些操作时，只需要运行录制的宏，Excel就能自动完成这些重复的操作。说明：当录制宏的工作开始后，所有的操作步骤都将被记录在宏中，所以应尽量减少不必要的或错误的操作，如果在录制宏时出现失误，更正失误的操作也会记录在宏中。,10.1 Excel宏,录制宏的案例建立10班的学生档案表，档案表的结构如下图所示。录制一个能够建立这种档案表结构的宏。,10.1 Excel宏,建立学生档案宏选择“工具”

4、|“宏”|“录制新宏”菜单项，在弹出的下示对话框中输入宏名字“学生档案”单击“确定”,10.1 Excel宏,3.输入表格内容（1）单击A1单元格，在其中输入“2001级学生档案”。（2）单击A2单元格，在其中输入“学号”。（3）单击B2单元格，在其中输入“姓名”。（4）单击C2单元格，在其中输入“班级”。（5）单击D2单元格，在其中输入“性别”。（6）单击E2单元格，在其中输入“籍贯”。（7）单击F2单元格，在其中输入“寝室”。（8）单击G2单元格，在其中输入“电话号码”。（9）选择A1:G1单元格区域，然后单击工具条中的跨列居中按钮。（10）单击“停止”记录制工具条中的停止按钮。（11）保

5、存该工作簿为“学籍档案.xls”。,10.1 Excel宏,4.停止录制，查看录制的宏选择“工具”|“宏”|“停止录制”选择“工具”|“宏”|“宏”菜单项，会显示“宏”对话框。选中其中的“学生档案”，单击“编辑”。,10.1 Excel宏,宏代码1Sub 学生档案()2 学生档案 Macro3 宏由 dk 录制，时间:2004-7-184 快捷键:Ctrl+s5Range(A1).Select6ActiveCell.FormulaR1C1=2001级一班学生档案7Range(A2).Select8ActiveCell.FormulaR1C1=学号9Range(B2).Select10Activ

6、eCell.FormulaR1C1=姓名11Range(C2).Select12ActiveCell.FormulaR1C1=班级13Range(D2).Select14ActiveCell.FormulaR1C1=性别15Range(E2).Select16ActiveCell.FormulaR1C1=籍贯17Range(F2).Select18ActiveCell.FormulaR1C1=寝室,10.1 Excel宏,19Range(G2).Select20ActiveCell.FormulaR1C1=电话号码21Range(A1:G1).Select22 With Selection23

7、.HorizontalAlignment=xlCenter24.VerticalAlignment=xlBottom25.WrapText=False26.Orientation=027.AddIndent=False28.ShrinkToFit=False29End With30 Selection.Merge31End Sub,10.1 Excel宏,5、宏的结构Sub 宏名()命令代码1 命令代码2 End Sub,6、With的结构With Selection End With,10.1 Excel宏,7、宏的保存保存在个人宏工作簿“Personal.xls”中。保存在专门保存宏的“新

8、的工作簿”中。保存在建立宏的当前工作薄中。,10.1 Excel宏,8、宏的执行通过快捷键运行宏通过对话框运行宏通过自定义工具按钮运行宏通过图形控件或窗体命令按钮运行宏通过自定义菜单运行宏（以后再讲）,10.2 VBA程序设计基础,1、数据类型的概念在计算机中，数据也是按不同的类别进行运算和保存的，人们称之为数据类型。同种类型的数据占用相同大小的存储空间，相互之间可以进行计算、比较或赋值等操作；不同类型的数据占用的存储空间大小不一定相同，且相互之间不能进行计算和比较等操作。2、VBA的数据类型（1）常量 数值常量，如：1，2，65，98.65等,10.2 VBA程序设计基础,字符及字符串常量

9、字符类型的常量称为字符常量，字符常量要用定界符双引号（“”）界定。例如，“d”、“5”、“A”等都是字符常量。符号常量 Const 常量名=常量值例如，Const PI=3.14，Const ABC=OK!China!,10.2 VBA程序设计基础,（2）变量变量是在程序运行期间其值可以发生变化的数据。例如：,1Dim A,B As Integer2A=13B=24A=4+bA=3A在本程序段中有3个不同的值，这就是变！,10.2 VBA程序设计基础,VBA数值数据类型,10.2 VBA程序设计基础,字符串类型在VBA中有两种类型的字符串，变长与定长的字符串。,Dim s1 As String

10、/变长类型Dim s2 As String*10/定长类型s1=dddkdk s2=d1234567890sssss,10.2 VBA程序设计基础,布尔类型布尔类型是比较运算或逻辑运算的结果值，它只有两个取值：True和False。True是比较结果为真时的值，False是比较结果为假时的值。,Dim A As Boolean Dim B As Boolean A=35 B=True,10.2 VBA程序设计基础,日期类型日期型数据用于保存日期，占8个字节的存储空间，以浮点数值形式保存日期，可以表示的日期范围从公元100年1月1日到公元9999 年12月31日，而时间可以从0:00:00 到

11、23:59:59。日期文字以“#”作界定符。,Dim d1,d2 As Dated1=#1 Jul 98#D1的取值是1998年7月1号d2=#12/2/2000#D2的值是2000年12月2号,10.2 VBA程序设计基础,变体数据类型Variant 是一种特殊的数据类型，除了定长 String 数据及用户定义类型外，它可以包含任何种类的数据。,Dim ar As Variantar=12ar=string typear=abc&arar=12.23在本例中，ar的类型是不定的！,10.2 VBA程序设计基础,数组 在VBA中，可以声明一个数组来代表一组具有相同数据类型的数据，它就是数组。,

12、假设一个班有20个同学，每个同学有5门课程，可以定义一个20行5列的二维数组来保存他们的成绩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,数组访问方法,78,10.2 VBA程序设计基础,对象、属性和方法 计算机程序设计中的对象是从现实世界中抽象出来的，它与现实世界中的对象具有相同的含义。对象具有属性和方法两种特性。,对象属性语法规则李立.年龄=32李立.体重=70方法的调用也要按这种语法规则李立.学习李立.授课,Excel对

13、象示例,10.3 子程序,子程序的两种结构子程序是VBA的最小程序单位，它必须独立存在，但在一个子程序中可以调用另外一个子程序。它有两种形式，第一种没有参数，第二种有参数,Sub 子程序名 子程序代码 End Sub,Sub 子程序名(p1,p2,p3)子程序代码 End Sub,10.3 子程序,2、子程序的调用形式1）直接调用直接调用子程序名，如果有参数，则在子程序后面直接写上调用参数；2）用Call命令调用在Call命令的后面写上了程序的名字，如果子程序有参数，则必须将参数写在括号中。,10.3 子程序,子程序调用举例,1 Sub Main()2 HouseCalc 99800,4310

14、03 Call HouseCalc(380950,49500)4 CircleArea(4)5 CircleArea 46 Message7 End Sub,Sub HouseCalc(price As Single,wage As Single)If 3*wage=0.85*price Then MsgBox 你的薪水不能承担房价！Else MsgBox 你的薪金足以承担房价！End IfEnd Sub,Sub CircleArea(R as Single)MsgBox 3.14*R*REnd sub,Sub Message()MsgBox 这是一个无参子程序End Sub,调用,10.4

15、自定义函数,1 函数结构,Function 函数名(p1,p2,p3,)As Type 函数代码 函数名=表达式End Function,10.4 自定义函数,2、定义函数的注意事项 函数由Function和End Function 语句所包含起来的 VBA语句。Function 函数和 Sub子程序很类似，但函数有一个返回值。Function 函数必须通过表达式调用。如果一个 Function 函数没有参数，它的 Function 语句必须包含一个空的圆括号。在函数体中，函数名至少被赋值一次。函数开头行的As Type用于指定函数值的返回值类型，如果省掉该定义，被视为Variant类型。,1

16、0.4 自定义函数,3、【例10-1】编写一个计算圆面积的简单函数，圆半径作为函数参数。Function CircleArea(r As Single)As Single CircleArea=3.14*r*rEnd Function,10.4 自定义函数,4、函数调用函数只能在表达式中调用。在Excel中，函数至少有以下3种调用方式。在Sub子程序中调用函数在其他函数中调用函数在Excel工作表单元格的公式中调用函数,10.4 自定义函数,5、自定义函数案例假设有一个学生成绩表如图所示。现要计算其中的综合成绩，在本例中，综合成绩的计算方法为：综合成绩=考试科目0.7+考查科目0.3。编写计算

17、综合成绩的函数ss。,10.4 自定义函数,计算综合成绩的自定义函数Function ss(ks1,ks2,kc1,kc2,s,c)ss=(ks1+ks2)*s+(kc1+kc2)*cEnd Function该函数定义出来之后，可在编写它的任何工作表中调用，与调用Excel内置函数无任何差别。比如，在上图的G5中输入公式=ss(C5,D5,E5,F5,$E$2,$E$3)然后把该公式向下填充复制到最后一位同学的“综合成绩”单元格，这样就可计算出所有同学的综合成绩。,10.5 VBA选择结构,1、VBA程序执行的方式在一般情况下，一个VBA的Sub子程序和Function函数的执行都是从程序代码

18、的第一个语句行开始，逐条运行程序代码中的语句，直到遇到End Sub或End Function来结束整个程序的执行。有些时候，需要程序代码按一定的条件执行，当条件成立的时候，执行一部分程序代码，条件不成立的时候执行另外一部分程序代码。这种功能需要用VBA的选择结构来实现。,10.5 VBA选择结构,2、VBA条件语句的语法,形式1：If 条件 Then 语句组1 Else 语句组2例如：If salary1500 Then rate=0.1 Else rate=0.05,10.5 VBA选择结构,形式2,If 条件 Then 语句组1 Else 语句组2 End If,Function abc

19、(a,b)If a b Then t=a a=b b=tEnd Ifabc=aEnd Function,例如,10.5 VBA选择结构,形式3,If 条件1 Then 语句组1ElseIf 条件2 Then 语句组2 ElseIf 条件n Then 语句组nElse 语句组n+1End If,10.5 VBA选择结构,3、条件函数案例某汽车出租公司可为顾客提供运送货物的业务，根据货物的重量及路程可对运费进行适当的优惠。设运费F（单位为元），重量P（单位为吨），路程S（公里）及优惠系数（D）之间的关系式为：F=P*S*W*(1-D)。优惠系数D与路程远近的关系如下，编写计算折扣的函数,10.5

20、VBA选择结构,计算折扣的函数Function d(s)If s=1000 Then d=0.1 ElseIf s=750 Then d=0.07 ElseIf s=500 Then d=0.05 ElseIf s=250 Then d=0.02 Else d=0 End IfEnd Function,10.5 VBA选择结构,调用自定义函数计算,10.5 VBA选择结构,4 分情况选择语句 Select Case语句的语法结构如下。,Select Case 测试表达式Case 表达式1 语句组1Case 表达式2 语句组2 Case Else 语句组n End Select,10.5 VBA

21、选择结构,Select 案例某学校的职工人事数据存在Excel工作表中，如图所示。现在，要按职称提升每位职工的工资，各种职称的工资增长情况如下：教授150、副教授130、讲师100、助教80、高级工程师150、工程师140、助工90。用select语句编写计算增加工资的 函数。,10.5 VBA选择结构,编写的Seclect 函数,Function AddSalary(职称)职称作为一个参数 Select Case 职称 Case 教授,高级工程师 AddSalary=150 Case 副教授 AddSalary=130 Case 讲师 AddSalary=100 Case 助教 AddSal

22、ary=80 Case 工程师 AddSalary=140 Case 助工 AddSalary=90 End SelectEnd Function,10.6 VBA循环结构,在计算机中，一些被重复执行的语句是通过循环来完成的。1、ForNext循环结构,For counter=start To end step 步长 循环语句1 循环语句2 循环语句3 循环语句nNext counter,10.6 VBA循环结构,For循环案例 某公司职工档案数据保存在Excel工作表中，如下图所示。该公司共有1 234名职工，每月要从工资表中扣除一定的住房公积金，假设住房公积金按以下的百分比扣除。编写计算公

23、积金比例的宏程序。,10.6 VBA循环结构,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 Else rate=0 End If countrate=rateEnd Functio

24、n,数函的例比金积公算计,10.6 VBA循环结构,计算所有职工工积金的宏,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,4)=r*Cells(i,2)计算第1位职工的公积金并填入本行的第4列中 Cells(i,5)=Cells(i,2)-Cells(i,4)计算第i位职工的应发工资并填

25、入第5列中NextEnd Sub,10.6 VBA循环结构,调用宏计算公积金,10.6 VBA循环结构,2 WhileWend循环结构在事先知道循环次数时，用For循环方便。如果事先并不知道循环次数，但知道执行或结束循环的条件，则用DoLoop循环或WhileWend循环。While循环的语法结构如下,While 判定条件 语句1 语句2 Wend,10.6 VBA循环结构,3 DoLoop循环结构,第一种结构为：Do While 判定条件 Loop,第二种结构为：Do Loop While 判定条件,第三种结构为：Do Until判定条件 Loop,第四种结构为：Do Loop Until

26、判定条件,10.6 VBA循环结构,案例在上节的For循环实例中，假设并不知道有多少位职工，在其他条件都相同的条件下，现用WhileWend循环结构来计算每位职工的住房公积金比例、应交公积金和实际发放工资。改写的函数如下,10.6 VBA循环结构,用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=Per

27、cent 把公积金比例的格式设置为百分比 Cells(i,4)=r*Cells(i,2)计算第1位职工的公积金并填入本行的第4列中 Cells(i,5)=Cells(i,2)-Cells(i,4)计算第i位职工的应发工资并填入本行第5列中 i=i+1 计算下一位职工的公积金WendEnd Sub,10.6 VBA循环结构,用do循环改写的函数,Sub CountDataC()i=3 Do r=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)

28、-Cells(i,4)i=i+1Loop Until(IsEmpty(Cells(i,2)End Sub,10.6 VBA循环结构,4 数组在循环中的应用 循环的主要功能是进行VBA代码的重复执行，数组的主要功能是存储大量的数据。在计算机应用中，数组和循环相结合能够解决许多实际问题。例如大批量数据的排序、统计、检索等功能。案例,10.6 VBA循环结构,某学校每年对新生都要进行一次英语入学测试，以了解每位学生的英语能力。考试完毕后，要统计每个分数段的人数，并根据学生的成绩将他们分为A、B、C若干个班，第125名同学进入A班，第2650名同学进入B班，其余的以此类推。该校每年招收的人数都有细小的

29、差别。假设学生的英语测试成绩如下页图示。编写一个宏完成以下功能。自动统计出各分数段的人数，并将统计结果保存在一个新的工作表中。对所有的考试成绩进行排序。根据排名对学生进行分班，每个班25名学生，每个班保存在一个新的工作表中。,10.6 VBA循环结构,成绩表本例程序代码较多，不便PPT演示，可直接查看Excel中的宏。,10.7 模块与变量的作用域,VBA变量的作用域变量的作用域是指变量的有效范围。局部变量在VBA宏或函数内部定义的变量，只能在定义它的函数或宏范围内可用，这种变量称为局部变量。模块级变量 如果一个变量在模块的开始部分定义，且不包含在任何宏或函数体内，则在本模块内的所有宏或函数中

30、都可以使用它。全局变量 在整个应用程序的各个模块之间都可共用的变量。,10.7 模块与变量的作用域,2、变量的定义方式宏或函数的私有变量，可直接在宏或函数体内定义。在模块的声明段中使用Dim语句声明一个模块级变量。在模块的声明段中（或模块内），使用Public 语句，而非Dim语句来声明一个公共变量。声明方式如下。Public 变量名表 As 数据类型在宏或函数的名称前也可以加上Public关键字，这样的宏或函数就是全局宏（全局函数）。例如，Public Ma,Ya,Ta As Integer。,10.7 模块与变量的作用域,3、变量类型的案例假设有一个教师的档案表（部分数据），如图所示。编写

31、宏将其中的男教师和女教师分别保存在新工作表中。,10.7 模块与变量的作用域,插入两模块，在模块1中的程序如下,Public N声明一个全局变量用于保存教师总人数Public tea(100,5)As Variant声明一个全局数组保存教师的所有数据，Sub readData()Dim i,j As Integer定义两个只能在本宏内部分使用的局部变量Worksheets(教师总表).Activate 激活教师工作表N=1While Not IsEmpty(Cells(N,1)如果教师工作表中的第一列数据非空，就将For i=1 To 5该行的数据读入全局数组中tea(N,i)=Cells(N

32、,i)Next iN=n+1读入一个教师的数据后，教师的人数增加一个WendEnd Sub,10.7 模块与变量的作用域,分离出男教师的宏,Dim i,j,k As IntegerSub man()Sheets.Add 插入一个工作表，在其中保存男教师的数据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

33、 5Cells(k,j)=tea(i,j)将“男”教师的数据从数组读入工作表Next jk=k+1End IfNext iEnd Sub,10.7 模块与变量的作用域,制定调用宏的工作表,10.8 窗体及其应用,窗体的概念窗体是VBA的一种控件，可以用来显示Excel工作表中的数据，也可以用来设计用户输入数据的界面。窗体必须与宏程序结合使用，通过宏可以将窗体与Excel的工作表、自定义菜单、按钮或图形控件结合在一起。,10.8 窗体及其应用,窗体设计案例为上例的教师管理工作薄建立窗体，该窗体可用于显示、修改及输入“教师总表”中的数据。窗体的样式如下图所示。,10.8 窗体及其应用,2、建立窗体

34、的版面（1）打开“教师管理宏”工作薄，进入其VBA编程环境。（2）选择VBA的“插入”|“用户窗体”，会见到图示的设计界面。,10.8 窗体及其应用,窗体设计工具箱,这是绝大多数Wndows编程工具都支持的工具编程工具，一定要掌握其用法！,10.8 窗体及其应用,向窗体添加工具箱中的控件工具,属性对话框中重要的设计工具，它标准而通用，通过它能够轻松地修改与设置窗体中各控件的大小、颜色、名字等。,在窗体中添加一标签控件，然后通过属性对话框修改该控件的背景色、标题内容和字体大小,10.8 窗体及其应用,向窗体中添加标签、编辑框和命令按钮等控件，调整窗体大小和控件的位置，设置各控件的字体、字型、前景

35、和背景等属性，如样图所示。,10.8 窗体及其应用,3、为命令按钮编写事件程序双击要编写事件程序的控件，会见下示编程界面,1、选择要编写事件程序的对象,2、选择事件,3、输入程序代码,10.8 窗体及其应用,部分窗体控件的事件程序窗体初始化事件程序,Private Sub UserForm_Initialize()Worksheets(“教师总表”).Activate 激活教师工作表 n=2 第1次显示工作表“教师总表”的第2行数据 display 调用显示宏，将工作表中的数据显示在窗体的文本框中End Sub,Private Sub 上一位_Click()If n 2 Then n=n 1

36、工作表行号减1，Call display 将工作表的第n行显示在文本框中End Sub,单击“上一位”命令按钮时执行的程序代码,10.8 窗体及其应用,Display过程代码,Sub display()TextBox1.Value=Cells(n,1)将工作表的第1列中的数据显示在TextBox1文本框中TextBox2.Value=Cells(n,2)TextBox3.Value=Cells(n,3)TextBox4.Value=Cells(n,4)TextBox5.Value=Cells(n,5)TextBox6.Value=Cells(n,6)TextBox7.Value=Cells(n

37、,7)TextBox8.Value=Cells(n,8)End Sub,10.8 窗体及其应用,单击退出按钮时执行的程序代码,Private Sub退出_Click()teacher.HideEnd Sub,Private Sub 新教师_Click()While Not(IsEmpty(Cells(n,1)定位到工作表的最后一行 n=n+1 Wend displayEnd Sub,单击“新教师”按钮时执行的程序代码,10.8 窗体及其应用,建立调用窗体的控件,10.9 对话框操作,1 MsgBox函数功能MsgBox对话框函数可用于显示一些简单的信息，可以用该函数向用户显示一些提示信息。函数

38、用法MsgBox(prompt,buttons,title,helpfile,context)其中，prompt是一个字符串，显示在对话框中的消息。buttons是一个常数，指定显示按钮的数目及形式，使用的图标样式。title表示对话框的标题。,10.9 对话框操作,buttons参数的常数值,10.9 对话框操作,对话框的图标常数,10.9 对话框操作,MessageBox函数应用事例一,Sub Msg1()MsgBox 第一次使用对话框 a=Sin(35)b=MsgBox(a,vbOKCancel)End Sub,运行结果,10.9 对话框操作,MessageBox函数应用事例二,Sub

39、Msg2()Dim Msg,Style,Title,Response,MyString Msg=警告，系统有严重错误！+Chr(13)&Chr(10)_+想继续进行下面的操作?Style=vbYesNo+vbCritical 定义按钮。同时显示按钮、图标 Title=警告 定义标题 Response=MsgBox(Msg,Style,Title)调用MsgBox函数 If Response=vbYes Then 用户按下“是”MyString=Yes 完成某操作 Else 用户按下“否”MyString=No 完成某操作 End IfEnd Sub,10.9 对话框操作,2 InputBox函

40、数 功能InputBox函数可以接收用户的输入信息，而且可以将输入信息写入Excel工作表中。用法InputBox(prompt,title,default,xpos,ypos,helpfile,context)其中prompt是出现在输入对话框中的提示信息 title是输入对话框的标题xpos，ypos指定输入文本框中InputBox中的坐标位置 Helpfile、context指定该对话框的帮助信息。,10.9 对话框操作,Input函数应用举例,Sub ss()Worksheets(Sheet1).Activate Dim Message,Titl1,Default,MyValue Me

41、ssage=请输入用户名设置提示信息 Title=用户名设置标题 Default=张三英设置默认值 MyValue=InputBox(Message,Title,Default)用变量作参数调用InputBox函数 pass=InputBox(请输入密码，密码)直接调用InputBox函数输入用户密码 If pass=111111 Then Cells(1,1)=用户名 Cells(1,2)=密码 Cells(2,1)=MyValue Cells(2,2)=pass Else MsgBox(密码不正确，你无权进入本系统)End IfEnd Sub,10.9 对话框操作,Ss函数的运行结果该函数

42、运行时将显示下示两个对话框，将根据用户输入进行用户名与用户密码的判定，然后将结果写入工作表中。,10.10 自定义菜单及自定义工具,1 菜单简介菜单和工具栏可用于执行Excel命令。菜单显示了一系列命令。大多数菜单位于菜单栏中，菜单栏常在屏幕的顶部。在菜单中有各种不同的标记。,暂不可用的菜单,将弹出对话框的菜单,具有相级菜单,下面还有菜单项,10.10 自定义菜单及自定义工具,2 修改系统菜单 Excel的菜单和工具栏可以随时修改系统菜单，如删除其中从不使用的菜单项，增加需要的菜单项，或者在系统菜单中添加自定义菜单项。修改方法是选择“工具”|“自定义”菜单项，在弹出下示对话框后，就可以进行各种

43、修改了。,10.10 自定义菜单及自定义工具,3 在系统菜单中添加自定义菜单 修改的方法参考教材,10.10 自定义菜单及自定义工具,4 用VBA创建自定义菜单 认识菜单的各组成部分,10.10 自定义菜单及自定义工具,建立VBA自定义菜单的步骤，以建立教师管理工作薄的菜单为例1）建立菜单栏MenuBars.Add 教师管理2）在菜单栏中建立菜单MenuBars(教师管理).Menus.Add 学生管理(&S)MenuBars(教师管理).Menus.Add 教师管理(&T)MenuBars(教师管理).Menus.Add 成绩管理(&M)MenuBars(教师管理).Menus.Add 查询

44、(&P)MenuBars(教师管理).Menus.Add 学籍管理(&D)MenuBars(教师管理).Menus.Add 系统维护(&U)MenuBars(教师管理).Menus.Add 帮助系统(&H),10.10 自定义菜单及自定义工具,3）建立菜单项，并指定各菜单项的运行宏设置“学生管理”中的每个菜单项MenuBars(教师管理).Menus(学生管理(&S).MenuItems.Add 新生入学,xsMenuBars(教师管理).Menus(学生管理(&S).MenuItems.Add 降级,jjMenuBars(教师管理).Menus(学生管理(&S).MenuItems.Add-

45、MenuBars(教师管理).Menus(学生管理(&S).MenuItems.Add 处分,cfMenuBars(教师管理).Menus(学生管理(&S).MenuItems.Add 毕业,byMenuBars(教师管理).Menus(学生管理(&S).MenuItems.Add 退出系统,quit“设置“教师管理”中的每个菜单项MenuBars(教师管理).Menus(教师管理(&T).MenuItems.Add 男教师,manMenuBars(教师管理).Menus(教师管理(&T).MenuItems.Add 女教师,womanMenuBars(教师管理).Menus(教师管理(&T)

46、.MenuItems.Add-MenuBars(教师管理).Menus(教师管理(&T).MenuItems.Add 讲师,jsMenuBars(教师管理).Menus(教师管理(&T).MenuItems.Add 教授,jsoMenuBars(教师管理).Menus(教师管理(&T).MenuItems.Add 副教授,fjso,10.10 自定义菜单及自定义工具,Quit宏将删除自定义菜单，恢复系统菜单,Sub quit()For k=1 To MenuBars.Count If MenuBars(k).Caption=教师管理Then For i=MenuBars(教师管理).Menus

47、.Count To 1 Step-1 For j=MenuBars(教师管理).Menus(i)MenuItems.Count To Step-1 MenuBars(教师管理).Menus(i).MenuItems(j).Delete Next j MenuBars(教师管理).Nenus(i).Delete Next i End If Next k MenuBars(教师管理).Delete MenuBars(xlWorksheet).Reset MenuBars(xlWorksheet).ActivateEnd Sub,10.10 自定义菜单及自定义工具,VBA命令建立的菜单,10.11

48、VBA自动宏,VBA提供了Auto_Open和Auto_Close两个非常有用的自动运行宏。Auto_Open将在打开工作簿时被自动执行，这对于许多自动化工作的实现非常有用。Auto_Close会在关闭工作簿时被自动调用。在用Excel创建应用系统时，将退出系统时需要执行的程序代码写在宏Auto_Close中，是一种较好的方法,10.12 VBA应用实例,某公司有7 000多职工，他们的工资管理系统是用VFP编写的，其中的工资数据库如图所示,10.12 VBA应用实例,问题该公司的财务管理人员在每个月末都要对本月和上月的工资数据进行统计分析，并制作出如图所示的财务报表汇报给上级管理人员。,10

49、.12 VBA应用实例,解题思路编写宏将要分析的两个月的从数据库导入到Excel工作表中，并利用宏自动完成各项数据的分析，并形成报表。当每个月需要制作报表时，只需要重新运行宏，就能自动从指定位置导入两月工资，并自动得出分析报告。具体实现思路如下。利用VFP的数据导出功能将本月和上月工资导出到两个VFP的DBF数据表中，将它们保存在某个目录中，如“C:ApsdGZ”目录，名字分别为“本月.dbf”和“上月.dbf”在Excel中，以录制的方式记录两个宏，用于将“C:ApsdGZ”目录中的“本月.dbf”和“上月.dbf”数据表导入到EXCEL中。宏的名字分别为“本月工资”和“上月工资”。编写宏“Sub 分析数据”分析本月工资和上月工资表中的数据，形成需要的分析数据，并保存在数组中。,10.12 VBA应用实例,编写计算百分比的宏“sub 计算百分比”，用于计算两月各项数据对比后的增加百分比。编写输出数据的宏“Sub 输出结果”，用于将保存在数组中的计算结果输出到Excel工作表中。另外，还有一个供其它宏调用的计算人数的宏“计算人数”各宏之间通过数组传递数据。在一个工作表中建立调度上述宏的菜单或命令按钮。,10.12 VBA应用实例,通过宏导入到Excel中的数据,10.12 VBA应用实例,通过宏作出的分析报告,10.12 VBA应用实例,总控工作表,The End,