43个典型ExcelVBA实例精编版

43个典型ExcelVBA实例目录例1.九九乘法 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf （Print方法的应用）3例2输入个人信息（Inputbox函数的应用）3例3退出确认（Msgbox函数的应用）5例4突出显示不及格学生7例5从身份证号码中提取性别8例6评定成绩等级10例7计算个人所得税11例8密码验证13例9求最小公倍数和最大公约数15例10输出ASCII码表16例11计算选中区域数值之和17例12换零钱法（多重循环）18例13数据排序21例14彩票幸运号码22例15用数组填充单元格区域24例16判断单元格是否包含公式26例17自动填充公式26例18锁定和隐藏公式28例19将单元格公式转换为数值29例20删除所有公式29例21用VBA表示数组公式301数据查询31例22查找指定的值31例23带 格式 pdf格式笔记格式下载页码格式下载公文格式下载简报格式下载 查找34例24查找上一个/下一个数据34例25代码转换36例26模糊查询37例27网上查询快件信息39例28查询基金信息41例29查询手机所在地42例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取消筛选632：例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=1To9Forj=1ToiDebug.Printi;"x";j;"=";i*j;"";NextDebug.PrintNextEndSub（3）按功能键“F5”运行子过程，在“立即窗口”输出九九乘法表，如图3-1所示。例2输入个人信息（Inputbox函数的应用）1．案例说明本例演示Inputbox函数的使用方法。执行程序，将弹出“输入个人信息”对话框，要求用户输入“姓名、年龄、地址”信息，然后在“立即窗口”中将这些信息打印输出。2．关键技术3为了实现数据输入，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"姓名：";strNameDebug.Print"年龄：";ageDebug.Print"地址：";AddressEndSub3）按功能键“F5”运行子过程，将弹出“输入个人信息”窗口。在对话框中输入内容后按“回车”，或单击“确定”按钮。4）接着输入“年龄”和“地址”信息，在“立即窗口”中将输出这些内容。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按钮常数值常量值说明vbOkOnly0只显示“确定”（Ok）按钮vbOkCancel1显示“确定”（Ok）及“取消”（Cancel）按钮vbAbortRetryI2显示“异常终止”（Abort）、“重试”（Retry）及“忽略”（Ignoregnore）按钮vbYesNoCanc3显示“是”（Yes）、“否”（No）及“取消”（Cancel）按钮el续表常量值说明vbYesNo4显示“是”（Yes）及“否”（No）按钮vbRetryCancel5显示“重试”（Retry）及“取消”（Cancel）按钮vbCritical1显示CriticalMessage图标6vbQuestion3显示WarningQuery图标2vbExclamation4显示WarningMessage图标8vbInformation6显示InformationMessage图标54vbDefaultButto0以第一个按钮为默认按钮n1vbDefaultButto2以第二个按钮为默认按钮n256vbDefaultButto5以第三个按钮为默认按钮n312vbDefaultButto7以第四个按钮为默认按钮n468vbApplication0进入该消息框，当前应用程序暂停ModalvbSystemModa4进入该消息框，所有应用程序暂停l096表3-1中的数值（或常数）可分为四组，其作用分别为：—第一组值（0～5）用来决定对话框中按钮的类型与数量。—第二组值（16，32，48，64）用来决定对话框中显示的图标。—第三组值（0，256，512）设置对话框的默认活动按钮。活动按钮中文字的周转有虚线，按回车键可执行该按钮的单击事件代码。—第四组值（0，4096）决定消息框的强制响应性。buttons参数可由上面4组数值组成，其组成原则是：从每一类中选择一个值，把这几个值累加在一起就是buttons参数的值（大部分时间里都只使用前三组数值的组合），不同的组合可得到不同的结果。3．编写代码（）在中，双击“工程”子窗口中的“ThisWorkbook”打开代码窗口，如图3-41VBE所示。（2）在代码窗口左上方的对象列表中选择“Workbook”，如图3-5所示。（）在代码窗口右上方的事件列表中选择“BeforeClose”，如图3-6所示。代码窗口中3将自动生成事件过程结构如下：PrivateSubWorkbook_BeforeClose(CancelAsBoolean)EndSub（4）在上面生成的事件过程中输入以下代码：PrivateSubWorkbook_BeforeClose(CancelAsBoolean)DimintReturnAsIntegerintReturn=MsgBox("真的退出系统吗？",vbYesNo+vbQuestion,"提示")6IfintReturn<>vbYesThenCancel=TrueEndSub5）保存Excel工作簿。6）关闭Excel工作簿。分支结构，又叫选择结构。这种结构的程序将根据给定的条件来决定执行哪一部分代码，而跳过其他代码。例4突出显示不及格学生1．案例说明本例判断学生成绩表中的成绩，如果成绩不及格（低于60分），则将该成绩着重显示出来。2．关键技术在本例中，需要进行一个判断（成绩是否低于60分），这时可使用IfThen语句。用IfThen语句可有条件地执行一个或多个语句。其语法格式如下：If逻辑表达式Then语句1语句1语句nEndIf逻辑表达式也可以是任何计算数值的表达式，VBA将为零（）的数值看做，而0False任何非零数值都被看做True。该语句的功能为：若逻辑表达式的值是True，则执行位于Then与EndIf之间的语句；若逻辑表达式的值是，则不执行Then与EndIf之间的语句，而执行EndIf后面的语句False。其流程图如图3-9所示。IfThen结构还有一种更简单的形式：单行结构条件语句。其语法格式如下：If逻辑表达式Then语句该语句的功能为：若逻辑表达式的值是True，则执行Then后的语句；若逻辑表达式的值是False，则不执行Then后的语句，而执行下一条语句。3．编写代码1）打开“学生成绩表”。2）按快捷键“Alt+F11”进入VBE环境。3）单击菜单“插入/模块”命令向工程中插入一个模块，并编写以下代码：Sub显示不及格学生()DimiAsInteger7Fori=3To11IfSheets(1).Cells(i,2).Value<60ThenSheets(1).Cells(i,2).SelectSelection.Font.FontStyle="加粗"Selection.Font.ColorIndex=3EndIfNextEndSub4）关闭VBE开发环境返回Excel。5）在功能区“开发工具”选项卡的“控件”组中，单击“插入”按钮弹出“表单控件”面板。6）在“表单控件”面板中单击“按钮”，拖动鼠标在工作表中绘制一个按钮。当松开鼠标时，将弹出“指定宏”对话框。7）在“指定宏”对话框中，单击选中“显示不及格学生”宏，单击“确定”按钮。（）右击工作表中的按钮，弹出快捷菜单如图3-12所示，单击“编辑文字”菜单，修8改按钮中的提示文字为“显示不及格学生”。9）单击“显示不及格学生”按钮，执行宏代码，成绩表中不及格成绩将突出显示为粗体、红色。例5从身份证号码中提取性别1．案例说明在很多信息系统中都需要使用到身份证号码，身份证号码中包含有很多信息，如可从其中提取性别。我国现行使用的身份证号码有两种编码规则，即15位居民身份证和18位居民身份证。位的身份证号的编码规则。ddddddyymmddxxp位的身份证号的编码规则。ddddddyyyymmddxxpy其中：—dddddd为地址码（省地县三级）18位中的和15位中的不完全相同。—yyyymmddyymmdd为出生年月日。—xx序号类编码。—p性别。—18位中末尾的y为校验码。2．关键技术在IfThen语句中，条件不成立时不执行任何语句。在很多时候需要根据条件是否成8立分别执行两段不同的代码，这时可用IfThenElse语句，其语法格式如下：If逻辑表达式Then语句序列1Else语句序列2EndIfVBA判断“逻辑表达式”的值，如果它为True，将执行“语句序列1”中的各条语句，当“逻辑表达式的值为False时，就执行语句序列中的各条语句。其流程图如图3-14所示。”“2”3．编写代码1）新建Excel工作簿，在VBE中插入一个模块。2）在模块中编写以下代码：Sub根据身份证号码确定性别()sid=InputBox("请输入身份证号码：")i=Len(sid)Ifi<>15Andi<>18Then'判断身份证号长度是否正确MsgBox"身份证号码只能为15位或18位！"ExitSubEndIfIfi=15Then'长度为15位s=Right(sid,1)'取最右侧的数字Else长度为18度's=Mid(sid,17,1)'取倒数第2位数EndIfIfInt(s/2)=s/2Then'为偶数sex="女"Elsesex="男"EndIfMsgBox"性别："+sexEndSub3）切换到Excel环境，添加一个按钮“从身份证号码提取性别”，并指定执行上步创建的宏。4）单击“从身份证号码提取性别”按钮。5）输入身份证号码后单击“确定”按钮。9例6评定成绩等级1．案例说明本例将成绩表中的百分制成绩按一定规则划分为A、B、C、D、E五个等级。其中各等级对应的成绩分别为：—A：大于等于90分；—B：大于等于80分，小于90分；—C：大于等于70分，小于80分；—D：大于等于60分，小于70分；—E：小于60分。2．关键技术本例共有五个分支，使用IfThenElse这种二路分支结构也可完成，但需要复杂的嵌套结构才能解决该问题。其实VBA中提供了一种IfThenElseIf的多分支结构，其语法格式如下：If逻辑表达式1Then语句序列1ElseIf逻辑表达式2Then语句序列2.ElseIf逻辑表达式3Then语句序列3...Else语句序列nEndIf在以上结构中，可以包括任意数量的ElseIf子句和条件，ElseIf子句总是出现在Else子句之前。VBA首先判断“逻辑表达式1”的值。如果它为False，再判断“逻辑表达式2”的值，依此类推，当找到一个为True的条件，就会执行相应的语句块，然后执行EndIf后面的代码。如果所有“逻辑表达式”都为，且包含Else语句块，则执行Else语句块。False3．编写代码1）在Excel中打开成绩表。2）按快捷键“Alt+F11”进入VBE开发环境。（3）单击“插入/模块”命令向工程中插入一个模块，并编写以下VBA代码：Sub评定等级()DimiAsInteger10Fori=3To11t=Sheets(1).Cells(i,2).Value'取得成绩Ift>=90Thenj="A"ElseIft>=80Thenj="B"ElseIft>=70Thenj="C"ElseIft>=60Thenj="D"Elsej="E"EndIfSheets(1).Cells(i,3)=jNextEndSub（4）返回Excel操作界面，在成绩表旁边增加一个按钮，并指定执行宏“评定等级”。（5）单击“评定等级”按钮，即可在成绩表的C列显示出各成绩对应的等级，如图3-17所示。例7计算个人所得税1．案例说明在工资管理系统中，需要计算员工应缴纳的个人所得税。个人所得税税额按5%至45%的九级超额累进税率计算应缴税额。个人所得税的计算公式为：应纳个人所得税税额=应纳税所得额×适用税率-速算扣除数本例根据工资表中的相应数据计算出纳税额，并填充在工资表对应的列中。2．关键技术本例中计算个人所得税时共有九个分支。这时可在IfElseIf结构中添加多个ThenElseIf块来进行各分支的处理。对于多分支结构，可使用SelectCase语句。SelectCase语句的功能与IfThenElse语句类似，但在多分支结构中，使用SelectCase语句可使代码简洁易读。SelectCase结构的语法格式如下：SelectCase测试表达式Case表达式列表1语句序列111Case表达式列表2语句序列2CaseElse语句序列nEndSelect在以上结构中，首先计算出“测试表达式”的值，然后，VBA将表达式的值与结构中的每个Case的值进行比较。如果相等，就执行与该Case语句下面的语句块，执行完毕再跳转到EndSelect语句后执行。其流程图如图3-20所示。在SelectCase结构中，“测试表达式”通常是一个数值型或字符型的变量。“表达式列表”可以是一个或几个值的列表。如果在一个列表中有多个值，需要用逗号将各值分隔开。表达式列表可以按以下几种情况进行 书 关于书的成语关于读书的排比句社区图书漂流公约怎么写关于读书的小报汉书pdf 写：—表达式：表示一些具体的取值。例如：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)DimcurTAsCurrencycurP=curP–1600'1600为扣除数IfcurP>0ThenSelectCasecurPCaseIs<=500curT=curP*0.05CaseIs<=2000curT=(curP-500)*0.1+25CaseIs<=5000curT=(curP-2000)*0.15+125CaseIs<=20000curT=(curP-5000)*0.2+375CaseIs<=4000012curT=(curP-20000)*0.25+1375CaseIs<60000curT=(curP-40000)*0.3+3375CaseIs<80000curT=(curP-60000)*0.35+6375CaseIs<100000curT=(curP-80000)*0.4+10375CaseElsecurT=(curP-100000)*0.45+15375EndSelect个人所得税=curTElse个人所得税=0EndIfEndFunction5）在模块中编写“计算”子过程，计算工资表中每个员工应缴所得税额，并填写在对应的列中。Sub计算()Fori=4To9Sheets(1).Cells(i,8).Value=个人所得税(Sheets(1).Cells(i,6).Value)NextEndSub（）返回到Excel环境中，在工资表下方插入一个按钮，为按钮指定宏为计算。6“”（7）单击“计算”按钮，可计算出每个员工的所得税额。在实际开发的应用系统中，经常需要重复执行一条或多条语句。这种结构称为循环结构。循环结构的思想是利用计算机高速处理运算的特性，重复执行某一部分代码，以完成大量有规则的重复性运算。VBA提供了多个循环结构控制语句：DoLoop结构、WhileWend结构、ForNext结构、ForEachNext结构。例8密码验证1．案例说明在信息管理系统中，很多时候都需要用户进行登录操作。在登录操作时要求用户输入密码，一般都要给用户三次机会，每次的输入过程和判断过程都相同。本例使用DoLoop循环完成密码验证过程。2．关键技术在VBA中，最常用的循环语句是DoLoop循环。循环结构DoWhileLoop的语法格13式如下：DoWhile逻辑表达式语句序列1[ExitDo][语句序列2]Loop其中DoWhile和Loop为关键字，在DoWhile和Loop之间的语句称为循环体。当VBA执行这个Do循环时，首先判断“逻辑表达式”的值，如果为（或零），False则跳过所有语句，执行Loop的下一条语句，如果为True（或非零），则执行循环体，当执行到Loop语句后，又跳回到DoWhile语句再次判断条件。在循环体中如果包含有ExitDo语句，当执行到ExitDo语句，马上跳出循环，执行Loop的下一条语句。其流程图如图3-22所示。DoWhileLoop流程图VBA的DoLoop循环有4种结构，分别如下：—DoWhileLoop循环：先测试条件，如果条件成立则执行循环体。—DoLoopWhile循环：先执行一遍循环体，再测试循环条件，如果条件成立则执行循环体。—DoUntilLoop循环：先测试条件，如果条件不成立则执行循环体。—DoLoopUntil循环：先执行一遍循环体，再测试循环条件，如果条件不成立则执行循环体。3．编写代码1）新建Excel工作簿，按快捷键“Alt+F11”进入VBE开发环境。2）单击菜单“插入/模块”命令向工程中插入一个模块。3）在模块中编写以下VBA代码：Sublogin()DimstrPasswordAsString'保存密码DimiAsInteger'输入密码的次数DostrPassword=InputBox("请输入密码")'输入密码IfstrPassword="test"Then'判断密码是否正确ExitDo'退出循环ElseMsgBox("请输入正确的密码！")EndIfi=i+114LoopWhilei<3Ifi>=3Then'超过正常输入密码次数MsgBox"非法用户，系统将退出！"Application.QuitElseMsgBox"欢迎你使用本系统！"EndIfEndSub（）返回Excel操作界面，在工作表中插入一个按钮，设置提示文字为密码验证，4“”并为该按钮指定执行的宏为“login。”5）单击“密码验证”按钮，弹出对话框，输入密码后单击“确定”按钮进行密码的验证。例9求最小公倍数和最大公约数1．案例说明几个数公有的倍数叫做这几个数的公倍数，其中最小的一个叫做这几个数的最小公倍数。如12、、20这三个数的最小公倍数为180。18最大公约数是指某几个整数的共有公约数中最大的那个数。如、、6这三个数的最大24公约数为2。本例使用辗转相除法求两个自然数m、n的最大公约数和最小公倍数。2．关键技术本例首先求出两数m、n的最大公约数，再将m、n数的乘积除以最大公约数，即可得到最小公倍数。本例使用DoLoop循环，并且没有设置循环条件。一般情况下，这种循环是一个死循环（也就是说程序将一直循环下去），因此，在这种循环结构中必须添加一个判断语句，当达到指定的条件时退出循环。如本例中使用以下语句退出循环：Ifr=0ThenExitDo3．编写代码1）新建Excel工作簿，按快捷键“Alt+F11”进入VBE环境。2）单击菜单“插入/模块”命令向工程中插入一个模块。3）在模块中编写以下子过程：Sub最小公倍数和最大公约数()DimmAsInteger,nAsIntegerDimm1AsInteger,n1AsIntegerDimtAsIntegerm=InputBox("输入自然数m：")15n=InputBox("输入自然数n：")m1=mn1=nIfm1 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 局（ANSI）制定的ASCII码。ASCII码由8位二进制组成，一共可包含256个符号。本例使用循环语句输出ASCII中的可见字符。2．关键技术使用DoLoop循环时，可以不知道循环的具体次数。如果知道循环的次数，可以使用ForNext循环语句来执行循环。For循环的语法如下：For循环变量=初始值To终值[Step步长值]语句序列1[ExitFor][语句序列2]Next[循环变量]在For循环中使用循环变量来控制循环，每重复一次循环之后，循环变量的值将与步长16值相加。步长值可正可负，如果步长值为正，则初始值必须小于等于终值，才执行循环体，否则退出循环。如果步长值为负，则初始值必须大于等于终值，这样才能执行循环体。如果没有设置，则步长值默认为。循环结构的流程图如图3-28所示。Step1ForNextFor循环一般都可计算出循环体的执行次数，计算公式如下：循环次数=[(终值-初值)/步长值]+1这里用中括号表示取整。在事先不知道循环体需要执行多少次时，应该用Do循环。而在知道循环体要执行的次数时，最好使用ForNext循环。3．编写代码1）新建Excel工作簿，按快捷键“Alt+F11”进入VBE环境。2）单击菜单“插入/模块”命令向工程中插入一个模块。3）在模块中编写以下子过程：Subascii()DimaAsInteger,iAsIntegeri=3Fora=32To126Sheets(1).Cells(i,1)=aSheets(1).Cells(i,2)=Chr(a)i=i+1NextEndSub（4）返回Excel操作环境，向工作表中插入一个按钮，为按钮指定执行上步创建的宏。5）单击按钮，得到结果。例11计算选中区域数值之和1．案例说明在某些情况下，需要统计工作表中选定区域数值单元格的数值之和（例如，临时查看应发奖金之和），在Excel的状态栏就可查看选中单元格的数值之和。本例编写VBA代码，使用循环结构来完成该项功能。2．关键技术用户在Excel工作表中选定单元格的数量是不固定的，若需统计所选单元格数值之和，这时可使用ForEach循环来进行处理，对选中区域的每个单元格进行判断，然后再累加数值单元格的值。ForEachNext循环语句的语法格式如下：ForEach元素In对象集合17[语句序列1][ExitFor][语句序列2]Next使用ForEach循环结构，可在对象集合每个元素中执行一次循环体。如果集合中至少有一个元素，就会进入ForEach循环体执行。一旦进入循环，便先针对“对象集合”中第一个元素执行循环中的所有语句。如果“对象集合”中还有其他的元素，则会针对它们执行循环中的语句，当对象集合中的所有元素都执行完了，便会退出循环，然后从Next语句之后的“”语句继续执行。在循环体中可以放置任意多个ExitFor语句，随时退出循环。ExitFor经常在条件判断之后使用，例如If，并将控制权转移到紧接在Next之后的语句。Then3．编写代码1）新建Excel工作簿，按快捷键“Alt+F11”进入VBE环境。2）单击菜单“插入/模块”命令向工程中插入一个模块。3）在模块中编写以下子过程：Sub求和()DimrDimtAsLongForEachrInSelectionIfIsNumeric(r.Value)Thent=t+r.ValueEndIfNextMsgBox"所选区域数值之和为："&tEndSub（）返回Excel操作环境，向工作表中插入一个按钮，修改按钮的提示字符为求和4“”，为按钮指定执行上步创建的宏“求和”。5）在工作表“Sheet1”中输入数据。6）拖动鼠标选中数据区域，单击“求和”按钮，求和结果将显示在话框中。例12换零钱法（多重循环）1．案例说明将十元钱换成1角、2角、5角、1元、2元、5元的零钱若干，求出一共有多少种方法进行计算？2．关键技术在VBA中，循环结构内的循环体又可以是循环结构，这种情况称为循环的嵌套。VBA18允许在同一过程里嵌套多种类型的循环。在编写嵌套循环程序的代码时，一定要注意每个循环语句的配对情况。如图3-30所示，其中左图是正确的嵌套关系，第一个Next关闭了内层的For循环，而最后一个Loop关闭了外层的Do循环。同样，在嵌套的If语句中，EndIf语句自动与最靠近的前一个If语句配对。嵌套的DoLoop结构的工作方式也是一样的，最内圈的Loop语句与最内圈的Do语句匹配。3．编写代码1）新建Excel工作簿，按快捷键“Alt+F11”进入VBE环境。2）单击菜单“插入/模块”命令向工程中插入一个模块。（3）零钱换法最简单的算法是：使用多重循环，将10元钱能换成的各种可能都考虑进去（如10可换为100个1角，可换为50个2角，等等）。根据这种算法在模块中编写以下子过程：Sub换零钱1()DimtAsIntegerFori=0To100'1角Forj=0To50'2角Fork=0To20'5角Forl=0To10'1元Form=0To5'2元Forn=0To2'5元Ifi+2*j+5*k+10*l+20*m+50*n=100Thent=t+1Sheets(1).Cells(t+1,1)=iSheets(1).Cells(t+1,2)=jSheets(1).Cells(t+1,3)=kSheets(1).Cells(t+1,4)=lSheets(1).Cells(t+1,5)=mSheets(1).Cells(t+1,6)=nEndIfNextNextNextNextNextNextMsgBox"10元换为零钱共有"&t&"种方法！"19EndSub（4）运行该子过程，Excel工作表中每一行将填写一种可能的换法，如图3-31所示。5）因为换零钱的方法很多，根据计算机的速度不同该程序的运行速度也不同，最后将通过对话框显示出总的换法次数。6）在循环嵌套中，内层循环体执行的次数等各外层循环数数之积，如本例代码内循环执行次数为：101×51×21×11×6×3=21417858次7）对于嵌套循环，一般都可以对代码进行一定的优化，使程序的执行效率更高。本例最简单的优化代码如下：Sub换零钱2()DimtAsLongForj=0To50'2角Fork=0To20'5角Forl=0To10'1元Form=0To5'2元Forn=0To2'5元t2=2*j+5*k+10*l+20*m+50*nIft2<=100Thent=t+1i=100-t2Sheets(1).Cells(t+1,1)=iSheets(1).Cells(t+1,2)=jSheets(1).Cells(t+1,3)=kSheets(1).Cells(t+1,4)=lSheets(1).Cells(t+1,5)=mSheets(1).Cells(t+1,6)=nEndIfNextNextNextNextNextMsgBox"10元换为零钱共有"&t&"种方法！"EndSub（8）以上程序中内循环的执行数数如下：51×21×11×6×3=212058次20可以看出减少最外层循环的101次，可使用内循环体提高100倍的执行效率。本例程序还有很多优化方法，这里就不再介绍。使用数组在程序中，如果要处理大量的数据，为每个数据定义一个变量将使程序变得很难阅读，并且代码很烦琐。对于大量有序的数据，可以使用数组对其进行存储和处理。在其他程序设计语言中，数组中的所有元素都必须为同样的数据类型，在VBA中，数组中各元素可以是相同的数据类型，也可以是不同的数据类型。例13数据排序1．案例说明在Excel中可以方便地对单元格区域中的数据进行排序。本例使用VBA程序首先让用户输入10个数据，然后使用冒泡排序法对这10个数进行排序。2．关键技术在程序中处理大量数据时，使用数组来保存是比较好的方法。数组使用之前可以使用Dim、Static、Private或Public语句来声明。在VBA中，数组最大可以达到60维，最常用的是一维数组和二维数组。定义一维数组的语法格式如下：Dim数组名([下界To]上界)As数据类型其中下界可以省略，只给出数组的上界（即可以使用的最大下标值），这时默认值为“”0，即数组的下标从0开始至定义的上界，如：DimMyArray(10)AsString定义了一个名为MyArray的数组，共有11个元素，分别为MyArray(0)、MyArray(1)、、MyArray(10)。如果希望下标从1开始，可以通过OptionBase语句来设置，其语法格式如下：OptionBase1使用该语句指定数组下标的默认下界，只能设为0或1。该语句只能出现在用户窗体或模块的声明部分，不能出现在过程中，且必须放在数组定21义之前。3．编写代码1）新建Excel工作簿，按快捷键“Alt+F11”进入VBE环境。2）单击菜单“插入/模块”命令向工程中插入一个模块。3）在模块中编写以下代码：OptionBase1Sub数据排序()DimiAsInteger,jAsIntegerDimkDims(10)AsIntegerFori=1To10s(i)=Application.InputBox("输入第"&i&"个数据：","输入数组",,,,,,1)NextFori=1To9Forj=i+1To10Ifs(i)0，则将生成随机序列中的下一个随机数。—number=0，则将生成最近生成的数。—省略number，则生成序列中的下一个随机数。—在调用Rnd之前，先使用无参数的Randomize语句初始化随机数生成器，该生成器具有根据系统计时器得到的种子。23为了生成某个范围内的随机整数，可使用以下公式：Int((上限–下限+1)*Rnd+下限)3．编写代码1）新建Excel工作簿，按快捷键“Alt+F11”进入VBE环境。2）单击菜单“插入/模块”命令向工程中插入一个模块。3）在模块中编写以下代码：OptionBase1Sub幸运号码()DimnAsInteger,iAsInteger,jAsIntegerDiml()AsIntegern=Application.InputBox("请输入需要产生幸运号码的数量：","幸运号码",,,,,,2)ReDiml(n,7)AsIntegerFori=1TonForj=1To7Randomizel(i,j)=Int(10*Rnd)NextNextFori=1TonForj=1To7Debug.Printl(i,j);NextDebug.PrintNextEndSub4）运行上面的宏，弹出如图3-35所示的对话框，提示用户输入数据。输入生成幸运号码的数量。5）单击“确定”按钮后在“立即窗口”输出生成的幸运号码。例15用数组填充单元格区域1．案例说明在Excel中要处理大量数据时，可使用循环从各单元格读入数据，经过加工处理后再写回单元格区域中。这种方式比在数组中处理数据的速度要慢。因此，如果有大量的数据需要处理时，可先将数据保存到数组中，经过加工处理后，再将数组的数据填充到单元格区域。本例演示将二维数组中的数据填充到工作表中的方法。2．关键技术在Excel工作表中，工作表是一个二维结构，由行和列组成。这种特性与二维数组类似24，因此可以很方便地将工作表单元格区域与二维数组之间进行转换。通过以下语句可将单元格区域赋值给一个二维数组：myarr=Range(Cells(1,1),Cells(5,5))反过来，也可将二维数组中的值快速的赋值给一个单元格区域，如以下语句将二维数组myarr中的值赋值给单元格区域Rng：Rng.Value=arr3．编写代码1）新建Excel工作簿，按快捷键“Alt+F11”进入VBE环境。2）单击菜单“插入/模块”命令向工程中插入一个模块。3）在模块中编写以下代码：OptionBase1Sub数组填充单元格区域()DimiAsLong,jAsLongDimcolAsLong,rowAsLongDimarr()AsLongrow=Application.InputBox(prompt:="col=Application.InputBox(prompt:="ReDimarr(row,col)Fori=1TorowForj=1Tocolarr(i,j)=(i-1)*col+jNextNext输入行数：",Type:=2)输入列数：",Type:=2)SetRng=Sheets(1).Range(Cells(1,1),Cells(row,col))Rng.Value=arrEndSub（）返回Excel操作环境，向工作表中添加一个按钮，设置提示文字为填充数据，4“”指定该按钮的宏为“数组填充单元格区域”。（5）单击“填充数据”按钮，弹出对话框，分别输入数组的行和列。输入行和列（6）VBA代码生成一个二维数组，最后填充到工作表中。通过Excel相关对象可对工作表中的数据进行操作，如处理单元格区域的公式、对数据进行查询、排序、筛选等操作。本章演示使用VBA进行处理数据的实例。处理公式使用VBA代码可对工作表中的公式单元格进行处理，如判断单元格是否包25含公式、复制公式、将单元格公式转换为具体的值等。例16判断单元格是否包含公式1．案例说明打开本例工作簿，单击左上角的“公式单元格”按钮，将弹出提示框，显示当前工作表中定义了公式的单元格。显示有公式的单元格2．关键技术本例使用Range对象的HasFormula属性来判断指定单元格是否包含公式，如果区域中所有单元格均包含公式，则该属性值为True；如果所有单元格均不包含公式，则该属性值为False；其他情况下为null。本例对当前单元格区域中的单元格逐个进行判断，并显示出具有公式的单元格。3．编写代码“公式单元格”按钮的VBA代码如下：Sub显示公式单元格()DimrngAsRangeSetrng=ActiveSheet.Range("A1").CurrentRegionForEachcInrng.CellsIfc.HasFormulaThenMsgBox"单元格"&c.Address&"定义了公式！"EndIfNextEndSub例17自动填充公式1．案例说明打开本例工作簿如图12-2所示，在如图所示工作表中，单元格J3和D16定义了公式，单击“填充公式”按钮，单元格J3的公式将向下填充，单元格D16的公式向右填充。2．关键技术本例使用Range对象的AutoFill方法，对指定区域中的单元格执行自动填充。该方法的语法格式如下：表达式.AutoFill(Destination,Type)该方法有两个参数，其含义如下：—Destination：要填充的单元格。目标区域必须包括源区域。—Type：指定填充类型。该填充类型可使用xlAutoFillType枚举类型，其值如表12-1所示。26xlAutoFillType枚举值名称值描述xlFillCopy1将源区域的值和格式复制到目标区域，如有必要可重复执行xlFillDays5将星期中每天的名称从源区域扩展到目标区域中。格式从源区域复制到目标区域，如有必要可重复执行xlFillDefa0Excel确定用于填充目标区域的值和格式ultxlFillForm3只将源区域的格式复制到目标区域，如有必要可重复执行atsxlFillMont7将月名称从源区域扩展到目标区域中。格式从源区域复制到目hs标区域，如有必要可重复执行xlFillSerie2将源区域中的值扩展到目标区域中，形式为系列（如，“1,2扩”s展为）。格式从源区域复制到目标区域，如有必要可重复执“3,4,5”行xlFillValu4只将源区域的值复制到目标区域，如有必要可重复执行esxlFillWee6将工作周每天的名称从源区域扩展到目标区域中。格式从源区kdays域复制到目标区域，如有必要可重复执行xlFillYear8将年从源区域扩展到目标区域中。格式从源区域复制到目标区s域，如有必要可重复执行xlGrowth1将数值从源区域扩展到目标区域中，假定源区域的数字之间是Trend0乘法关系（如，“1,2,扩”展为“4,8,16，假”定每个数字都是前一个数字乘以某个值的结果）。格式从源区域复制到目标区域，如有必要可重复执行xlLinearTr9将数值从源区域扩展到目标区域中，假定数字之间是加法关系end（如，扩展为，假定每个数字都是前一个数字加上某“1,2,”“3,4,5”个值的结果）。格式从源区域复制到目标区域，如有必要可重复执行3．编写代码“填充公式”按钮的VBA代码如下：Sub填充公式()DimiAsLong,jAsLongWithRange("A1").CurrentRegioni=.Rows.Count-1j=.Columns.Count-127EndWithRange("J3").AutoFill_Destination:=Range(Cells(3,10),Cells(i,10))Range("D16").AutoFill_Destination:=Range(Cells(16,4),Cells(16,j))EndSub以上代码首先获取当前区域的行和列，接着使用AutoFill方法在垂直方向和水平方向填充相应的公式。例18锁定和隐藏公式1．案例说明打开本例工作簿，单击“锁定隐藏公式”按钮，当前工作表中的所有公式单元格将被锁定，不允许用户修改，而其他单元格的数据用户可进行修改。同时，公式单元格定义的公式将被隐藏，单击选取具有公式的单元格时，将不会显示公式。图12-4锁定和隐藏公式2．关键技术要锁定和隐藏单元格，可通过Range对象的以下两个属性来进行设置。—Locked属性：指明对象是否已被锁定。—FormulaHidden属性：指明在工作表处于保护状态时是否隐藏公式。当设置以上两个属性为True时，对指定区域锁定和隐藏。但要真正锁定和隐藏单元格，必须使用Protect方法对工作表进行保护。3．编写代码“锁定隐藏公式”按钮的VBA代码如下：Sub锁定和隐藏公式()IfActiveSheet.ProtectContents=TrueThenMsgBox"工作表已