Excel与数据处理（一）：ch10

前言
? 若想使那些枯燥反复的工作变得高效, 准确而
自动化, 请认真学习本章的内容 。
? 若想建立自已的办公自动化数据管理系统, 请
认真学习本章的内容 。
? 若想成为一个真正的 Excel专家, 不但要学习本
章的内容, 而且还不够 ！
本章 学习目标
1,了解 Excel宏的基本知识
2,掌握宏的录制, 编写和运行方法
3,了解 VBA程序的基本知识
4,掌握 VBA的基本数据类型
5,掌握 VBA宏与函数的编写和调用方法
6,掌握 VBA条件, 循环程序的设计方法
7,掌握 VBA窗体的设计方法
8,掌握 VBA的对话框和菜单程序的设计方法
9,了解用 VBA和 EXCEL相结合开发应用程序的方法
10.1 Excel宏
1,宏的概念
? 宏是用户用 VBA程序设计语言编写或录制的程序, 其中保
存有一系列 Excel 的命令, 可以被多次重复使用 。 宏可以
自动执行复杂的任务, 减少完成任务所需的步骤 。
? VBA 即 Visual Basic for Applications, 它是 Visual
Basic的一个派生体, 它有针对性地对 Visual Basic进行
了优化和设置 。 两者的主要区别在于,Visual Basic开发
的应用程序可以独立在 Windows系统中运行, 而用 VBA
开发的程序只能在提供它的应用程序中运行 。
? 在 Excel中, 可以用 Excel提供的宏录制工具录制宏程序,
也可以使用它提供的, Visual Basic 编辑器, 直接编写宏 。
10.1 Excel宏
? 2,录制宏
? 宏录制器是 Excel提供的一种软件工具, 它能够将
用户的操作过程记录下来, 并自动将所记录的操作
转换成为 VBA程序代码 。
? 对于经常重复进行的操作过程, 可以通过宏录制器
将它记录下来, 当需要再次进行这些操作时, 只需
要运行录制的宏, Excel就能自动完成这些重复的
操作 。
? 说明：当录制宏的工作开始后, 所有的操作步骤都
将被记录在宏中, 所以应尽量减少不必要的或错误
的操作, 如果在录制宏时出现失误, 更正失误的操
作也会记录在宏中 。
10.1 Excel宏
? 录制宏的案例
? 建立 10班的学生档案表, 档案表的结构如下图所示 。
录制一个能够建立这种档案表结构的宏 。
10.1 Excel宏
? 建立学生档案宏
1,选择, 工具, |“宏, |“录制新宏, 菜单项, 在弹
出的下示对话框中输入宏名字, 学生档案,
2,单击, 确定,
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） 保存该工作簿为, 学籍档案,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").Select
? 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.FormulaR1C1 = "寝室 "
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 Selection.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,数据类型的概念
? 在计算机中, 数据也是按不同的类别进行运算和保
存的, 人们称之为数据类型 。 同种类型的数据占用
相同大小的存储空间, 相互之间可以进行计算, 比
较或赋值等操作；不同类型的数据占用的存储空间
大小不一定相同, 且相互之间不能进行计算和比较
等操作 。
? 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程序设计基础
? （ 2） 变量
? 变量是在程序运行期间其值可以发生变化的数据 。
? 例如,
1 Dim A,B As Integer
2 A=1
3 B=2
4 A=4+b
5 A=3
A在本程序段中有 3个不同的值，这就是变！
10.2 VBA程序设计基础
? VBA数值数据类型
数据类型 存储空间 数 据 范 围
Byte 1 字节 0~255
Boolean 2字节 True 或 false
Integer 2 字节 -32 768~32 767
Long 4 字节 -2 147 483 648~2 147 483 647
Single 4 字节 负数,-3.402823E38~-1.401298E-45
正数,1.401298E-45~3.402823E38
Double 8 字节 负数,1.79769313486232E308~-4.94065645841247E-324
正数,4.94065645841247E-324~1.79769313486232E308
Currency 8 字节 -922337203685477.5808~922337203685477.5807
Date 8字节 0100年 1月 1日到 9999年 12月 31日
Object 4字节 任何对象引用
String 字符长度 分为定长和可变长度两种，可变长可达 0～ 20亿个字符，定长最多
65536个字符
Variant(数字 ) 16字节 任何数字，最大达到 double的数值范围
Variant(字符 ) 22+字符串长度 约 0～ 20亿
10.2 VBA程序设计基础
? 字符串类型
? 在 VBA中有两种类型的字符串, 变长与定长的字符串 。
Dim s1 As String //变长类型
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=3>5
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的值是 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 To 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程序设计基础
? 对象, 属性和方法
? 计算机程序设计中的对象是从现实世界中抽象出来
的, 它与现实世界中的对象具有相同的含义 。
? 对象具有属性和方法两种特性 。
年龄,30
学历：硕士
身高,173cm
体重,69公斤
说自己的年龄
学习
授课
……
李立
属性
方法
对象属性语法规则
李立,年龄 =32
李立,体重 =70
方法的调用也要按这种语法规则
李立,学习
李立,授课
Excel对象示例
10.3 子程序
1,子程序的两种结构
子程序是 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,43100
3 Call HouseCalc(380950,49500)
4 CircleArea (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 函数结构
Function 函数名 ([p1,p2,p3,? ])[As Type]
??
函数代码
??
函数名 =表达式
End Function
10.4 自定义函数
2,定义函数的注意事项
① 函数由 Function和 End Function 语句所包含起来
的 VBA语句 。
② Function 函数和 Sub子程序很类似, 但函数有一
个返回值 。 Function 函数必须通过表达式调用 。
③ 如果一个 Function 函数没有参数, 它的 Function
语句必须包含一个空的圆括号 。
④ 在函数体中, 函数名至少被赋值一次 。
⑤ 函数开头行的 [As Type]用于指定函数值的返回值
类型, 如果省掉该定义, 被视为 Variant类型 。
10.4 自定义函数
3,【 例 10-1】
? 编写一个计算圆面积的简单函数, 圆半径作为函
数参数 。
Function CircleArea(r As Single) As Single
CircleArea= 3.14*r*r
End Function
10.4 自定义函数
4,函数调用
? 函数只能在表达式中调用 。 在 Excel中, 函数至少
有以下 3种调用方式 。
1,在 Sub子程序中调用函数
2,在其他函数中调用函数
3,在 Excel工作表单元格的公式中调用函数
10.4 自定义函数
5,自定义函数案例
? 假设有一个学生成绩表如图所示 。 现要计算其中的综合
成绩, 在本例中, 综合成绩的计算方法为：综合成绩 =
考试科目 × 0.7+考查科目 × 0.3。
? 编写计算综合成绩的函数 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的 Sub子程序和 Function
函数的执行都是从程序代码的第一个语句行开始,
逐条运行程序代码中的语句, 直到遇到 End Sub或
End Function来结束整个程序的执行 。
? 有些时候, 需要程序代码按一定的条件执行, 当条
件成立的时候, 执行一部分程序代码, 条件不成立
的时候执行另外一部分程序代码 。 这种功能需要用
VBA的选择结构来实现 。
10.5 VBA选择结构
? 2,VBA条件语句的语法
形式 1,
If 条件 Then [语句组 1] [Else 语句组 2]
例如,
If salary>1500 Then rate=0.1 Else rate=0.05
10.5 VBA选择结构
? 形式 2
If 条件 Then
语句组 1
??
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）
之间的关系式为,F=P*S*W*(1-D)。
? 优惠系数 D与路程远近的关系如下, 编写计算折扣
的函数
?
?
?
?
?
?
?
?
?
?
?
?
?
?
1 0 0 0≥S%10
1 0 0 0S≤750%7
750S≤500%5
500S≤250%2
250S0
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 测试表
达式
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 "教授 ","高级工程师 "
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,For… Next循环结构
For counter = start To end [step 步长 ]
循环语句 1
循环语句 2
循环语句 3
??
循环语句 n
Next [counter]
10.6 VBA循环结构
? For循环案例
? 某公司职工档案数据保存在 Excel工作表中, 如下
图所示 。 该公司共有 1 234名职工, 每月要从工资
表中扣除一定的住房公积金, 假设住房公积金按以
下的百分比扣除 。
? 编写计算公积金比例的宏程序 。
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
2000≥S%10
2000S≤1500%7
1500S≤1200%5
1200S≤800%2
800S≤500%1
500S0
D
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 = rate
End Function
数函的例比金积公算计
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位职工的应发工资并填入第 5列中
Next
End Sub
10.6 VBA循环结构
? 调用宏计算公积金
10.6 VBA循环结构
? 2 While… Wend循环结构
? 在事先知道循环次数时, 用 For循环方便 。 如果事先并不知
道循环次数, 但 知 道 执 行 或 结 束 循 环 的 条 件, 则用
Do… Loop循环或 While… Wend循环 。
? While循环的语法结构如下
While 判定条件
语句 1
语句 2
??
Wend
10.6 VBA循环结构
? 3 Do… Loop循环结构
第一种结构为,
Do While 判定条件
??
Loop
第二种结构为,
Do
??
Loop While 判定条件
第三种结构为,
Do Until判定条件
??
Loop
第四种结构为,
Do
??
Loop Until 判定条件
10.6 VBA循环结构
? 案例
? 在上节的 For循环实例中, 假设并不知道有多少位
职工, 在 其 他 条 件 都 相 同 的 条 件 下, 现用
While… Wend循环结构来计算每位职工的住房公积
金比例, 应交公积金和实际发放工资 。
? 改写的函数如下
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 = "Percent" '把公积金比例的格式设置为百分比
Cells(i,4) = r * Cells(i,2) '计算第 1位职工的公积金并填入本行的第
Cells(i,5) = Cells(i,2) - Cells(i,4) '计算第 i位职工的应发工资并填入本行第
列中
i = i + 1 '计算下一位职工的公积金
Wend
End 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) - Cells(i,4)
i = i + 1
Loop Until (IsEmpty(Cells(i,2)))
End Sub
10.6 VBA循环结构
? 4 数组在循环中的应用
? 循环的主要功能是进行 VBA代码的重复执行, 数组
的主要功能是存储大量的数据 。
? 在计算机应用中, 数组和循环相结合能够解决许多
实际问题 。 例如大批量数据的排序, 统计, 检索等
功能 。
? 案例
10.6 VBA循环结构
? 某学校每年对新生都要进行一次英语入学测试, 以了解每
位学生的英语能力 。 考试完毕后, 要统计每个分数段的人
数, 并根据学生的成绩将他们分为 A,B,C…… 若干个班,
第 1~25名同学进入 A班, 第 26~50名同学进入 B班, 其余的
以此类推 。 该校每年招收的人数都有细小的差别 。 假设学
生的英语测试成绩如下页图示 。
?
? 编写一个宏完成以下功能 。
? 自动统计出各分数段的人数, 并将统计结果保存在一个
新的工作表中 。
? 对所有的考试成绩进行排序 。
? 根据排名对学生进行分班, 每个班 25名学生, 每个班保
存在一个新的工作表中 。
10.6 VBA循环结构
? 成绩表
? 本例程序代码较多, 不便 PPT演示, 可直接查看 Excel中的宏 。
10.7 模块与变量的作用域
1,VBA变量的作用域
? 变量的作用域是指变量的有效范围 。
? 局部变量
? 在 VBA宏或函数内部定义的变量, 只能在定义它的函
数或宏范围内可用, 这种变量称为局部变量 。
? 模块级变量
? 如果一个变量在模块的开始部分定义, 且不包含在任
何宏或函数体内, 则在本模块内的所有宏或函数中都
可以使用它 。
? 全局变量
? 在整个应用程序的各个模块之间都可共用的变量 。
10.7 模块与变量的作用域
? 2,变量的定义方式
? 宏或函数的私有变量, 可直接在宏或函数体内定义 。
? 在模块的声明段中使用 Dim语句声明一个模块级变量 。
? 在模块的声明段中 （ 或模块内 ）, 使用 Public 语句, 而非
Dim语句来声明一个公共变量 。 声明方式如下 。
? Public 变量名表 As 数据类型
? 在宏或函数的名称前也可以加上 Public关键字, 这样的宏
或函数就是全局宏 （ 全局函数 ） 。 例如, Public Ma,Ya,
Ta As Integer。
10.7 模块与变量的作用域
? 3,变量类型的案例
? 假设有一个教师的档案表 （ 部分数据 ）, 如图所示 。
编写宏将其中的男教师和女教师分别保存在新工作
表中 。
10.7 模块与变量的作用域
? 插入两模块, 在模块 1中的程序如下
Public N '声明一个全局变量用于保存教师总人数
Public tea(100,5) As Variant '声明一个全局数组保存教师的所有数据,
Sub readData()
Dim i,j As Integer '定义两个只能在本宏内部分使用的局部变量
Worksheets(＂教师总表＂ ).Activate '激活教师工作表
N=1
While Not IsEmpty(Cells (N,1)) '如果教师工作表中的第一列数据非空，就将
For i=1 To 5 '该行的数据读入全局数组中
tea(N,i)=Cells(N,i)
Next i
N=n+1 '读入一个教师的数据后，教师的人数增加一个
Wend
End Sub
10.7 模块与变量的作用域
? 分离出男教师的宏
Dim i,j,k As Integer
Sub man()
Sheets.Add '插入一个工作表，在其中保存男教师的数据
Active Sheet.Name=＂男教师＂ '修改工作表的名称为, 男教师,
For j=1 To 5
Cells(1,j)=tea(1,j) '读入第一行数据即表头到二维数组的第一行
Next j
k=2 'k统计男教师的人数 '
For i=2 To N '从数组的第 2行开始分析数据
If tea(i,3)=＂男＂ Then '如果数组的第 3列中的数据为, 男,
For j=1 To 5
Cells(k,j)=tea(i,j) '将, 男, 教师的数据从数组读入工作表
Next j
k=k+1
End If
Next i
End Sub
10.7 模块与变量的作用域
? 制定调用宏的工作表
10.8 窗体及其应用
1,窗体的概念
? 窗体是 VBA的一种控件, 可以用来显示 Excel工作
表中的数据, 也可以用来设计用户输入数据的界
面 。
? 窗体必须与宏程序结合使用, 通过宏可以将窗体
与 Excel的工作表, 自定义菜单, 按钮或图形控件
结合在一起 。
10.8 窗体及其应用
? 窗体设计案例
? 为上例的教师管理工作薄建立窗体, 该窗体可用于
显示, 修改及输入, 教师总表, 中的数据 。 窗体的
样式如下图所示 。
10.8 窗体及其应用
2,建立窗体的版面
（ 1） 打开,教师管理宏, 工作薄, 进入其 VBA编程环境 。
（ 2） 选择 VBA的, 插入, |“用户窗体,, 会见到图示的设
计界面 。
10.8 窗体及其应用
? 窗体设计工具箱
这是绝大多数 Wndows
编程工具都支持的工具
编程工具，一定要掌握
其用法！
10.8 窗体及其应用
? 向窗体添加工具箱中的控件工具
属性对话框中重要的设计
工具，它标准而通用，通
过它能够轻松地修改与设
置窗体中各控件的大小、
颜色、名字等。
在窗体中添加一 标签 控
件，然后通过属性对话
框修改该控件的背景色、
标题内容和字体大小
10.8 窗体及其应用
? 向窗体中添加标签, 编辑框和命令按钮等控件, 调
整窗体大小和控件的位置, 设置各控件的字体, 字
型, 前景和背景等属性, 如样图所示 。
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 '工作表行号减 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,7)
TextBox8.Value=Cells(n,8)
End Sub
10.8 窗体及其应用
? 单击退出按钮时执行的程序代码
Private Sub退出 _Click()
teacher.Hide
End Sub
Private Sub 新教师 _Click()
While Not (IsEmpty(Cells(n,1))) '定位到工作表
的最后一行
n=n+1
Wend
display
End Sub
? 单击, 新教师, 按钮时执行的程序代码
10.8 窗体及其应用
? 建立调用窗体的控件
10.9 对话框操作
? 1 MsgBox函数
? 功能
? MsgBox对话框函数可用于显示一些简单的信息, 可以用
该函数向用户显示一些提示信息 。
? 函数用法
? MsgBox(prompt[,buttons] [,title] [,helpfile,context])
? 其中, prompt是一个字符串, 显示在对话框中的消息 。
? buttons是一个常数, 指定显示按钮的数目及形式, 使用
的图标样式 。
? title表示对话框的标题 。
10.9 对话框操作
? buttons参数的常数值
按钮选项的常数 代表的数值 功 能
vbOKOnly 0 只显示 OK 按钮
VbOKCancel 1 显示 OK 及 Cancel 按钮
VbAbortRetryIgnore 2 显示 Abort,Retry 及 Ignore 按钮
VbYesNoCancel 3 显示 Yes,No 及 Cancel 按钮
VbYesNo 4 显示 Yes 及 No 按钮
VbRetryCancel 5 显示 Retry 及 Cancel 按钮
10.9 对话框操作
? 对话框的图标常数
按钮常数 代表的数值 功 能
VbRetryCancel 5 显示 Retry 及 Cancel 按钮
VbCritical 16 显示 Critical Message 图标
VbQuestion 32 显示 Warning Query 图标
VbExclamation 48 显示 Warning Message 图标
VbInformation 64 显示 Information Message 图标
10.9 对话框操作
? MessageBox函数应用事例一
Sub Msg1()
MsgBox "第一次使用对话框 "
a = Sin(35)
b = MsgBox(a,vbOKCancel)
End Sub
运行结果
10.9 对话框操作
? MessageBox函数应用事例二
Sub 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 If
End Sub
10.9 对话框操作
? 2 InputBox函数
? 功能
? 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
Message= " 请输入用户名 " '设置提示信息
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 If
End Sub
10.9 对话框操作
? Ss函数的运行结果
? 该函数运行时将显示下示两个对话框, 将根据用户
输入进行用户名与用户密码的判定, 然后将结果写
入工作表中 。
10.10 自定义菜单及自定义工具
? 1 菜单简介
? 菜单和工具栏可用于执行 Excel命令 。 菜单显示了一系列命
令 。 大多数菜单位于菜单栏中, 菜单栏常在屏幕的顶部 。
在菜单中有各种不同的标记 。
暂不可用的菜单
将弹出对话框的菜单
具有相级菜单
下面还有菜单项
10.10 自定义菜单及自定义工具
? 2 修改系统菜单
? Excel的菜单和工具栏可以随时修改系统菜单, 如
删除其中从不使用的菜单项, 增加需要的菜单项,
或者在系统菜单中添加自定义菜单项 。
? 修改方法是选择, 工具, |“自定义, 菜单项, 在弹
出下示对话框后, 就可以进行各种修改了 。
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 "查询 (&P)"
? MenuBars("教师管理 ").Menus.Add "学籍管理 (&D)"
? MenuBars("教师管理 ").Menus.Add "系统维护 (&U)"
? MenuBars("教师管理 ").Menus.Add "帮助系统 (&H)"
10.10 自定义菜单及自定义工具
3）, 建立菜单项, 并指定各菜单项的运行宏
'设置, 学生管理, 中的每个菜单项
MenuBars("教师管理 ").Menus("学生管理 (&S)").MenuItems.Add "新生入学 ","xs"
MenuBars("教师管理 ").Menus("学生管理 (&S)").MenuItems.Add "降级 ","jj"
MenuBars("教师管理 ").Menus("学生管理 (&S)").MenuItems.Add "-"
MenuBars("教师管理 ").Menus("学生管理 (&S)").MenuItems.Add "处分 ","cf"
MenuBars("教师管理 ").Menus("学生管理 (&S)").MenuItems.Add "毕业 ","by"
MenuBars("教师管理 ").Menus("学生管理 (&S)").MenuItems.Add "退出系统 ","quit,
'设置, 教师管理, 中的每个菜单项
MenuBars("教师管理 ").Menus("教师管理 (&T)").MenuItems.Add "男教师 ","man"
MenuBars("教师管理 ").Menus("教师管理 (&T)").MenuItems.Add "女教师 ","woman"
MenuBars("教师管理 ").Menus("教师管理 (&T)").MenuItems.Add "-"
MenuBars("教师管理 ").Menus("教师管理 (&T)").MenuItems.Add "讲师 ","js"
MenuBars("教师管理 ").Menus("教师管理 (&T)").MenuItems.Add "教授 ","jso"
MenuBars("教师管理 ").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.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).Activate
End Sub
10.10 自定义菜单及自定义工具
? VBA命令建立的菜单
10.11 VBA自动宏
? VBA提供了 Auto_Open和 Auto_Close两个非常有用
的自动运行宏 。
? Auto_Open将在打开工作簿时被自动执行, 这对于
许多自动化工作的实现非常有用 。
? Auto_Close会在关闭工作簿时被自动调用 。 在用
Excel创建应用系统时, 将退出系统时需要执行的
程序代码写在宏 Auto_Close中, 是一种较好的方法
10.12 VBA应用实例
? 某公司有 7 000多职工, 他们的工资管理系统
是用 VFP编写的, 其中的工资数据库如图所示
10.12 VBA应用实例
? 问题
? 该公司的财务管理人员在每个月末都要对本月和上
月的工资数据进行统计分析, 并制作出如图所示的
财务报表汇报给上级管理人员 。
10.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