43个典型ExcelVBA实例.docx

43个典型ExcelVBA实例.docx

《43个典型ExcelVBA实例.docx》由会员分享，可在线阅读，更多相关《43个典型ExcelVBA实例.docx（91页珍藏版）》请在冰豆网上搜索。

43个典型ExcelVBA实例

43个典型ExcelVBA实例

：

例1.九九乘法表（Print方法的应用）

1．案例说明

在早期的Basic版本中，程序运行结果主要依靠Print语句输出到终端。

在VB中，Print作为窗体的一个方法，用来在窗体中显示信息。

但是在VBA中，用户窗体已经不支持Print方法了。

在VBA中，Print方法只能向“立即窗口”中输出程序的运行中间结果，供开发人员调试程序时使用。

本例使用Print方法在立即窗口中输入九九乘法表。

2．关键技术

在VBA中，Print方法只能应用于Debug对象，其语法格式如下：

Debug.Print[outputlist]

参数outputlist是要打印的表达式或表达式的列表。

如果省略，则打印一个空白行。

—   Print首先计算表达式的值，然后输出计算的结果。

在outputlist参数中还可以使用分隔符，以格式化输出的数据。

格式化分隔符有以下几种：

—   Spc（n）：

插入n个空格到输出数据之间；

—   Tab（n）：

移动光标到适当位置，n为移动的列数；

—   分号：

表示前后两个数据项连在一起输出；

—   逗号：

以14个字符为一个输出区，每个数据输出到对应的输出区。

3．编写代码

（1）在VBE中，单击菜单“插入/模块”命令插入一个模块。

（2）在模块中输入以下代码：

Submulti（）

   Fori=1To9

       Forj=1Toi

           Debug.Printi;"x";j;"=";i*j;" ";

       Next

       Debug.Print                                      

   Next

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：

设置对话框的帮助主题编号，可省略。

3．编写代码

（1）在VBE中，单击菜单“插入/模块”命令插入一个模块。

（2）在模块中输入以下代码：

Subinputinfo（）

   Title="输入个人信息"

   name1="请输入姓名：

"

   age1="请输入年龄：

"

   address1="请输入地址：

"

   strName=InputBox（name1,Title）

   age=InputBox（age1,Title）

   Address=InputBox（addres1,Title）

   Debug.Print"姓名：

";strName

   Debug.Print"年龄：

";age

   Debug.Print"地址：

";Address

EndSub

（3）按功能键“F5”运行子过程，将弹出“输入个人信息”窗口。

在对话框中输入内容后按“回车”，或单击“确定”按钮。

（4）接着输入“年龄”和“地址”信息，在“立即窗口”中将输出这些内容。

例3 退出确认（Msgbox函数的应用）

1．案例说明

在应用程序中，有时用户会由于误操作关闭Excel，为了防止这种情况，可在退出Excel之前弹出对话框，让用户确认是否真的要关闭Excel。

本例使用Msgbox函数弹出对话框，让用户选择是否退出系统。

2．关键技术

使用MsgBox函数可打开一个对话框，在对话框中显示一个提示信息，并让用户单击对话框中的按钮，使程序继续执行。

MsgBox函数语法格式如下：

Value=MsgBox（prompt[,buttons][,title][,helpfile,context]）

通过函数返回值可获得用户单击的按钮，并可根据按钮的不同而选择不同的程序段来执行。

该函数共有5个参数，除第1个参数外，其余参数都可省略。

各参数的意义与Inputbox函数参数的意义基本相同，不同的地方是多了一个buttons参数，用来指定显示按钮的数目及形式、使用提示图标样式、默认按钮以及消息框的强制响应等。

其常数值如表3-1所示。

表3-1 按钮常数值

常   量

值

说   明

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）按钮

vbCritical

16

显示CriticalMessage图标

vbQuestion

32

显示WarningQuery图标

vbExclamation

48

显示WarningMessage图标

vbInformation

64

显示InformationMessage图标

vbDefaultButton1

0

以第一个按钮为默认按钮

vbDefaultButton2

256

以第二个按钮为默认按钮

vbDefaultButton3

512

