43个典型ExcelVBA实例Word文档下载推荐.docx
《43个典型ExcelVBA实例Word文档下载推荐.docx》由会员分享,可在线阅读,更多相关《43个典型ExcelVBA实例Word文档下载推荐.docx(73页珍藏版)》请在冰豆网上搜索。
Debug.Print
EndSub
(3)按功能键“F5”运行子过程,在“立即窗口”输出九九乘法表,如图3-1所示。
例2
输入个人信息(Inputbox函数的应用)
1.案例说明
本例演示Inputbox函数的使用方法。
执行程序,将弹出“输入个人信息”对话框,要求用户输入“姓名、年龄、地址”信息,然后在“立即窗口”中将这些信息打印输出。
2.关键技术
为了实现数据输入,VBA提供了InputBox函数。
该函数将打开一个对话框作为输入数据的界面,等待用户输入数据,并返回所输入的内容。
其语法格式如下:
InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile,context])
各参数的含义如下:
Prompt:
为对话框消息出现的字符串表达式。
其最大长度为1024个字符。
如果需要在对话框中显示多行数据,则可在各行之间用回车符换行符来分隔,一般使用VBA的常数vbCrLf代表回车换行符。
Title:
为对话框标题栏中的字符串。
如果省略该参数,则把应用程序名放入标题栏中。
Default:
为显示在文本框中的字符串。
如果省略该参数,则文本框为空。
Xpos:
应和Ypos成对出现,指定对话框的左边与屏幕左边的水平距离。
如果省略该参数,则对话框会在水平方向居中。
Ypos:
应和Xpos成对出现,指定对话框的上边与屏幕上边的距离。
如果省略该参数,则对话框被放置在屏幕垂直方向距下边大约三分之一的位置。
Helpfile:
设置对话框的帮助文件,可省略。
Context:
设置对话框的帮助主题编号,可省略。
Subinputinfo()
Title="
输入个人信息"
name1="
请输入姓名:
"
age1="
请输入年龄:
address1="
请输入地址:
strName=InputBox(name1,Title)
age=InputBox(age1,Title)
Address=InputBox(addres1,Title)
Debug.Print"
姓名:
strName
年龄:
age
地址:
Address
(3)按功能键“F5”运行子过程,将弹出“输入个人信息”窗口。
在对话框中输入内容后按“回车”,或单击“确定”按钮。
(4)接着输入“年龄”和“地址”信息,在“立即窗口”中将输出这些内容。
例3
退出确认(Msgbox函数的应用)
在应用程序中,有时用户会由于误操作关闭Excel,为了防止这种情况,可在退出Excel之前弹出对话框,让用户确认是否真的要关闭Excel。
本例使用Msgbox函数弹出对话框,让用户选择是否退出系统。
使用MsgBox函数可打开一个对话框,在对话框中显示一个提示信息,并让用户单击对话框中的按钮,使程序继续执行。
MsgBox函数语法格式如下:
Value=MsgBox(prompt[,buttons][,title][,helpfile,context])
通过函数返回值可获得用户单击的按钮,并可根据按钮的不同而选择不同的程序段来执行。
该函数共有5个参数,除第1个参数外,其余参数都可省略。
各参数的意义与Inputbox函数参数的意义基本相同,不同的地方是多了一个buttons参数,用来指定显示按钮的数目及形式、使用提示图标样式、默认按钮以及消息框的强制响应等。
其常数值如表3-1所示。
表3-1
按钮常数值
常
量
值
说
明
vbOkOnly
只显示“确定”(Ok)按钮
vbOkCancel
1
显示“确定”(Ok)及“取消”(Cancel)按钮
vbAbortRetryIgnore
2
显示“异常终止”(Abort)、“重试”(Retry)及“忽略”(Ignore)按钮
vbYesNoCancel
3
显示“是”(Yes)、“否”(No)及“取消”(Cancel)按钮
续表
vbYesNo
4
显示“是”(Yes)及“否”(No)按钮
vbRetryCancel
5
显示“重试”(Retry)及“取消”(Cancel)按钮
vbCritical
16
显示CriticalMessage图标
vbQuestion
32
显示WarningQuery图标
vbExclamation
48
显示WarningMessage图标
vbInformation
64
显示InformationMessage图标
vbDefaultButton1
以第一个按钮为默认按钮
vbDefaultButton2
256
以第二个按钮为默认按钮
vbDefaultButton3
512
以第三个按钮为默认按钮
vbDefaultButton4
768
以第四个按钮为默认按钮
vbApplicationModal
进入该消息框,当前应用程序暂停
vbSystemModal
4096
进入该消息框,所有应用程序暂停
表3-1中的数值(或常数)可分为四组,其作用分别为:
第一组值(0~5)用来决定对话框中按钮的类型与数量。
第二组值(16,32,48,64)用来决定对话框中显示的图标。
第三组值(0,256,512)设置对话框的默认活动按钮。
活动按钮中文字的周转有虚线,按回车键可执行该按钮的单击事件代码。
第四组值(0,4096)决定消息框的强制响应性。
buttons参数可由上面4组数值组成,其组成原则是:
从每一类中选择一个值,把这几个值累加在一起就是buttons参数的值(大部分时间里都只使用前三组数值的组合),不同的组合可得到不同的结果。
(1)在VBE中,双击“工程”子窗口中的“ThisWorkbook”打开代码窗口,如图3-4所示。
(2)在代码窗口左上方的对象列表中选择“Workbook”,如图3-5所示。
(3)在代码窗口右上方的事件列表中选择“BeforeClose”,如图3-6所示。
代码窗口中将自动生成事件过程结构如下:
PrivateSubWorkbook_BeforeClose(CancelAsBoolean)
(4)在上面生成的事件过程中输入以下代码:
DimintReturnAsInteger
intReturn=MsgBox("
真的退出系统吗?
vbYesNo+vbQuestion,"
提示"
)
IfintReturn<
>
vbYesThenCancel=True
(5)保存Excel工作簿。
(6)关闭Excel工作簿。
分支结构,又叫选择结构。
这种结构的程序将根据给定的条件来决定执行哪一部分代码,而跳过其他代码。
例4突出显示不及格学生
本例判断学生成绩表中的成绩,如果成绩不及格(低于60分),则将该成绩着重显示出来。
在本例中,需要进行一个判断(成绩是否低于60分),这时可使用If…Then语句。
用If…Then语句可有条件地执行一个或多个语句。
If逻辑表达式Then
语句1
……
语句n
EndIf
逻辑表达式也可以是任何计算数值的表达式,VBA将为零(0)的数值看做False,而任何非零数值都被看做True。
该语句的功能为:
若逻辑表达式的值是True,则执行位于Then与EndIf之间的语句;
若逻辑表达式的值是False,则不执行Then与EndIf之间的语句,而执行EndIf后面的语句。
其流程图如图3-9所示。
If…Then结构还有一种更简单的形式:
单行结构条件语句。
If逻辑表达式Then语句
若逻辑表达式的值是True,则执行Then后的语句;
若逻辑表达式的值是False,则不执行Then后的语句,而执行下一条语句。
(1)打开“学生成绩表”。
(2)按快捷键“Alt+F11”进入VBE环境。
(3)单击菜单“插入/模块”命令向工程中插入一个模块,并编写以下代码:
Sub显示不及格学生()
DimiAsInteger
Fori=3To11
IfSheets
(1).Cells(i,2).Value<
60Then
Sheets
(1).Cells(i,2).Select
Selection.Font.FontStyle="
加粗"
Selection.Font.ColorIndex=3
EndIf
(4)关闭VBE开发环境返回Excel。
(5)在功能区“开发工具”选项卡的“控件”组中,单击“插入”按钮弹出“表单控件”面板。
(6)在“表单控件”面板中单击“按钮”,拖动鼠标在工作表中绘制一个按钮。
当松开鼠标时,将弹出“指定宏”对话框。
(7)在“指定宏”对话框中,单击选中“显示不及格学生”宏,单击“确定”按钮。
(8)右击工作表中的按钮,弹出快捷菜单如图3-12所示,单击“编辑文字”菜单,修改按钮中的提示文字为“显示不及格学生”。
(9)单击“显示不及格学生”按钮,执行宏代码,成绩表中不及格成绩将突出显示为粗体、红色。
例5
从身份证号码中提取性别
在很多信息系统中都需要使用到身份证号码,身份证号码中包含有很多信息,如可从其中提取性别。
我国现行使用的身份证号码有两种编码规则,即15位居民身份证和18位居民身份证。
15位的身份证号的编码规则。
ddddddyymmddxxp
18位的身份证号的编码规则。
ddddddyyyymmddxxpy
其中:
dddddd为地址码(省地县三级)18位中的和15位中的不完全相同。
yyyymmddyymmdd为出生年月日。
xx序号类编码。
p性别。
18位中末尾的y为校验码。
在If…Then语句中,条件不成立时不执行任何语句。
在很多时候需要根据条件是否成立分别执行两段不同的代码,这时可用If…Then…Else语句,其语法格式如下:
语句序列1
Else
语句序列2
VBA判断“逻辑表达式”的值,如果它为True,将执行“语句序列1”中的各条语句,当“逻辑表达式”的值为False时,就执行“语句序列2”中的各条语句。
其流程图如图3-14所示。
(1)新建Excel工作簿,在VBE中插入一个模块。
(2)在模块中编写以下代码:
Sub根据身份证号码确定性别()
sid=InputBox("
请输入身份证号码:
i=Len(sid)
Ifi<
15Andi<
18Then
'
判断身份证号长度是否正确
MsgBox"
身份证号码只能为15位或18位!
ExitSub
Ifi=15Then
长度为15位
s=Right(sid,1)
取最右侧的数字
Else
长度为18度
s=Mid(sid,17,1)
取倒数第2位数
IfInt(s/2)=s/2Then
为偶数
sex="
女"
Else
男"
性别:
+sex
(3)切换到Excel环境,添加一个按钮“从身份证号码提取性别”,并指定执行上步创建的宏。
(4)单击“从身份证号码提取性别”按钮。
(5)输入身份证号码后单击“确定”按钮。
例6
评定成绩等级
本例将成绩表中的百分制成绩按一定规则划分为A、B、C、D、E五个等级。
其中各等级对应的成绩分别为:
A:
大于等于90分;
B:
大于等于80分,小于90分;
C:
大于等于70分,小于80分;
D:
大于等于60分,小于70分;
E:
小于60分。
本例共有五个分支,使用If…Then…Else这种二路分支结构也可完成,但需要复杂的嵌套结构才能解决该问题。
其实VBA中提供了一种If…Then…ElseIf的多分支结构,其语法格式如下:
If逻辑表达式1Then
语句序列1
ElseIf逻辑表达式2Then
语句序列2.
ElseIf逻辑表达式3Then
语句序列3
...…
语句序列n
在以上结构中,可以包括任意数量的ElseIf子句和条件,ElseIf子句总是出现在Else子句之前。
VBA首先判断“逻辑表达式1”的值。
如果它为False,再判断“逻辑表达式2”的值,依此类推,当找到一个为True的条件,就会执行相应的语句块,然后执行EndIf后面的代码。
如果所有“逻辑表达式”都为False,且包含Else语句块,则执行Else语句块。
(1)在Excel中打开成绩表。
(2)按快捷键“Alt+F11”进入VBE开发环境。
(3)单击“插入/模块”命令向工程中插入一个模块,并编写以下VBA代码:
Sub评定等级()
t=Sheets
(1).Cells(i,2).Value
取得成绩
Ift>
=90Then
j="
A"
ElseIft>
=80Then
B"
=70Then
C"
=60Then
D"
E"
Sheets
(1).Cells(i,3)=j
(4)返回Excel操作界面,在成绩表旁边增加一个按钮,并指定执行宏“评定等级”。
(5)单击“评定等级”按钮,即可在成绩表的C列显示出各成绩对应的等级,如图3-17所示。
例7
计算个人所得税
在工资管理系统中,需要计算员工应缴纳的个人所得税。
个人所得税税额按5%至45%的九级超额累进税率计算应缴税额。
个人所得税的计算公式为:
应纳个人所得税税额=应纳税所得额×
适用税率-速算扣除数
本例根据工资表中的相应数据计算出纳税额,并填充在工资表对应的列中。
本例中计算个人所得税时共有九个分支。
这时可在If…Then…ElseIf结构中添加多个ElseIf块来进行各分支的处理。
对于多分支结构,可使用SelectCase语句。
SelectCase语句的功能与If…Then…Else语句类似,但在多分支结构中,使用SelectCase语句可使代码简洁易读。
SelectCase结构的语法格式如下:
SelectCase测试表达式
Case表达式列表1
Case表达式列表2
…
…
CaseElse
EndSelect
在以上结构中,首先计算出“测试表达式”的值,然后,VBA将表达式的值与结构中的每个Case的值进行比较。
如果相等,就执行与该Case语句下面的语句块,执行完毕再跳转到EndSelect语句后执行。
其流程图如图3-20所示。
在SelectCase结构中,“测试表达式”通常是一个数值型或字符型的变量。
“表达式列表”可以是一个或几个值的列表。
如果在一个列表中有多个值,需要用逗号将各值分隔开。
表达式列表可以按以下几种情况进行书写:
表达式:
表示一些具体的取值。
例如:
Case10,15,25。
表达式ATo表达式B:
表示一个数据范围。
例如,Case7To17表示7~17之间的值。
Is比较运算符表达式:
表示一个范围。
例如,CaseIs>
60表示所有大于90的值。
以上三种情况的混合。
例如,Case4To10,15,Is>
20。
(1)在Excel中打开工资表工作簿。
(3)单击菜单“插入/模块”命令插入一个模块。
(4)在模块中编写以下函数,用来计算所得税:
Function个人所得税(curPAsCurrency)
DimcurTAsCurrency
curP=curP–1600
1600为扣除数
IfcurP>
0Then
SelectCasecurP
CaseIs<
=500
curT=curP*0.05
=2000
curT=(curP-500)*0.1+25
=5000
curT=(curP-2000)*0.15+125
=20000
curT=(curP-5000)*0.2+375
=40000
curT=(curP-20000)*0.25+1375
60000
curT=(curP-40000)*0.3+3375
80000
curT=(curP-60000)*0.35+6375
100000
curT=(curP-80000)*0.4+10375
CaseElse
curT=(curP-100000)*0.45+15375
EndSelect
个人所得税=curT
个人所得税=0
EndFunction
(5)在模块中编写“计算”子过程,计算工资表中每个员工应缴所得税额,并填写在对应的列中。
Sub计算()
Fori=4To9
Sheets
(1).Cells(i,8).Value=个人所得税(Sheets
(1).Cells(i,6).Value)
(6)返回到Excel环境中,在工资表下方插入一个按钮,为按钮指定宏为“计算”。
(7)单击“计算”按钮,可计算出每个员工的所得税额。
在实际开发的应用系统中,经常需要重复执行一条或多条语句。
这种结构称为循环结构。
循环结构的思想是利用计算机高速处理运算的特性,重复执行某一部分代码,以完成大量有规则的重复性运算。
VBA提供了多个循环结构控制语句:
Do…Loop结构、While…Wend结构、For…Next结构、ForEach…Next结构。
例8
密码验证
在信息管理系统中,很多时候都需要用户进行登录操作。
在登录操作时要求用户输入密码,一般都要给用户三次机会,每次的输入过程和判断过程都相同。
本例使用Do…Loop循环完成密码验证过程。
在VBA中,最常用的循环语句是