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 编辑器”直接编
3、写宏。,10.1 Excel宏,2、 录制宏 宏录制器是Excel提供的一种软件工具,它能够将用户的操作过程记录下来,并自动将所记录的操作转换成为VBA程序代码。 对于经常重复进行的操作过程,可以通过宏录制器将它记录下来,当需要再次进行这些操作时,只需要运行录制的宏,Excel就能自动完成这些重复的操作。 说明:当录制宏的工作开始后,所有的操作步骤都将被记录在宏中,所以应尽量减少不必要的或错误的操作,如果在录制宏时出现失误,更正失误的操作也会记录在宏中。,10.1 Excel宏,录制宏的案例 建立10班的学生档案表,档案表的结构如下图所示。录制一个能够建立这种档案表结构的宏。,10.1 Exc
4、el宏,建立学生档案宏 选择“工具”|“宏”|“录制新宏”菜单项,在弹出的下示对话框中输入宏名字“学生档案” 单击“确定”,10.1 Excel宏,3.输入表格内容 (1)单击A1单元格,在其中输入“2001级学生档案”。 (2)单击A2单元格,在其中输入“学号”。 (3)单击B2单元格,在其中输入“姓名”。 (4)单击C2单元格,在其中输入“班级”。 (5)单击D2单元格,在其中输入“性别”。 (6)单击E2单元格,在其中输入“籍贯”。 (7)单击F2单元格,在其中输入“寝室”。 (8)单击G2单元格,在其中输入“电话号码”。 (9)选择A1:G1单元格区域,然后单击工具条中的跨列居中按钮。
5、 (10)单击“停止”记录制工具条中的停止按钮。 (11)保存该工作簿为“学籍档案.xls”。,10.1 Excel宏,4.停止录制,查看录制的宏 选择“工具”|“宏”|“停止录制” 选择“工具”|“宏”|“宏”菜单项,会显示“宏”对话框。选中其中的“学生档案”,单击“编辑” 。,10.1 Excel宏,宏代码 1 Sub 学生档案() 2 学生档案 Macro 3 宏由 dk 录制,时间: 2004-7-18 4 快捷键: Ctrl+s 5 Range(“A1“).Select 6 ActiveCell.FormulaR1C1 = “2001级一班学生档案“ 7 Range(“A2“).Se
6、lect 8 ActiveCell.FormulaR1C1 = “学号“ 9 Range(“B2“).Select 10 ActiveCell.FormulaR1C1 = “姓名“ 11 Range(“C2“).Select 12 ActiveCell.FormulaR1C1 = “班级“ 13 Range(“D2“).Select 14 ActiveCell.FormulaR1C1 = “性别“ 15 Range(“E2“).Select 16 ActiveCell.FormulaR1C1 = “籍贯“ 17 Range(“F2“).Select 18 ActiveCell.FormulaR
7、1C1 = “寝室“,10.1 Excel宏,19 Range(“G2“).Select 20 ActiveCell.FormulaR1C1 = “电话号码“ 21 Range(“A1:G1“).Select 22 With Selection 23 .HorizontalAlignment = xlCenter 24 .VerticalAlignment = xlBottom 25 .WrapText = False 26 .Orientation = 0 27 .AddIndent = False 28 .ShrinkToFit = False 29 End With 30 Selecti
8、on.Merge 31 End Sub,10.1 Excel宏,5、宏的结构 Sub 宏名( ) 命令代码1 命令代码2 End Sub,6、With的结构 With Selection End With,10.1 Excel宏,7、 宏的保存 保存在个人宏工作簿“Personal.xls”中。 保存在专门保存宏的“新的工作簿”中。 保存在建立宏的当前工作薄中。,10.1 Excel宏,8、宏的执行 通过快捷键运行宏 通过对话框运行宏 通过自定义工具按钮运行宏 通过图形控件或窗体命令按钮运行宏 通过自定义菜单运行宏(以后再讲),10.2 VBA程序设计基础,1、数据类型的概念 在计算机中,数据
9、也是按不同的类别进行运算和保存的,人们称之为数据类型。同种类型的数据占用相同大小的存储空间,相互之间可以进行计算、比较或赋值等操作;不同类型的数据占用的存储空间大小不一定相同,且相互之间不能进行计算和比较等操作 。 2、VBA的数据类型 (1)常量 数值常量 ,如:1,2,65,98.65等,10.2 VBA程序设计基础,字符及字符串常量 字符类型的常量称为字符常量,字符常量要用定界符双引号(“ ”)界定。例如,“d”、“5”、“A”等都是字符常量。 符号常量 Const 常量名=常量值 例如,Const PI=3.14,Const ABC=“OK ! China! “,10.2 VBA程序设
10、计基础,(2)变量 变量是在程序运行期间其值可以发生变化的数据。 例如:,1 Dim A,B As Integer 2 A=1 3 B=2 4 A=4+b A=3 A在本程序段中有3个不同的值,这就是变!,10.2 VBA程序设计基础,VBA数值数据类型,10.2 VBA程序设计基础,字符串类型 在VBA中有两种类型的字符串,变长与定长的字符串。,Dim s1 As String /变长类型 Dim s2 As String *10 /定长类型 s1=“dddkdk “ s2=“d1234567890sssss “,10.2 VBA程序设计基础,布尔类型 布尔类型是比较运算或逻辑运算的结果值,
11、它只有两个取值: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 到 23:59:59。 日期文字以“#”作界定符。,Dim d1,d2 As Date d1=# 1 Jul 98 # D1的取值是1998年7月1号 d2=# 12/2/2000 # D2的值是
12、2000年12月2号,10.2 VBA程序设计基础,变体数据类型 Variant 是一种特殊的数据类型,除了定长 String 数据及用户定义类型外,它可以包含任何种类的数据。,Dim ar As Variant ar = 12 ar = “string type“ ar = “abc“ & ar ar = 12.23 在本例中,ar的类型是不定的!,10.2 VBA程序设计基础,数组 在VBA中,可以声明一个数组来代表一组具有相同数据类型的数据,它就是数组。,假设一个班有20个同学,每个同学有5门课程,可以定义一个20行5列的二维数组来保存他们的成绩 Dim stu(1 To 20, 1 T
13、o 5) As Single 这条命令定义了一个二维表格,如下所示。,stu(1,1)=78 stu(1,2)=90 stu(1,3)=87 stu(1,4)=88 stu(1,5)=76,数组访问方法,78,10.2 VBA程序设计基础,对象、属性和方法 计算机程序设计中的对象是从现实世界中抽象出来的,它与现实世界中的对象具有相同的含义。 对象具有属性和方法两种特性。,对象属性语法规则 李立.年龄=32 李立.体重=70 方法的调用也要按这种语法规则 李立.学习 李立.授课,Excel对象示例,10.3 子程序,子程序的两种结构 子程序是VBA的最小程序单位,它必须独立存在,但在一个子程序中
14、可以调用另外一个子程序。它有两种形式,第一种没有参数,第二种有参数,Sub 子程序名 子程序代码 End Sub,Sub 子程序名(p1, p2, p3) 子程序代码 End Sub,10.3 子程序,2、子程序的调用形式 1)直接调用 直接调用子程序名,如果有参数,则在子程序后面直接写上调用参数; 2)用Call命令调用 在Call命令的后面写上了程序的名字,如果子程序有参数,则必须将参数写在括号中。,10.3 子程序,子程序调用举例,1 Sub Main() 2 HouseCalc 99800, 43100 3 Call HouseCalc(380950, 49500) 4 CircleA
15、rea (4) 5 CircleArea 4 6 Message 7 End Sub,Sub HouseCalc(price As Single, wage As Single) If 3*wage = 0.85 * price Then MsgBox “你的薪水不能承担房价!“ Else MsgBox “你的薪金足以承担房价!“ End If End Sub,Sub CircleArea(R as Single) MsgBox 3.14*R*R End sub,Sub Message() MsgBox “这是一个无参子程序“ End Sub,调用,10.4 自定义函数,1 函数结构,Func
16、tion 函数名(p1, p2, p3, )As Type 函数代码 函数名=表达式 End Function,10.4 自定义函数,2、定义函数的注意事项 函数由Function和End Function 语句所包含起来的 VBA语句。 Function 函数和 Sub子程序很类似,但函数有一个返回值。Function 函数必须通过表达式调用。 如果一个 Function 函数没有参数,它的 Function 语句必须包含一个空的圆括号。 在函数体中,函数名至少被赋值一次。 函数开头行的As Type用于指定函数值的返回值类型,如果省掉该定义,被视为Variant类型。,10.4 自定义函数
17、,3、【例10-1】 编写一个计算圆面积的简单函数,圆半径作为函数参数。 Function CircleArea(r As Single) As Single CircleArea= 3.14*r*r End Function,10.4 自定义函数,4、函数调用 函数只能在表达式中调用。在Excel中,函数至少有以下3种调用方式。 在Sub子程序中调用函数 在其他函数中调用函数 在Excel工作表单元格的公式中调用函数,10.4 自定义函数,5、自定义函数案例 假设有一个学生成绩表如图所示。现要计算其中的综合成绩,在本例中,综合成绩的计算方法为:综合成绩=考试科目0.7+考查科目0.3。 编写
18、计算综合成绩的函数ss。,10.4 自定义函数,计算综合成绩的自定义函数 Function ss(ks1, ks2, kc1, kc2, s, c) ss = (ks1 + ks2) * s + (kc1 + kc2) * c End Function 该函数定义出来之后,可在编写它的任何工作表中调用,与调用Excel内置函数无任何差别。比如,在上图的G5中输入公式 =ss(C5,D5,E5,F5,$E$2,$E$3) 然后把该公式向下填充复制到最后一位同学的“综合成绩”单元格,这样就可计算出所有同学的综合成绩。,10.5 VBA选择结构,1、VBA程序执行的方式 在一般情况下,一个VBA的S
19、ub子程序和Function函数的执行都是从程序代码的第一个语句行开始,逐条运行程序代码中的语句,直到遇到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
20、 Else 语句组2 End If,Function abc (a, b) If a b Then t = a a = b b = t End If abc=a End Function,例如,10.5 VBA选择结构,形式3,If 条件1 Then 语句组1 ElseIf 条件2 Then 语句组2 ElseIf 条件n Then 语句组n Else 语句组n+1 End If,10.5 VBA选择结构,3、条件函数案例 某汽车出租公司可为顾客提供运送货物的业务,根据货物的重量及路程可对运费进行适当的优惠。设运费F(单位为元),重量P(单位为吨),路程S(公里)及优惠系数(D)之间的关系式为
21、:F=P*S*W*(1-D)。 优惠系数D与路程远近的关系如下,编写计算折扣的函数,10.5 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 If End Function,10.5 VBA选择结构,调用自定义函数计算,10.5 VBA选择结构,4 分情况选择语句 Select Case语句的语法结构如下。,Select Case
22、测试表达式 Case 表达式1 语句组1 Case 表达式2 语句组2 Case Else 语句组n End Select,10.5 VBA选择结构,Select 案例 某学校的职工人事数据存在Excel工作表中,如图所示。现在,要按职称提升每位职工的工资,各种职称的工资增长情况如下:教授150、副教授130、讲师100、助教80、高级工程师150、工程师140、助工90。 用select语句编写计算增加工资的 函数。,10.5 VBA选择结构,编写的Seclect 函数,Function AddSalary(职称) 职称作为一个参数 Select Case 职称 Case “教授“, “高级
23、工程师“ AddSalary = 150 Case “副教授“ AddSalary = 130 Case “讲师“ AddSalary = 100 Case “助教“ AddSalary = 80 Case “工程师“ AddSalary = 140 Case “助工“ AddSalary = 90 End Select End Function,10.6 VBA循环结构,在计算机中,一些被重复执行的语句是通过循环来完成的。 1、 ForNext循环结构,For counter = start To end step 步长 循环语句1 循环语句2 循环语句3 循环语句n Next counte
24、r,10.6 VBA循环结构,For循环案例 某公司职工档案数据保存在Excel工作表中,如下图所示。该公司共有1 234名职工,每月要从工资表中扣除一定的住房公积金,假设住房公积金按以下的百分比扣除。 编写计算公积金比例的宏程序。,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.
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 使用 10
链接地址:https://www.31doc.com/p-3470336.html