以第三个按钮为默认按钮

vbDefaultButton4

768

以第四个按钮为默认按钮

vbApplicationModal

0

进入该消息框，当前应用程序暂停

vbSystemModal

4096

进入该消息框，所有应用程序暂停

表3-1中的数值（或常数）可分为四组，其作用分别为：

—   第一组值（0～5）用来决定对话框中按钮的类型与数量。

—   第二组值（16，32，48，64）用来决定对话框中显示的图标。

—   第三组值（0，256，512）设置对话框的默认活动按钮。

活动按钮中文字的周转有虚线，按回车键可执行该按钮的单击事件代码。

—   第四组值（0，4096）决定消息框的强制响应性。

buttons参数可由上面4组数值组成，其组成原则是：

从每一类中选择一个值，把这几个值累加在一起就是buttons参数的值（大部分时间里都只使用前三组数值的组合），不同的组合可得到不同的结果。

3．编写代码

（1）在VBE中，双击“工程”子窗口中的“ThisWorkbook”打开代码窗口，如图3-4所示。

（2）在代码窗口左上方的对象列表中选择“Workbook”，如图3-5所示。

（3）在代码窗口右上方的事件列表中选择“BeforeClose”，如图3-6所示。

代码窗口中将自动生成事件过程结构如下：

PrivateSubWorkbook_BeforeClose（CancelAsBoolean）

EndSub

（4）在上面生成的事件过程中输入以下代码：

PrivateSubWorkbook_BeforeClose（CancelAsBoolean）

   DimintReturnAsInteger

   intReturn=MsgBox（"真的退出系统吗？

",vbYesNo+vbQuestion,"提示"）

   IfintReturn<>vbYesThenCancel=True

EndSub

（5）保存Excel工作簿。

（6）关闭Excel工作簿。

分支结构，又叫选择结构。

这种结构的程序将根据给定的条件来决定执行哪一部分代码，而跳过其他代码。

例4突出显示不及格学生

1．案例说明

本例判断学生成绩表中的成绩，如果成绩不及格（低于60分），则将该成绩着重显示出来。

2．关键技术

在本例中，需要进行一个判断（成绩是否低于60分），这时可使用If…Then语句。

用If…Then语句可有条件地执行一个或多个语句。

其语法格式如下：

If逻辑表达式Then

  语句1

  语句1

  ……

  语句n

EndIf

逻辑表达式也可以是任何计算数值的表达式，VBA将为零（0）的数值看做False，而任何非零数值都被看做True。

该语句的功能为：

若逻辑表达式的值是True，则执行位于Then与EndIf之间的语句；若逻辑表达式的值是False，则不执行Then与EndIf之间的语句，而执行EndIf后面的语句。

其流程图如图3-9所示。

If…Then结构还有一种更简单的形式：

单行结构条件语句。

其语法格式如下：

If逻辑表达式Then语句

该语句的功能为：

若逻辑表达式的值是True，则执行Then后的语句；若逻辑表达式的值是False，则不执行Then后的语句，而执行下一条语句。

3．编写代码

（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

   Next

EndSub

（4）关闭VBE开发环境返回Excel。

（5）在功能区“开发工具”选项卡的“控件”组中，单击“插入”按钮弹出“表单控件”面板。

（6）在“表单控件”面板中单击“按钮”，拖动鼠标在工作表中绘制一个按钮。

当松开鼠标时，将弹出“指定宏”对话框。

（7）在“指定宏”对话框中，单击选中“显示不及格学生”宏，单击“确定”按钮。

（8）右击工作表中的按钮，弹出快捷菜单如图3-12所示，单击“编辑文字”菜单，修改按钮中的提示文字为“显示不及格学生”。

（9）单击“显示不及格学生”按钮，执行宏代码，成绩表中不及格成绩将突出显示为粗体、红色。

例5 从身份证号码中提取性别

1．案例说明

在很多信息系统中都需要使用到身份证号码，身份证号码中包含有很多信息，如可从其中提取性别。

我国现行使用的身份证号码有两种编码规则，即15位居民身份证和18位居民身份证。

15位的身份证号的编码规则。

