43个典型ExcelVBA实例.docx

43个典型ExcelVBA实例.docx

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

43个典型ExcelVBA实例

43个典型ExcelVBA实例

目录

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

例2 输入个人信息（Inputbox函数的应用）3

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

例4突出显示不及格学生7

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

例6 评定成绩等级9

例7 计算个人所得税11

例8 密码验证13

例9 求最小公倍数和最大公约数15

例10 输出ASCII码表16

例11 计算选中区域数值之和17

例12 换零钱法（多重循环）18

例13 数据排序21

例14 彩票幸运号码22

例15 用数组填充单元格区域24

例16判断单元格是否包含公式25

例17自动填充公式26

例18锁定和隐藏公式28

例19将单元格公式转换为数值29

例20 删除所有公式29

例21 用VBA表示数组公式30

数据查询31

例22 查找指定的值31

例23 带格式查找33

例24查找上一个/下一个数据34

例25代码转换36

例26 模糊查询37

例27 网上查询快件信息38

例28 查询基金信息40

例29 查询手机所在地41

例30 使用字典查询43

数据排序45

例31 用VBA代码排序45

例32 乱序排序46

例33 自定义序列排序47

例34 多关键字排序49

例35 输入数据自动排序50

例36数组排序51

例37使用Small和Large函数排序52

例38使用RANK函数排序54

例39 姓名按笔画排序56

例40 用VBA进行简单筛选59

例41 用VBA进行高级筛选61

例42筛选非重复值62

例43 取消筛选63

：

例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