ddddddyymmddxxp

18位的身份证号的编码规则。

ddddddyyyymmddxxpy

其中：

—   dddddd为地址码（省地县三级）18位中的和15位中的不完全相同。

—   yyyymmddyymmdd为出生年月日。

—   xx序号类编码。

—   p性别。

—   18位中末尾的y为校验码。

2．关键技术

在If…Then语句中，条件不成立时不执行任何语句。

在很多时候需要根据条件是否成立分别执行两段不同的代码，这时可用If…Then…Else语句，其语法格式如下：

If逻辑表达式Then

  语句序列1

Else

  语句序列2

EndIf

VBA判断“逻辑表达式”的值，如果它为True，将执行“语句序列1”中的各条语句，当“逻辑表达式”的值为False时，就执行“语句序列2”中的各条语句。

其流程图如图3-14所示。

3．编写代码

（1）新建Excel工作簿，在VBE中插入一个模块。

（2）在模块中编写以下代码：

Sub根据身份证号码确定性别（）

   sid=InputBox（"请输入身份证号码：

"）

   i=Len（sid）

   Ifi<>15Andi<>18Then             '判断身份证号长度是否正确

       MsgBox"身份证号码只能为15位或18位！

"

       ExitSub

   EndIf

   Ifi=15Then                          '长度为15位

       s=Right（sid,1）                    '取最右侧的数字

   Else                                    '长度为18度

       s=Mid（sid,17,1）                  '取倒数第2位数

   EndIf

   IfInt（s/2）=s/2Then              '为偶数

       sex="女"

   Else

       sex="男"

   EndIf

   MsgBox"性别：

"+sex

EndSub

（3）切换到Excel环境，添加一个按钮“从身份证号码提取性别”，并指定执行上步创建的宏。

（4）单击“从身份证号码提取性别”按钮。

（5）输入身份证号码后单击“确定”按钮。

例6 评定成绩等级

1．案例说明

本例将成绩表中的百分制成绩按一定规则划分为A、B、C、D、E五个等级。

其中各等级对应的成绩分别为：

—   A：

大于等于90分；

—   B：

大于等于80分，小于90分；

—   C：

大于等于70分，小于80分；

—   D：

大于等于60分，小于70分；

—   E：

小于60分。

2．关键技术

本例共有五个分支，使用If…Then…Else这种二路分支结构也可完成，但需要复杂的嵌套结构才能解决该问题。

其实VBA中提供了一种If…Then…ElseIf的多分支结构，其语法格式如下：

If逻辑表达式1Then

  语句序列1

ElseIf逻辑表达式2Then

  语句序列2.

ElseIf逻辑表达式3Then

  语句序列3

   ...…

Else

  语句序列n

EndIf

在以上结构中，可以包括任意数量的ElseIf子句和条件，ElseIf子句总是出现在Else子句之前。

VBA首先判断“逻辑表达式1”的值。

如果它为False，再判断“逻辑表达式2”的值，依此类推，当找到一个为True的条件，就会执行相应的语句块，然后执行EndIf后面的代码。

如果所有“逻辑表达式”都为False，且包含Else语句块，则执行Else语句块。

3．编写代码

（1）在Excel中打开成绩表。

（2）按快捷键“Alt+F11”进入VBE开发环境。

（3）单击“插入/模块”命令向工程中插入一个模块，并编写以下VBA代码：

Sub评定等级（）

   DimiAsInteger

   Fori=3To11

       t=Sheets

（1）.Cells（i,2）.Value  '取得成绩

       Ift>=90Then

           j="A"

       ElseIft>=80Then

           j="B"

       ElseIft>=70Then

           j="C"

       ElseIft>=60Then

           j="D"

       Else

           j="E"

       EndIf

       Sheets

（1）.Cells（i,3）=j

   Next

EndSub

（4）返回Excel操作界面，在成绩表旁边增加一个按钮，并指定执行宏“评定等级”。

（5）单击“评定等级”按钮，即可在成绩表的C列显示出各成绩对应的等级，如图3-17所示。

例7 计算个人所得税

1．案例说明

在工资管理系统中，需要计算员工应缴纳的个人所得税。

个人所得税税额按5%至45%的九级超额累进税率计算应缴税额。

个人所得税的计算公式为：

应纳个人所得税税额=应纳税所得额×适用税率-速算扣除数

本例根据工资表中的相应数据计算出纳税额，并填充在工资表对应的列中。

2．关键技术

本例中计算个人所得税时共有九个分支。

这时可在If…Then…ElseIf结构中添加多个ElseIf块来进行各分支的处理。

对于多分支结构，可使用SelectCase语句。

SelectCase语句的功能与If…Then…Else语句类似，但在多分支结构中，使用SelectCase语句可使代码简洁易读。

SelectCase结构的语法格式如下：

SelectCase测试表达式

Case表达式列表1

  语句序列1

Case表达式列表2

  语句序列2

   …  …

CaseElse

  语句序列n

EndSelect

在以上结构中，首先计算出“测试表达式”的值，然后，VBA将表达式的值与结构中的每个Case的值进行比较。

如果相等，就执行与该Case语句下面的语句块，执行完毕再跳转到EndSelect语句后执行。

其流程图如图3-20所示。

在SelectCase结构中，“测试表达式”通常是一个数值型或字符型的变量。

“表达式列表”可以是一个或几个值的列表。

如果在一个列表中有多个值，需要用逗号将各值分隔开。

表达式列表可以按以下几种情况进行书写：

—   表达式：

表示一些具体的取值。

例如：

Case10,15,25。

—   表达式ATo表达式B：

表示一个数据范围。

例如，Case7To17表示7～17之间的值。

—   Is比较运算符表达式：

表示一个范围。

例如，CaseIs>60表示所有大于90的值。

—   以上三种情况的混合。

例如，Case4To10,15,Is>20。

3．编写代码

（1）在Excel中打开工资表工作簿。

（2）按快捷键“Alt+F11”进入VBE开发环境。

（3）单击菜单“插入/模块”命令插入一个模块。

（4）在模块中编写以下函数，用来计算所得税：

Function个人所得税（curPAsCurrency）

   DimcurTAsCurrency

   curP=curP–1600  '1600为扣除数

   IfcurP>0Then

       SelectCasecurP

           CaseIs<=500

               curT=curP*0.05

           CaseIs<=2000

               curT=（curP-500）*0.1+25

           CaseIs<=5000

               curT=（curP-2000）*0.15+125

           CaseIs<=20000

               curT=（curP-5000）*0.2+375

           CaseIs<=40000

               curT=（curP-20000）*0.25+1375

           CaseIs<60000

               curT=（curP-40000）*0.3+3375

           CaseIs<80000

               curT=（curP-60000）*0.35+6375

           CaseIs<100000

               curT=（curP-80000）*0.4+10375

           CaseElse

               curT=（curP-100000）*0.45+15375

       EndSelect

       个人所得税=curT

   Else

       个人所得税=0

   EndIf

EndFunction

（5）在模块中编写“计算”子过程，计算工资表中每个员工应缴所得税额，并填写在对应的列中。

Sub计算（）

   Fori=4To9

       Sheets

（1）.Cells（i,8）.Value=个人所得税（Sheets

（1）.Cells（i,6）.Value）

   Next

EndSub

（6）返回到Excel环境中，在工资表下方插入一个按钮，为按钮指定宏为“计算”。

（7）单击“计算”按钮，可计算出每个员工的所得税额。

在实际开发的应用系统中，经常需要重复执行一条或多条语句。

这种结构称为循环结构。

循环结构的思想是利用计算机高速处理运算的特性，重复执行某一部分代码，以完成大量有规则的重复性运算。

VBA提供了多个循环结构控制语句：

Do…Loop结构、While…Wend结构、For…Next结构、ForEach…Next结构。

例8 密码验证

1．案例说明

在信息管理系统中，很多时候都需要用户进行登录操作。

在登录操作时要求用户输入密码，一般都要给用户三次机会，每次的输入过程和判断过程都相同。

本例使用Do…Loop循环完成密码验证过程。

2．关键技术

在VBA中，最常用的循环