神马文学网完全手册Excel VBA典型实例大全：通过368个例子掌握
来源：百度文库 编辑：神马文学网 时间：2024/04/28 18:13:22
VBA应用程序由一系列的VBA代码组成，这些代码将按照一定的顺序执行。有时程序根据一定的条件只能执行某一部分代码，有时需要重复执行某一段代码。通过程序结构控制代码来完成这些功能，本章介绍这些程序控制流程方面的技巧。
3.1 常用输入/输出语句
结构化程序设计中使用的基本控制结构有3种：顺序结构、选择结构和循环结构。顺序结构就是按照语句的书写顺序从上到下、逐条语句地执行。执行时，编写在前面的代码先执行，编写在后面的代码后执行。这是最普遍的结构形式，也是后面两种结构的基础。
顺序结构不需要使用结构控制语句，本节介绍常用的输入输出语句的技巧。
例017 九九乘法表（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）在模块中输入以下代码：
Sub multi()
For i = 1 To 9
For j = 1 To i
Debug.Print i; "x"; j; "="; i * j; " ";
Next
Debug.Print '换行
Next
End Sub
（3）按功能键“F5”运行子过程，在“立即窗口”输出九九乘法表，如图3-1所示。
图3-1 立即窗口
例018 输入个人信息（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）在模块中输入以下代码：
Sub inputinfo()
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
End Sub
（3）按功能键“F5”运行子过程，将弹出“输入个人信息”窗口，如图3-2所示。在对话框中输入内容后按“回车”，或单击“确定”按钮。
（4）接着输入“年龄”和“地址”信息，在“立即窗口”中将输出这些内容，如图3-3所示。
图3-2 输入个人信息 图3-3 输出结果
例019 退出确认（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
显示Critical Message图标
vbQuestion
32
显示Warning Query图标
vbExclamation
48
显示Warning Message图标
vbInformation
64
显示Information Message图标
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所示。代码窗口中将自动生成事件过程结构如下：
图3-5 对象列表 图3-6 事件列表
Private Sub Workbook_BeforeClose(Cancel As Boolean)
End Sub
（4）在上面生成的事件过程中输入以下代码：
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim intReturn As Integer
intReturn = MsgBox("真的退出系统吗？", vbYesNo + vbQuestion, "提示")
If intReturn <> vbYes Then Cancel = True
End Sub
（5）保存Excel工作簿。
（6）关闭Excel工作簿时，将弹出如图3-7所示的对话框。单击“是”按钮将退出Excel，单击“否”按钮将返回Excel工作簿。
3.2 分支结构
分支结构，又叫选择结构。这种结构的程序将根据给定的条件来决定执行哪一部分代码，而跳过其他代码。
例020 突出显示不及格学生
1．案例说明
本例判断学生成绩表中的成绩，如果成绩不及格（低于60分），则将该成绩着重显示出来。如图3-8所示（左图为原成绩，右图突出显示不及格成绩）。
图3-8 突出显示不及格学生
2．关键技术
在本例中，需要进行一个判断（成绩是否低于60分），这时可使用If…Then语句。用If…Then语句可有条件地执行一个或多个语句。其语法格式如下：
If 逻辑表达式 Then
语句1
语句1
… …
语句n
End If
逻辑表达式也可以是任何计算数值的表达式，VBA将为零（0）的数值看做False，而任何非零数值都被看做True。
该语句的功能为：若逻辑表达式的值是True，则执行位于Then与End If之间的语句；若逻辑表达式的值是False，则不执行Then与End If之间的语句，而执行End If后面的语句。其流程图如图3-9所示。
If…Then结构还有一种更简单的形式：单行结构条件语句。其语法格式如下：
If 逻辑表达式 Then 语句
该语句的功能为：若逻辑表达式的值是True，则执行Then后的语句；若逻辑表达式的值是False，则不执行Then后的语句，而执行下一条语句。
3．编写代码
（1）打开“学生成绩表”。
（2）按快捷键“Alt+F11”进入VBE环境。
（3）单击菜单“插入/模块”命令向工程中插入一个模块，并编写以下代码：
Sub 显示不及格学生()
Dim i As Integer
For i = 3 To 11
If Sheets(1).Cells(i, 2).Value < 60 Then
Sheets(1).Cells(i, 2).Select
Selection.Font.FontStyle = "加粗"
Selection.Font.ColorIndex = 3
End If
Next
End Sub
（4）关闭VBE开发环境返回Excel。
（5）在功能区“开发工具”选项卡的“控件”组中，单击“插入”按钮弹出“表单控件”面板，如图3-10所示。
图3-10 插入按钮
（6）在“表单控件”面板中单击“按钮”，拖动鼠标在工作表中绘制一个按钮。当松开鼠标时，将弹出“指定宏”对话框，如图3-11所示。
（7）在“指定宏”对话框中，单击选中“显示不及格学生”宏，单击“确定”按钮。
（8）右击工作表中的按钮，弹出快捷菜单如图3-12所示，单击“编辑文字”菜单，修改按钮中的提示文字为“显示不及格学生”。
图3-11 指定宏 图3-12 编辑文字
（9）单击“显示不及格学生”按钮，执行宏代码，成绩表中不及格成绩将突出显示为粗体、红色，如图3-13所示。
图3-13 执行程序
例021 从身份证号码中提取性别
1．案例说明
在很多信息系统中都需要使用到身份证号码，身份证号码中包含有很多信息，如可从其中提取性别。我国现行使用的身份证号码有两种编码规则，即15位居民身份证和18位居民身份证。
15位的身份证号的编码规则。
dddddd yymmdd xx p
18位的身份证号的编码规则。
dddddd yyyymmdd xx p y
其中：
— dddddd为地址码（省地县三级）18位中的和15位中的不完全相同。
— yyyymmdd yymmdd 为出生年月日。
— xx序号类编码。
— p性别。
— 18位中末尾的y为校验码。
2．关键技术
在If…Then语句中，条件不成立时不执行任何语句。在很多时候需要根据条件是否成立分别执行两段不同的代码，这时可用If…Then…Else语句，其语法格式如下：
If 逻辑表达式 Then
语句序列1
Else
语句序列2
End If
VBA判断“逻辑表达式”的值，如果它为True，将执行“语句序列1”中的各条语句，当“逻辑表达式”的值为False时，就执行“语句序列2”中的各条语句。其流程图如图3-14所示。
3．编写代码
（1）新建Excel工作簿，在VBE中插入一个模块。
（2）在模块中编写以下代码：
Sub 根据身份证号码确定性别()
sid = InputBox("请输入身份证号码：")
i = Len(sid)
If i <> 15 And i <> 18 Then '判断身份证号长度是否正确
MsgBox "身份证号码只能为15位或18位！"
Exit Sub
End If
If i = 15 Then '长度为15位
s = Right(sid, 1) '取最右侧的数字
Else '长度为18度
s = Mid(sid, 17, 1) '取倒数第2位数
End If
If Int(s / 2) = s / 2 Then '为偶数
sex = "女"
Else
sex = "男"
End If
MsgBox "性别：" + sex
End Sub
（3）切换到Excel环境，添加一个按钮“从身份证号码提取性别”，并指定执行上步创建的宏。
（4）单击“从身份证号码提取性别”按钮，弹出如图3-15所示对话框。
（5）输入身份证号码后单击“确定”按钮，将在如图3-16所示对话框中显示性别。
图3-15 输入身份证号码 图3-16 显示性别
例022 评定成绩等级
1．案例说明
本例将成绩表中的百分制成绩按一定规则划分为A、B、C、D、E五个等级，如图3-17所示。
图3-17 评定成绩等级
其中各等级对应的成绩分别为：
— A：大于等于90分；
— B：大于等于80分，小于90分；
— C：大于等于70分，小于80分；
— D：大于等于60分，小于70分；
— E：小于60分。
2．关键技术
本例共有五个分支，使用If…Then…Else这种二路分支结构也可完成，但需要复杂的嵌套结构才能解决该问题。其实VBA中提供了一种If…Then…ElseIf的多分支结构，其语法格式如下：
If 逻辑表达式1 Then
语句序列1
ElseIf 逻辑表达式2 Then
语句序列2.
ElseIf 逻辑表达式3 Then
语句序列3
... …
Else
语句序列n
End If
在以上结构中，可以包括任意数量的ElseIf子句和条件，ElseIf子句总是出现在Else子句之前。
VBA首先判断“逻辑表达式1”的值。如果它为False，再判断“逻辑表达式2”的值，依此类推，当找到一个为True的条件，就会执行相应的语句块，然后执行End If后面的代码。如果所有“逻辑表达式”都为False，且包含Else语句块，则执行Else语句块。其流程图如图3-18所示。
图3-18 If…Then…ElseIf语句流程图
3．编写代码
（1）在Excel中打开成绩表。
（2）按快捷键“Alt+F11”进入VBE开发环境。
（3）单击“插入/模块”命令向工程中插入一个模块，并编写以下VBA代码：
Sub 评定等级()
Dim i As Integer
For i = 3 To 11
t = Sheets(1).Cells(i, 2).Value '取得成绩
If t >= 90 Then
j = "A"
ElseIf t >= 80 Then
j = "B"
ElseIf t >= 70 Then
j = "C"
ElseIf t >= 60 Then
j = "D"
Else
j = "E"
End If
Sheets(1).Cells(i, 3) = j
Next
End Sub
（4）返回Excel操作界面，在成绩表旁边增加一个按钮，并指定执行宏“评定等级”。
（5）单击“评定等级”按钮，即可在成绩表的C列显示出各成绩对应的等级，如图3-17所示。
例023 计算个人所得税
1．案例说明
在工资管理系统中，需要计算员工应缴纳的个人所得税。个人所得税税额按5%至45%的九级超额累进税率计算应缴税额，税率表如图3-19所示。
个人所得税的计算公式为：
应纳个人所得税税额=应纳税所得额×适用税率-速算扣除数
本例根据工资表中的相应数据计算出纳税额，并填充在工资表对应的列中。
图3-19 个人所得税税率表
2．关键技术
本例中计算个人所得税时共有九个分支。这时可在If…Then…ElseIf结构中添加多个ElseIf块来进行各分支的处理。对于多分支结构，可使用Select Case语句。Select Case语句的功能与If…Then…Else语句类似，但在多分支结构中，使用Select Case语句可使代码简洁易读。
Select Case结构的语法格式如下：
Select Case 测试表达式
Case 表达式列表1
语句序列1
Case 表达式列表2
语句序列2
… …
Case Else
语句序列n
End Select
在以上结构中，首先计算出“测试表达式”的值，然后，VBA将表达式的值与结构中的每个Case的值进行比较。如果相等，就执行与该Case语句下面的语句块，执行完毕再跳转到End Select语句后执行。其流程图如图3-20所示。
图3-20 Select Case语句流程图
在Select Case结构中，“测试表达式”通常是一个数值型或字符型的变量。“表达式列表”可以是一个或几个值的列表。如果在一个列表中有多个值，需要用逗号将各值分隔开。表达式列表可以按以下几种情况进行书写：
— 表达式：表示一些具体的取值。例如：Case 10,15,25。
— 表达式A To 表达式B：表示一个数据范围。例如，Case 7 To 17表示7～17之间的值。
— Is 比较运算符表达式：表示一个范围。例如，Case Is>60 表示所有大于90的值。
— 以上三种情况的混合。例如，Case 4 To 10, 15, Is>20。
3．编写代码
（1）在Excel中打开工资表工作簿。
（2）按快捷键“Alt+F11”进入VBE开发环境。
（3）单击菜单“插入/模块”命令插入一个模块。
（4）在模块中编写以下函数，用来计算所得税：
Function 个人所得税(curP As Currency)
Dim curT As Currency
curP = curP – 1600 '1600为扣除数
If curP > 0 Then
Select Case curP
Case Is <= 500
curT = curP * 0.05
Case Is <= 2000
curT = (curP - 500) * 0.1 + 25
Case Is <= 5000
curT = (curP - 2000) * 0.15 + 125
Case Is <= 20000
curT = (curP - 5000) * 0.2 + 375
Case Is <= 40000
curT = (curP - 20000) * 0.25 + 1375
Case Is < 60000
curT = (curP - 40000) * 0.3 + 3375
Case Is < 80000
curT = (curP - 60000) * 0.35 + 6375
Case Is < 100000
curT = (curP - 80000) * 0.4 + 10375
Case Else
curT = (curP - 100000) * 0.45 + 15375
End Select
个人所得税 = curT
Else
个人所得税 = 0
End If
End Function
（5）在模块中编写“计算”子过程，计算工资表中每个员工应缴所得税额，并填写在对应的列中。
Sub 计算()
For i = 4 To 9
Sheets(1).Cells(i, 8).Value = 个人所得税(Sheets(1).Cells(i, 6).Value)
Next
End Sub
（6）返回到Excel环境中，在工资表下方插入一个按钮，为按钮指定宏为“计算”。
（7）单击“计算”按钮，可计算出每个员工的所得税额，如图3-21所示。
图3-21 计算所得税
3.3 循环结构
在实际开发的应用系统中，经常需要重复执行一条或多条语句。这种结构称为循环结构。循环结构的思想是利用计算机高速处理运算的特性，重复执行某一部分代码，以完成大量有规则的重复性运算。
VBA提供了多个循环结构控制语句：Do…Loop结构、While…Wend结构、For…Next结构、For Each…Next结构。
例024 密码验证
1．案例说明
在信息管理系统中，很多时候都需要用户进行登录操作。在登录操作时要求用户输入密码，一般都要给用户三次机会，每次的输入过程和判断过程都相同。
本例使用Do…Loop循环完成密码验证过程。
2．关键技术
在VBA中，最常用的循环语句是Do…Loop循环。循环结构Do While…Loop的语法格式如下：
Do While 逻辑表达式
语句序列1
[Exit Do]
[语句序列2]
Loop
其中Do While和Loop为关键字，在Do While和Loop之间的语句称为循环体。
当VBA执行这个Do循环时，首先判断“逻辑表达式”的值，如果为False（或零），则跳过所有语句，执行Loop的下一条语句，如果为True（或非零），则执行循环体，当执行到Loop语句后，又跳回到Do While语句再次判断条件。在循环体中如果包含有Exit Do语句，当执行到Exit Do语句，马上跳出循环，执行Loop的下一条语句。其流程图如图3-22所示。
图3-22 Do While…Loop流程图
VBA的Do…Loop循环有4种结构，分别如下：
— Do While…Loop循环：先测试条件，如果条件成立则执行循环体。
— Do…Loop While循环：先执行一遍循环体，再测试循环条件，如果条件成立则执行循环体。
— Do Until…Loop循环：先测试条件，如果条件不成立则执行循环体。
— Do…Loop Until循环：先执行一遍循环体，再测试循环条件，如果条件不成立则执行循环体。
3．编写代码
（1）新建Excel工作簿，按快捷键“Alt+F11”进入VBE开发环境。
（2）单击菜单“插入/模块”命令向工程中插入一个模块。
（3）在模块中编写以下VBA代码：
Sub login()
Dim strPassword As String '保存密码
Dim i As Integer '输入密码的次数
Do
strPassword = InputBox("请输入密码") '输入密码
If strPassword = "test" Then '判断密码是否正确
Exit Do '退出循环
Else
MsgBox ("请输入正确的密码！")
End If
i = i + 1
Loop While i < 3
If i >= 3 Then '超过正常输入密码次数
MsgBox "非法用户，系统将退出！"
Application.Quit
Else
MsgBox "欢迎你使用本系统！"
End If
End Sub
（4）返回Excel操作界面，在工作表中插入一个按钮，设置提示文字为“密码验证”，并为该按钮指定执行的宏为“login”。
（5）单击“密码验证”按钮，弹出如图3-23所示对话框，输入密码后单击“确定”按钮进行密码的验证。
例025 求最小公倍数和最大公约数
1．案例说明
几个数公有的倍数叫做这几个数的公倍数，其中最小的一个叫做这几个数的最小公倍数。如12、18、20这三个数的最小公倍数为180。
最大公约数是指某几个整数的共有公约数中最大的那个数。如2、4、6这三个数的最大公约数为2。
本例使用辗转相除法求两个自然数m、n的最大公约数和最小公倍数。
2．关键技术
本例首先求出两数m、n的最大公约数，再将m、n数的乘积除以最大公约数，即可得到最小公倍数。求最大公约数的算法流程图如图3-24所示。
图3-24 最大公约数算法流程图
本例使用Do…Loop循环，并且没有设置循环条件。一般情况下，这种循环是一个死循环（也就是说程序将一直循环下去），因此，在这种循环结构中必须添加一个判断语句，当达到指定的条件时退出循环。如本例中使用以下语句退出循环：
If r = 0 Then Exit Do
3．编写代码
（1）新建Excel工作簿，按快捷键“Alt+F11”进入VBE环境。
（2）单击菜单“插入/模块”命令向工程中插入一个模块。
（3）在模块中编写以下子过程：
Sub 最小公倍数和最大公约数()
Dim m As Integer, n As Integer
Dim m1 As Integer, n1 As Integer
Dim t As Integer
m = InputBox("输入自然数m：")
n = InputBox("输入自然数n：")
m1 = m
n1 = n
If m1 < n1 Then
m1 = n
n1 = m '交换m和n的值
End If
Do
r = m1 Mod n1
If r = 0 Then Exit Do
m1 = n1
n1 = r
Loop
str1 = m & "," & n & "的最大公约数=" & n1 & vbCrLf
str1 = str1 & "最小公倍数=" & m * n / n1
MsgBox str1
End Sub
（4）返回Excel操作环境，向工作表中插入一个按钮，为按钮指定执行上步创建的宏。
（5）单击按钮，弹出如图3-25所示的输入提示框，分别输入两个数后，得到如图3-26所示的结果。
图3-25 输入数据 图3-26 最大公约数和最小公倍数
例026 输出ASCII码表
1．案例说明
目前计算机中用得最广泛的字符集及其编码，是由美国国家标准局（ANSI）制定的ASCII码。ASCII码由8位二进制组成，一共可包含256个符号。本例使用循环语句输出ASCII中的可见字符，如图3-27所示。
图3-27 ASCII码表
2．关键技术
使用Do…Loop循环时，可以不知道循环的具体次数。如果知道循环的次数，可以使用For…Next循环语句来执行循环。For循环的语法如下：
For 循环变量=初始值 To 终值 [Step 步长值]
语句序列1
[Exit For]
[语句序列2]
Next [循环变量]
在For循环中使用循环变量来控制循环，每重复一次循环之后，循环变量的值将与步长值相加。步长值可正可负，如果步长值为正，则初始值必须小于等于终值，才执行循环体，否则退出循环。如果步长值为负，则初始值必须大于等于终值，这样才能执行循环体。如果没有设置Step，则步长值默认为1。For…Next循环结构的流程图如图3-28所示。
For循环一般都可计算出循环体的执行次数，计算公式如下：
循环次数=[(终值-初值)/步长值]+1
这里用中括号表示取整。
在事先不知道循环体需要执行多少次时，应该用Do循环。而在知道循环体要执行的次数时，最好使用For…Next循环。
图3-28 For…Next流程图
3．编写代码
（1）新建Excel工作簿，按快捷键“Alt+F11”进入VBE环境。
（2）单击菜单“插入/模块”命令向工程中插入一个模块。
（3）在模块中编写以下子过程：
Sub ascii()
Dim a As Integer, i As Integer
i = 3
For a = 32 To 126
Sheets(1).Cells(i, 1) = a
Sheets(1).Cells(i, 2) = Chr(a)
i = i + 1
Next
End Sub
（4）返回Excel操作环境，向工作表中插入一个按钮，为按钮指定执行上步创建的宏。
（5）单击按钮，得到如图3-27所示的结果。
例027 计算选中区域数值之和
1．案例说明
在某些情况下，需要统计工作表中选定区域数值单元格的数值之和（例如，临时查看应发奖金之和），在Excel的状态栏就可查看选中单元格的数值之和。本例编写VBA代码，使用循环结构来完成该项功能。
2．关键技术
用户在Excel工作表中选定单元格的数量是不固定的，若需统计所选单元格数值之和，这时可使用For Each循环来进行处理，对选中区域的每个单元格进行判断，然后再累加数值单元格的值。
For Each…Next循环语句的语法格式如下：
For Each 元素 In 对象集合
[语句序列1]
[Exit For]
[语句序列2]
Next
使用For Each循环结构，可在对象集合每个元素中执行一次循环体。如果集合中至少有一个元素，就会进入For Each循环体执行。一旦进入循环，便先针对“对象集合”中第一个元素执行循环中的所有语句。如果“对象集合”中还有其他的元素，则会针对它们执行循环中的语句，当“对象集合”中的所有元素都执行完了，便会退出循环，然后从Next语句之后的语句继续执行。
在循环体中可以放置任意多个Exit For语句，随时退出循环。Exit For经常在条件判断之后使用，例如If…Then，并将控制权转移到紧接在Next之后的语句。
3．编写代码
（1）新建Excel工作簿，按快捷键“Alt+F11”进入VBE环境。
（2）单击菜单“插入/模块”命令向工程中插入一个模块。
（3）在模块中编写以下子过程：
Sub 求和()
Dim r
Dim t As Long
For Each r In Selection
If IsNumeric(r.Value) Then
t = t + r.Value
End If
Next
MsgBox "所选区域数值之和为：" & t
End Sub
（4）返回Excel操作环境，向工作表中插入一个按钮，修改按钮的提示字符为“求和”，为按钮指定执行上步创建的宏“求和”。
（5）在工作表“Sheet1”中输入数据，如图3-29左图所示。
（6）拖动鼠标选中如图3-29左图所示数据区域，单击“求和”按钮，求和结果将显示在如图3-29右图所示对话框中。
图3-29 计算选中区域数值之和
例028 换零钱法（多重循环）
1．案例说明
将十元钱换成1角、2角、5角、1元、2元、5元的零钱若干，求出一共有多少种方法进行计算？
2．关键技术
在VBA中，循环结构内的循环体又可以是循环结构，这种情况称为循环的嵌套。VBA允许在同一过程里嵌套多种类型的循环。
在编写嵌套循环程序的代码时，一定要注意每个循环语句的配对情况。如图3-30所示，其中左图是正确的嵌套关系，第一个Next关闭了内层的For循环，而最后一个Loop关闭了外层的Do循环。同样，在嵌套的If语句中，End If语句自动与最靠近的前一个If语句配对。嵌套的Do…Loop结构的工作方式也是一样的，最内圈的Loop语句与最内圈的Do语句匹配。图3-30右图则是错误的嵌套关系。
语句序列2
图3-30 正确的嵌套（左）与错误的嵌套（右）
3．编写代码
（1）新建Excel工作簿，按快捷键“Alt+F11”进入VBE环境。
（2）单击菜单“插入/模块”命令向工程中插入一个模块。
（3）零钱换法最简单的算法是：使用多重循环，将10元钱能换成的各种可能都考虑进去（如10可换为100个1角，可换为50个2角，等等）。根据这种算法在模块中编写以下子过程：
Sub 换零钱1()
Dim t As Integer
For i = 0 To 100 '1角
For j = 0 To 50 '2角
For k = 0 To 20 '5角
For l = 0 To 10 '1元
For m = 0 To 5 '2元
For n = 0 To 2 '5元
If i + 2 * j + 5 * k + 10 * l + 20 * m + 50 * n = 100 Then
t = t + 1
Sheets(1).Cells(t + 1, 1) = i
Sheets(1).Cells(t + 1, 2) = j
Sheets(1).Cells(t + 1, 3) = k
Sheets(1).Cells(t + 1, 4) = l
Sheets(1).Cells(t + 1, 5) = m
Sheets(1).Cells(t + 1, 6) = n
End If
Next
Next
Next
Next
Next
Next
MsgBox "10元换为零钱共有" & t & "种方法！"
End Sub
（4）运行该子过程，Excel工作表中每一行将填写一种可能的换法，如图3-31所示。
（5）因为换零钱的方法很多，根据计算机的速度不同该程序的运行速度也不同，最后将通过对话框显示出总的换法次数，如图3-32所示。
图3-31 零钱换法 图3-32 换法总数
（6）在循环嵌套中，内层循环体执行的次数等各外层循环数数之积，如本例代码内循环执行次数为：
101×51×21×11×6×3=21417858次
（7）对于嵌套循环，一般都可以对代码进行一定的优化，使程序的执行效率更高。本例最简单的优化代码如下：
Sub 换零钱2()
Dim t As Long
For j = 0 To 50 '2角
For k = 0 To 20 '5角
For l = 0 To 10 '1元
For m = 0 To 5 '2元
For n = 0 To 2 '5元
t2 = 2 * j + 5 * k + 10 * l + 20 * m + 50 * n
If t2 <= 100 Then
t = t + 1
i = 100 - t2
Sheets(1).Cells(t + 1, 1) = i
Sheets(1).Cells(t + 1, 2) = j
Sheets(1).Cells(t + 1, 3) = k
Sheets(1).Cells(t + 1, 4) = l
Sheets(1).Cells(t + 1, 5) = m
Sheets(1).Cells(t + 1, 6) = n
End If
Next
Next
Next
Next
Next
MsgBox "10元换为零钱共有" & t & "种方法！"
End Sub
（8）以上程序中内循环的执行数数如下：
51×21×11×6×3=212058次
可以看出减少最外层循环的101次，可使用内循环体提高100倍的执行效率。
本例程序还有很多优化方法，这里就不再介绍。
3.4 使用数组
在程序中，如果要处理大量的数据，为每个数据定义一个变量将使程序变得很难阅读，并且代码很烦琐。
对于大量有序的数据，可以使用数组对其进行存储和处理。在其他程序设计语言中，数组中的所有元素都必须为同样的数据类型，在VBA中，数组中各元素可以是相同的数据类型，也可以是不同的数据类型。
例029 数据排序
1．案例说明
在Excel中可以方便地对单元格区域中的数据进行排序。本例使用VBA程序首先让用户输入10个数据，然后使用冒泡排序法对这10个数进行排序。
2．关键技术
在程序中处理大量数据时，使用数组来保存是比较好的方法。数组使用之前可以使用Dim、Static、Private或Public语句来声明。在VBA中，数组最大可以达到60维，最常用的是一维数组和二维数组。
定义一维数组的语法格式如下：
Dim 数组名([下界 To] 上界) As 数据类型
其中“下界”可以省略，只给出数组的上界（即可以使用的最大下标值），这时默认值为0，即数组的下标从0开始至定义的上界，如：
Dim MyArray(10) As String
定义了一个名为MyArray的数组，共有11个元素，分别为MyArray(0)、MyArray(1)、…、MyArray(10)。
如果希望下标从1开始，可以通过Option Base语句来设置，其语法格式如下：
Option Base 1
使用该语句指定数组下标的默认下界，只能设为0或1。
—
该语句只能出现在用户窗体或模块的声明部分，不能出现在过程中，且必须放在数组定义之前。
3．编写代码
（1）新建Excel工作簿，按快捷键“Alt+F11”进入VBE环境。
（2）单击菜单“插入/模块”命令向工程中插入一个模块。
（3）在模块中编写以下代码：
Option Base 1
Sub 数据排序()
Dim i As Integer, j As Integer
Dim k
Dim s(10) As Integer
For i = 1 To 10
s(i) = Application.InputBox("输入第" & i & "个数据：", "输入数组", , , , , , 1)
Next
For i = 1 To 9
For j = i + 1 To 10
If s(i) < s(j) Then
t = s(i)
s(i) = s(j)
s(j) = t
End If
Next
Next
For Each k In s
Debug.Print k
Next
End Sub
在VBA中使用Inputbox函数接受用户输入数据时，返回的值为文本型。以上代码中使用了Application对象的InputBox方法来接受用户输入数据，该方法的语法格式如下：
Application.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)
设置Type参数可指定返回的数据类型，如本例设置其值为2，则返回的值为数值型。
（4）运行上面的宏，弹出如图3-33所示的对话框，提示用户输入数据。循环程序要求用户输入10个数据。
（5）最后在“立即窗口”输出排序的结果，如图3-34所示。
图3-33 输入数据 图3-34 排序结果
例030 彩票幸运号码
1．案例说明
本例结合数组和随机函数的知识，生成指定数量的彩票幸运号码。本例生成的彩票号码每注由7位数构成，首先让用户输入产生的注数，再使用循环语句生成指定注数的号码。
2．关键技术
本例代码中使用了两个关键技术：动态数组和随机函数。
（1）动态数组
本例使用二维数组保存所有的彩票号码，二维数组的定义格式如下：
Dim 数组名(第1维上界, 第2维上界) As 数据类型
或
Dim 数组名(第1维下界 To 第1维上界, 第2维下界 To 第2维上界) As 数据类型
在本例中，因为生成的彩票数量是由用户输入的数据决定的。因此这里使用动态数组。
动态数组是指在程序运行时大小可以改变的数组，定义动态数组一般分两个步骤：首先在用户窗体、模块或过程中使用Dim或Public声明一个没有下标的数组（不能省略括号），然后在过程中用ReDim语句重定义该数组的大小。
ReDim语句在过程级别中使用，用于为动态数组变量重新分配存储空间。其语法格式如下：
ReDim [Preserve] 数组名(下标) [As 数据类型]
可以使用ReDim语句反复地改变数组的元素以及维数的数目，但是不能在将一个数组定义为某种数据类型之后，再使用ReDim将该数组改为其他数据类型，除非是Variant所包含的数组。
在默认情况下，使用ReDim语句重定义数组的维数和大小时，数组中原来保存的值将全部消失，如果使用Preserve关键字，当改变原有数组最后一维的大小时，可以保持数组中原来的数据。
如果使用了Preserve关键字，就只能重新定义数组最后一维的大小，并不能改变维数的数目。
（2）随机函数Rnd
随机函数Rnd可返回小于1但大于或等于0的一个小数。其语法格式如下：
Rnd[(number)]
可选的number参数是Single或任何有效的数值表达式。根据number参数值的不同，Rnd函数生成的随机数也不同：
— number<0，则每次使用相同的number作为随机数种得到的相同结果。
— number>0，则将生成随机序列中的下一个随机数。
— number=0，则将生成最近生成的数。
— 省略number，则生成序列中的下一个随机数。
—在调用Rnd之前，先使用无参数的Randomize语句初始化随机数生成器，该生成器具有根据系统计时器得到的种子。
为了生成某个范围内的随机整数，可使用以下公式：
Int((上限 – 下限 + 1) * Rnd + 下限)
3．编写代码
（1）新建Excel工作簿，按快捷键“Alt+F11”进入VBE环境。
（2）单击菜单“插入/模块”命令向工程中插入一个模块。
（3）在模块中编写以下代码：
Option Base 1
Sub 幸运号码()
Dim n As Integer, i As Integer, j As Integer
Dim l() As Integer
n = Application.InputBox("请输入需要产生幸运号码的数量：", "幸运号码", , , , , , 2)
ReDim l(n, 7) As Integer
For i = 1 To n
For j = 1 To 7
Randomize
l(i, j) = Int(10 * Rnd)
Next
Next
For i = 1 To n
For j = 1 To 7
Debug.Print l(i, j);
Next
Debug.Print
Next
End Sub
（4）运行上面的宏，弹出如图3-35所示的对话框，提示用户输入数据。输入生成幸运号码的数量。
（5）单击“确定”按钮后在“立即窗口”输出生成的幸运号码，如图3-36所示。
图3-35 输入数据 图3-36 生成幸运号码
例031 用数组填充单元格区域
1．案例说明
在Excel中要处理大量数据时，可使用循环从各单元格读入数据，经过加工处理后再写回单元格区域中。这种方式比在数组中处理数据的速度要慢。因此，如果有大量的数据需要处理时，可先将数据保存到数组中，经过加工处理后，再将数组的数据填充到单元格区域。
本例演示将二维数组中的数据填充到工作表中的方法。
2．关键技术
在Excel工作表中，工作表是一个二维结构，由行和列组成。这种特性与二维数组类似，因此可以很方便地将工作表单元格区域与二维数组之间进行转换。通过以下语句可将单元格区域赋值给一个二维数组：
myarr = Range(Cells(1, 1), Cells(5, 5))
反过来，也可将二维数组中的值快速的赋值给一个单元格区域，如以下语句将二维数组myarr中的值赋值给单元格区域Rng：
Rng.Value = arr
3．编写代码
（1）新建Excel工作簿，按快捷键“Alt+F11”进入VBE环境。
（2）单击菜单“插入/模块”命令向工程中插入一个模块。
（3）在模块中编写以下代码：
Option Base 1
Sub 数组填充单元格区域()
Dim i As Long, j As Long
Dim col As Long, row As Long
Dim arr() As Long
row = Application.InputBox(prompt:="输入行数：", Type:=2)
col = Application.InputBox(prompt:="输入列数：", Type:=2)
ReDim arr(row, col)
For i = 1 To row
For j = 1 To col
arr(i, j) = (i - 1) * col + j
Next
Next
Set Rng = Sheets(1).Range(Cells(1, 1), Cells(row, col))
Rng.Value = arr
End Sub
（4）返回Excel操作环境，向工作表中添加一个按钮，设置提示文字为“填充数据”，指定该按钮的宏为“数组填充单元格区域”。
（5）单击“填充数据”按钮，弹出如图3-37所示对话框，分别输入数组的行和列。
图3-37 输入行和列
（6）VBA代码生成一个二维数组，最后填充到工作表中，如图3-38所示。
图3-38 填充数据
通过Excel相关对象可对工作表中的数据进行操作，如处理单元格区域的公式、对数据进行查询、排序、筛选等操作。本章演示使用VBA进行处理数据的实例。
12.1 处理公式
使用VBA代码可对工作表中的公式单元格进行处理，如判断单元格是否包含公式、复制公式、将单元格公式转换为具体的值等。
例254 判断单元格是否包含公式
1．案例说明
打开本例工作簿如图12-1所示，单击左上角的“公式单元格”按钮，将弹出如图12-1右图所示的提示框，显示当前工作表中定义了公式的单元格。
图12-1 显示有公式的单元格
2．关键技术
本例使用Range对象的HasFormula属性来判断指定单元格是否包含公式，如果区域中所有单元格均包含公式，则该属性值为True；如果所有单元格均不包含公式，则该属性值为False；其他情况下为null。
本例对当前单元格区域中的单元格逐个进行判断，并显示出具有公式的单元格。
3．编写代码
“公式单元格”按钮的VBA代码如下：
Sub 显示公式单元格()
Dim rng As Range
Set rng = ActiveSheet.Range("A1").CurrentRegion
For Each c In rng.Cells
If c.HasFormula Then
MsgBox "单元格" & c.Address & " 定义了公式！"
End If
Next
End Sub
例255 自动填充公式
1．案例说明
打开本例工作簿如图12-2所示，在如图所示工作表中，单元格J3和D16定义了公式，单击“填充公式”按钮，单元格J3的公式将向下填充，单元格D16的公式向右填充，结果如图12-3所示。
图12-2 原工作表
图12-3 复制公式
2．关键技术
本例使用Range对象的AutoFill方法，对指定区域中的单元格执行自动填充。该方法的语法格式如下：
表达式.AutoFill(Destination, Type)
该方法有两个参数，其含义如下：
— Destination：要填充的单元格。目标区域必须包括源区域。
— Type：指定填充类型。该填充类型可使用xlAutoFillType枚举类型，其值如表12-1所示。
表12-1 xlAutoFillType枚举值
名 称
值
描 述
xlFillCopy
1
将源区域的值和格式复制到目标区域，如有必要可重复执行
xlFillDays
5
将星期中每天的名称从源区域扩展到目标区域中。格式从源区域复制到目标区域，如有必要可重复执行
xlFillDefault
0
Excel确定用于填充目标区域的值和格式
xlFillFormats
3
只将源区域的格式复制到目标区域，如有必要可重复执行
xlFillMonths
7
将月名称从源区域扩展到目标区域中。格式从源区域复制到目标区域，如有必要可重复执行
xlFillSeries
2
将源区域中的值扩展到目标区域中，形式为系列（如，“1, 2”扩展为“3, 4, 5”）。格式从源区域复制到目标区域，如有必要可重复执行
xlFillValues
4
只将源区域的值复制到目标区域，如有必要可重复执行
xlFillWeekdays
6
将工作周每天的名称从源区域扩展到目标区域中。格式从源区域复制到目标区域，如有必要可重复执行
xlFillYears
8
将年从源区域扩展到目标区域中。格式从源区域复制到目标区域，如有必要可重复执行
xlGrowthTrend
10
将数值从源区域扩展到目标区域中，假定源区域的数字之间是乘法关系（如，“1, 2,”扩展为“4, 8, 16”，假定每个数字都是前一个数字乘以某个值的结果）。格式从源区域复制到目标区域，如有必要可重复执行
xlLinearTrend
9
将数值从源区域扩展到目标区域中，假定数字之间是加法关系（如，“1, 2,”扩展为“3, 4, 5”，假定每个数字都是前一个数字加上某个值的结果）。格式从源区域复制到目标区域，如有必要可重复执行
3．编写代码
“填充公式”按钮的VBA代码如下：
Sub 填充公式()
Dim i As Long, j As Long
With Range("A1").CurrentRegion
i = .Rows.Count - 1
j = .Columns.Count - 1
End With
Range("J3").AutoFill _
Destination:=Range(Cells(3, 10), Cells(i, 10))
Range("D16").AutoFill _
Destination:=Range(Cells(16, 4), Cells(16, j))
End Sub
以上代码首先获取当前区域的行和列，接着使用AutoFill方法在垂直方向和水平方向填充相应的公式。
例256 锁定和隐藏公式
1．案例说明
打开本例工作簿如图12-4所示，单击“锁定隐藏公式”按钮，当前工作表中的所有公式单元格将被锁定，不允许用户修改，而其他单元格的数据用户可进行修改。同时，公式单元格定义的公式将被隐藏，单击选取具有公式的单元格时，将不会显示公式。
图12-4 锁定和隐藏公式
2．关键技术
要锁定和隐藏单元格，可通过Range对象的以下两个属性来进行设置。
— Locked属性：指明对象是否已被锁定。
— FormulaHidden属性：指明在工作表处于保护状态时是否隐藏公式。
当设置以上两个属性为True时，对指定区域锁定和隐藏。但要真正锁定和隐藏单元格，必须使用Protect方法对工作表进行保护。
3．编写代码
“锁定隐藏公式”按钮的VBA代码如下：
Sub 锁定和隐藏公式()
If ActiveSheet.ProtectContents = True Then
MsgBox "工作表已保护！"
Exit Sub
End If
Worksheets("Sheet1").Range("A1").CurrentRegion.Select
Selection.Locked = False
Selection.FormulaHidden = False
Selection.SpecialCells(xlCellTypeFormulas).Select
Selection.Locked = True
Selection.FormulaHidden = True
Worksheets("Sheet1").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Worksheets("Sheet1").EnableSelection = xlNoRestrictions
End Sub
例257 将单元格公式转换为数值
1．案例说明
打开本例工作簿如图12-5所示，在当前工作表中单元格区域“J3:J15”和“D16:I15”中都定义了公式，单击选择这两个区域中的任意一个单元格，编辑栏中将显示该单元格的公式。
单击工作表左上角的“公式转为数值”按钮，当前工作表中所有公式单元格的公式定义都将被具体计算值所替代，这时再修改引用单元格的值，这两个区域的值不会再变化了。
图12-5 将公式转为数值
2．关键技术
将单元格公式转换为计算结果的表示方法很简单，只需通过以下的赋值运算即可：
rng.Value = rng.Value
以上赋值语句中，rng表示Range对象，该语句首先通过右侧的表达式rng.Value获取指定单元格的值（如果是公式，则获取公式的计算结果），再将该值赋值给单元格的Value变量，从而取代单元格原有的内容（公式）。
3．编写代码
“公式转为数值”按钮的VBA代码如下：
Sub 公式转为数值()
Dim rng As Range, c As Range
Set rng = ActiveSheet.Range("A1").CurrentRegion
For Each c In rng.Cells
If c.HasFormula Then
c.Value = c.Value
End If
Next
End Sub
以上代码首先获取工作表的当前区域，再逐个单元格判断，如果单元格有公式，则进行转换。
例258 删除所有公式
1．案例说明
在Excel中，当单元格的数据发生改变后，引用该单元格的公式单元格的值也会随之变化。有时希望经过计算后，具有公式的单元格的值不再随着引用单元格而变化。这时可以删除工作表中的公式，取消与引用单元格的关联。
打开本例工作簿如图12-6所示，在如图所示的工作表中部分单元格具有公式，单击选择单元格I16，在编辑栏中可看到具体的公式。
图12-6 具有公式的工作表
单击“删除所有公式”按钮，将打开如图12-7所示的对话框，询问用户是否删除提示工作簿中的所有公式，单击“是”按钮工作簿中各工作表中的公式都将被删除，如图12-8所示选中单元格I16，编辑栏中可以看到显示的是具体的值，公式已被删除。
图12-7 确认操作
图12-8 删除公式的工作表
2．关键技术
本例代码与上例类似，不同的是本例将对所有打开工作簿进行处理，对每个工作簿的每张工作表进行循环，将具有公式的单元格转换为具体的数值。
3．编写代码
“删除所有公式”按钮的VBA代码如下：
Sub 删除所有公式()
Dim wb1 As Workbook, ws1 As Worksheet
Dim rng As Range, rng1 As Range
For Each wb1 In Workbooks
With wb1
If MsgBox("是否删除工作簿“" & wb1.Name & "”中的所有公式？", _
vbQuestion + vbYesNo) = vbYes Then
For Each ws1 In .Worksheets
On Error Resume Next
Set rng1 = ws1.UsedRange.SpecialCells(xlCellTypeFormulas)
'获取公式单元格区域引用
For Each rng In rng1
rng.Value = rng.Value '将公式转换成数值
Next
Next
End If
End With
Next
End Sub
例259 用VBA表示数组公式
1．案例说明
打开本例工作簿如图12-9所示。在Excel中，可以通过定义数组公式计算销售总金额。但是如果销售日报表中销售商品的数量不确定（占用表格的行是动态的），使用固定的数组公式就不太方便。
图12-9 销售日报表
本例使用VBA动态定义数组公式，在图12-9所示工作表中输入数据，然后单击“汇总金额”按钮，在单元格F5中将根据录入数据的行数自动生成数组公式，如图12-10所示，在编辑栏可看到数组公式为：
{=SUM(B4:B9*C4:C9)}
2．关键技术
使用Range对象的FormulaArray属性，可获取或设置区域的数组公式。如果指定区域不包含数组公式，则该属性返回null。
图12-10 生成数组公式
3．编写代码
“汇总金额”按钮的VBA代码如下：
Sub 汇总金额()
Dim r As Long
r = ActiveSheet.Range("A3").End(xlDown).Row
Range("F5").FormulaArray = "=SUM(B4:B" & r & "*C4:C" & r & ")"
End Sub
12.2 数据查询
在Excel中，数据查询是最常用的操作。在“开始”选项卡的“编辑”组中单击“查找和选择”按钮，从下拉的菜单按钮中选择相应的命令即可进行查询操作。在VBE中，可使用Find方法进行查询相关的操作，本节实例演示查询数据的VBA代码。
例260 查找指定的值
1．案例说明
打开本例工作簿如图12-11所示，单击左上角的“查找”按钮，弹出“查找”对话框如图12-12所示，在该对话框中输入要查找的值（如本例中输入200），单击“确定”按钮，查找的结果显示在如图12-13所示的对话框中，同时工作表中对应单元格也加亮显示，如图12-14所示。
图12-11 查找工作表 图12-12 输入查找值
图12-13 查找结果 图12-14 加亮显示
2．关键技术
本例的查找使用了Range对象的两个方法：Find方法和FindNext方法。
（1）Find方法
使用该方法可以在区域中查找特定信息。其语法格式如下：
表达式.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
该方法的参数很多，其中What参数是必须指定的，其余参数都可省略。各参数的含义如下：
— What：要搜索的数据。可为字符串或任意Excel数据类型。
— After：表示搜索过程将从其之后开始进行的单元格。此单元格对应于从用户界面搜索时的活动单元格的位置。After必须是区域中的单个单元格。要记住搜索是从该单元格之后开始的；直到此方法绕回到此单元格时，才对其进行搜索。如果不指定该参数，搜索将从区域的左上角的单元格之后开始。
— LookIn：信息类型。
— LookAt：设置匹配文本的方式。可为常量xlWhole（匹配全部搜索文本）或xlPart（匹配任一部分搜索文本）。
— SearchOrder：指定搜索区域的次序。可为常量xlByRows（按行）或xlByColumns（按列）搜索。
— SearchDirection：搜索的方向。可为常量xlNext（在区域中搜索下一匹配值）或xlPrevious（在区域中搜索上一匹配值）。
— MatchCase ：如果为True，则搜索区分大小写。默认值为False。
— MatchByte：只在已经选择或安装了双字节语言支持时适用。如果为True，则双字节字符只与双字节字符匹配。如果为False，则双字节字符可与其对等的单字节字符匹配。
— SearchFormat：搜索的格式。
使用该方法将返回一个Range对象，它代表第一个在其中找到该信息的单元格。如果未发现匹配项，则返回Nothing。Find方法不影响选定区域或当前活动的单元格。
—
每次使用此方法后，参数LookIn、LookAt、SearchOrder和MatchByte的设置都将被保存。如果下次调用此方法时不指定这些参数的值，就使用保存的值。设置这些参数将更改“查找”对话框中的设置，如果省略这些参数，更改“查找”对话框中的
— 设置将更改使用的保存值。要避免出现这一问题，每次使用此方法时最好明确设置这些参数。
（2）FindNext方法
FindNext方法继续由Find方法开始的搜索。查找匹配相同条件的下一个单元格，并返回表示该单元格的Range对象。该操作不影响选定内容和活动单元格。其语法格式如下：
表达式.FindNext(After)
参数After指定一个单元格，查找将从该单元格之后开始。此单元格对应于从用户界面搜索时的活动单元格位置。
—After必须是查找区域中的单个单元格。搜索是从该单元格之后开始的；直到本方法环绕到此单元格时，才检测其内容。如果未指定本参数，查找将从区域的左上角单元格之后开始。
当查找到指定查找区域的末尾时，FindNext方法将环绕至区域的开始继续搜索。发生环绕后，为停止查找，可保存第一次找到的单元格地址，然后测试下一个查找到的单元格地址是否与其相同。
3．编写代码
“查找”按钮的VBA代码如下：
Sub 查找指定值()
Dim result As String, str1 As String, str2 As String
Dim c As Range
result = Application.InputBox(prompt:="请输入要查找的值：", Title:="查找", Type:=2)
If result = "False" Or result = "" Then Exit Sub
Application.ScreenUpdating = False
Application.DisplayAlerts = False
With ActiveSheet.Cells
Set c = .Find(result, , , xlWhole, xlByColumns, xlNext, False)
If Not c Is Nothing Then
str1 = c.Address
Do
c.Interior.ColorIndex = 4 '加亮显示
str2 = str2 & c.Address & vbCrLf
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> str1
End If
End With
MsgBox "查找到指定数据在以下单元格中：" & vbCrLf & vbCrLf _
& str2, vbInformation + vbOKOnly, "查找结果"
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
以上代码首先让用户输入查找的值，接着使用Find方法查找第一个满足条件的单元格，再使用循环查找当前工作簿中下一个满足条件的单元格，并在循环中对满足条件的单元格设置不同的底纹，以突出显示。
例261 带格式查找
1．案例说明
打开本例工作簿如图12-15所示，单击左上角的“查找指定格式”按钮，单元格A2将被选中，并填上不同的底色。
图12-15 带格式查找
2．关键技术
本例使用Application对象的FindFormat属性，设置要查找的单元格格式类型的搜索条件，然后使用Find方法按格式进行查找。
3．编写代码
“查找指定格式”按钮的VBA代码如下：
Sub 查找指定格式()
With Application.FindFormat.Font
.Name = "宋体"
.FontStyle = "Bold"
.Size = 11
End With
Cells.Find(what:="", SearchFormat:=True).Activate
Selection.Interior.ColorIndex = 4 '加亮显示
End Sub
以上代码首先使用FindFormat属性设置查找的格式条件，接着使用Find方法按格式查找并激活满足条件的单元格，最后加亮显示激活单元格。
例262 查找上一个/下一个数据
1．案例说明
打开本例工作簿如图12-16所示，单击右上角的“查找”按钮，将弹出输入查找条件对话框，在对话框中输入查找条件单击“确定”按钮，即可在当前工作表中查找满足条件的单元格，找到满足条件的单元格后，选中该单元格。
单击“向前查找”或“向后查找”按钮，可从当前单元格向前或向后查找满足前面设置条件的单元格，并选中该单元格。
如果在使用“查找”按钮输入查找条件之前，就直接单击“向前查找”或“向后查找”按钮，也将弹出如图12-17所示的“查找”对话框输入查询条件。
图12-16 查找上一下/下一个数据 图12-17 输入查找条件
要重设查找条件，单击“查找”按钮打开如图12-17所示对话框即可。
2．关键技术
（1）FindNext方法
使用该方法继续由Find方法开始的搜索。查找匹配相同条件的下一个单元格，并返回表示该单元格的Range对象。该方法的语法格式如下：
表达式.FindNext(After)
参数After指定一个单元格，查找将从该单元格之后开始。此单元格对应于从用户界面搜索时的活动单元格位置。After必须是查找区域中的单个单元格。搜索是从该单元格之后开始的；直到本方法环绕到此单元格时，才检测其内容。如果未指定本参数，查找将从区域的左上角单元格之后开始。
当查找到指定查找区域的末尾时，本方法将环绕至区域的开始继续搜索。发生环绕后，为停止查找，可保存第一次找到的单元格地址，然后测试下一个查找到的单元格地址是否与其相同。
（2）FindPrevious方法
该方法继续由Find方法开始的搜索。查找匹配相同条件的上一个单元格，并返回代表该单元格的Range对象。其语法格式如下：
表达式.FindPrevious(After)
参数After指定一个单元格，查找将从该单元格之前开始。此单元格对应于从用户界面搜索时的活动单元格的位置。
3．编写代码
（1）在VBE中插入一个模块，使用以下代码声明一个模块变量：
Dim c As Range
（2）“查找”按钮的VBA代码如下：
Sub 查找()
result = Application.InputBox(prompt:="请输入要查找的值：", Title:="查找", Type:=2)
If result = "False" Or result = "" Then Exit Sub
Set c = ActiveSheet.Cells.Find(result, , , xlWhole, xlByColumns, xlNext, False)
If Not c Is Nothing Then
c.Activate
End If
End Sub
以上代码首先提示用户输入查询条件，再使用Find方法向下查找。
（3）“向前查找”按钮的VBA代码如下：
Sub 向前查找()
Dim result As String, str1 As String, str2 As String
If c Is Nothing Then
result = Application.InputBox(prompt:="请输入要查找的值：", Title:="查找", Type:=2)
If result = "False" Or result = "" Then Exit Sub
Set c = ActiveSheet.Cells.Find(result, , , xlWhole, xlByColumns, xlPrevious, False)
Else
Set c = ActiveSheet.Cells.FindPrevious(c)
End If
If Not c Is Nothing Then
c.Activate
End If
End Sub
以上代码首先判断模块变量c是否为空（判断执行该子过程之前是否设置了查询条件），若为空，则打开对话框让用户输入查询条件，并使用Find方法向前查找。若模块变量c不为空，则调用FindPrevious方法向前查找。
（4）“向后查找”按钮的VBA代码如下：
Sub 向后查找()
Dim result As String, str1 As String, str2 As String
If c Is Nothing Then
result = Application.InputBox(prompt:="请输入要查找的值：", Title:="查找", Type:=2)
If result = "False" Or result = "" Then Exit Sub
Set c = ActiveSheet.Cells.Find(result, , , xlWhole, xlByColumns, xlNext, False)
Else
Set c = ActiveSheet.Cells.FindNext(c)
End If
If Not c Is Nothing Then
c.Activate
End If
End Sub
例263 代码转换
1．案例说明
打开本例工作簿如图12-18所示，在单元格C3中输入“101”，按回车键或Tab键后，单元格C3中输入的值将转换为“财务部”，如图12-19所示。
图12-18 输入代码
单击工作表的“编码”标签，可看到编码表中编码与名称的对应关系，如图12-20所示。
图12-19 转换代码 图12-20 编码表
2．关键技术
本例使用查表的方法，将工作表中指定列中输入的代码转换为对应的值。在如图12-20所示的“编码”表中输入编码内容。
本例的关键技术是使用工作表事件Change事件来进行代码的转换。
当用户更改工作表中的单元格，或外部链接引起单元格的更改时发生Change事件。该事件的参数Target为数据正在被更改的区域。
3．编写代码
在工作表“Sheet1”的Change事件中编写以下VBA代码：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim t, rng As Range, i As Long, c As Range
If Target.Column = 3 And Target.Row > 2 And Target.Value <> "" Then
t = Target.Value
With Worksheets("编码")
i = .Range("A1").End(xlDown).Row
Set rng = .Range(.Cells(2, 1), .Cells(i, 1))
Set c = rng.Find(what:=t)
If c Is Nothing Then Exit Sub
Target.Value = c.Offset(0, 1).Value
End With
End If
End Sub
以上代码首先对更改单元格的行和列进行判断，如果是第3列第2行以下单元格，则执行编码转换的代码。在转换代码时先获取更改单元格的值，再从“编码”工作表中查找相应的编码，并将查到的编码对应的名称赋值给当前单元格，完成代码的转换。
例264 模糊查询
1．案例说明
打开本例工作簿如图12-21所示，单击“模糊查询”按钮，弹出如图12-22所示的对话框，在对话框中输入查询条件“刘”，单击“确定”按钮，即可在工作表中查找含有“刘”字的单元格，并为单元格填充底色，如图12-23所示。
图12-21 模糊查询 图12-22 查询条件
图12-23 加亮显示查询结果
2．关键技术
本例使用Like运算符进行模糊查询。Like运算符可用来比较两个字符串。其使用方法如下：
result = string Like pattern
Like运算符的语法具有以下几个部分：
— result：运算的结果。
— string：被查询的字符串。
— pattern：查询字符串，该字符串可建立模式匹配。
如果string与pattern匹配，则result为 True；如果不匹配，则result为False。但是如果string或pattern中有一个为Null，则result为Null。
pattern中的字符可使用以下匹配模式：
— ?：可为任何单一字符。
— *：零个或多个字符。
— #：任何一个数字(0–9)。
— [charlist]：charlist中的任何单一字符。
— [!charlist]：不在charlist中的任何单一字符。
在中括号([ ])中，可以用由一个或多个字符(charlist)组成的组与string中的任一字符进行匹配，这个组几乎包括任何一个字符代码以及数字。
例如：
MyCheck = "张三" Like "张*" ' 返回 True
MyCheck = "F" Like "[A-Z]" ' 返回 True
MyCheck = "F" Like "[!A-Z]" ' 返回 False
MyCheck = "a2a" Like "a#a" ' 返回 True
3．编写代码
“模糊查询”按钮的VBA代码如下：
Sub 模糊查询()
Dim result As String, str1 As String
Dim c As Range, rng As Range
result = Application.InputBox(prompt:="请输入要查找的值：", _
Title:="模糊查找", Type:=2)
If result = "False" Or result = "" Then Exit Sub
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set rng = ActiveSheet.Range("A1").CurrentRegion
str1 = "*" & result & "*"
For Each c In rng.Cells
If c.Value Like str1 Then
c.Interior.ColorIndex = 4
End If
Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
以上代码首先让用户输入查询条件，接着使用For循环逐个单元格进行比较，在比较时使用Like进行模糊查询，如果单元格中包含有指定条件的值，则设置单元格的底色。
例265 网上查询快件信息
1．案例说明
使用本例代码可查询申通快递的快件投递情况。打开本例工作簿如图12-24所示，单击“查询快件”按钮打开如图12-25所示对话框，在对话框中输入快件编号，单击“确定”按钮，经过一段时间后得到查询结果如图12-26所示。
图12-24 查询工作表 图12-25 输入快件编号
图12-26 查询结果
—本例使用的快件编号进行了处理（虚拟编号），在使用本例代码之前应确保计算已接入互联网。
2．关键技术
（1）QueryTable对象
QueryTable对象代表一个利用从外部数据源（如SQL Server、Microsoft Access数据库、网络数据等）返回的数据生成的工作表表格。
QueryTable对象是QueryTables集合的成员。
（2）Add方法
使用QueryTables集合对象的Add方法可新建一个查询表。其语法格式如下：
表达式.Add(Connection, Destination, Sql)
该方法参数的含义如下：
— Connection：查询表的数据源。可为连接数据库的连接字符串，也可以是一个Web查询。Web查询字符串的格式如下：
URL;
其中“URL;”是必需的，字符串的其余部分作为Web查询的URL。
— Destination：查询表目标区域（生成的查询表的放置区域）左上角的单元格。目标区域必须位于QueryTables对象所在的工作表中。
— Sql：在ODBC数据源上运行的SQL查询字符串。当使用的数据源为ODBC数据源时，该参数可省略。
（3）Refresh方法
使用QueryTable对象的Refresh方法可更新外部数据区域(QueryTable)。该方法的语法格式如下：
表达式.Refresh(BackgroundQuery)
参数BackgroundQuery如果为True，则在数据库建立连接并提交查询之后，将控制返回给过程。QueryTable在后台进行更新。如果为False，则在所有数据被取回到工作表之后，将控制返回给过程。如果没有指定该参数，则由BackgroundQuery属性的设置决定查询模式。
在Excel建立一个成功的连接之后，将存储完整的连接字符串，这样，以后在同一编辑会话中调用Refresh方法时就不会再显示提示。通过检查Connection属性的值可以获得完整的连接字符串。
如果成功地完成或启动查询，则Refresh方法返回True；如果用户取消连接或参数对话框，该方法返回False。
（4）使用Web查询
在申能快递的网站上可查询快件的投递情况，在浏览器中输入以下网址：
http://www.sto.cn/querybill/webform1.aspx?wen=&Submit2=%B2%E9%D1%AF
将打开如图12-27所示的查询页面，在文本区中输入快件编号，单击“查询”按钮即可在网页上显示指定编号的快件投递情况。
图12-27 通过网页查询快件投递情况
如果要在Excel中通过VBA查询快件投递情况，只需要将前面的URL地址中的“wen=”字符串后面加上快件编号即可。
3．编写代码
“查询快件”按钮的VBA代码如下：
Sub 查询快件()
Dim str As String, strURL As String
str = Application.InputBox(prompt:="请输入快件的编号：", _
Title:="申通快件查询", Type:=2)
If str = "False" Then Exit Sub
strURL = "URL;http://www.sto.cn/querybill/webform1.aspx?wen="
strURL=strURL & str & "&Submit2=%E6%9F%A5%E8%AF%A2"
With ActiveSheet.QueryTables.Add(Connection:=strURL, Destination:=Range("A2"))
.Name = "abc"
.FieldNames = True
.WebSelectionType = xlSpecifiedTables '导入指定表
.WebFormatting = xlWebFormattingNone '不导入任何格式
.WebTables = "1,2" '导入第一个和第二个表格中的数据
.BackgroundQuery = True '查询异步执行（在后台执行）
.Refresh BackgroundQuery:=False '更新数据
End With
End Sub
例266 查询基金信息
1．案例说明
打开本例工作簿，单击“查询基金信息”按钮，将在当前工作表中显示当前基金的信息如图12-28所示。
图12-28 基金信息
2．关键技术
在网站http://tw.stock.yahoo.com/us/worldinx.html中可查询基金的信息，如图12-29所示。
在图12-29所示的基金信息网页中，上面用6个表格显示了一些超链接信息。最下方的表格显示具体各基金的数据，本例通过Web查询只需要获取下方的表格即可。通过查看HTML代码，可知该表格是第7个表格，所以需要设置QueryTable对象的WebTables属性为7。
3．编写代码
“查询基金信息”按钮的VBA代码如下：
Sub 查询基金信息()
Dim strURL As String
strURL = "URL;http://fund.sohu.com/r/cxo.php"
With ActiveSheet.QueryTables.Add(Connection:=strURL, Destination:=Range("A2"))
.Name = "worldinx"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "7"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
图12-29 网站查询基金信息
例267 查询手机所在地
1．案例说明
打开本例工作簿如图12-30所示，单击“手机所在地”按钮打开如图12-31所示对话框，输入手机号码后，单击“确定”按钮即可查询出手机所在地，如图12-32所示。
2．关键技术
本例与前面各例使用的Web查询不同。本例使用http://www.123cha.com/网站来查询手机所在地。其查询的HTML代码如下：
图12-30 查询手机所在地 图12-31 输入手机号码
图12-32 手机所在地
从以上HTML代码可以看出，查询手机所在地使用的是POST方法（另一种方式是GET方式，前面两例使用的这种方式），这种方法将传递一个查询变量到目标页面，需要提供以下两个参数：
— 第一个是查询页面，即QueryTable对象的Connection参数。该参数应该是
3.1 常用输入/输出语句
结构化程序设计中使用的基本控制结构有3种：顺序结构、选择结构和循环结构。顺序结构就是按照语句的书写顺序从上到下、逐条语句地执行。执行时，编写在前面的代码先执行，编写在后面的代码后执行。这是最普遍的结构形式，也是后面两种结构的基础。
顺序结构不需要使用结构控制语句，本节介绍常用的输入输出语句的技巧。
例017 九九乘法表（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）在模块中输入以下代码：
Sub multi()
For i = 1 To 9
For j = 1 To i
Debug.Print i; "x"; j; "="; i * j; " ";
Next
Debug.Print '换行
Next
End Sub
（3）按功能键“F5”运行子过程，在“立即窗口”输出九九乘法表，如图3-1所示。
图3-1 立即窗口
例018 输入个人信息（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）在模块中输入以下代码：
Sub inputinfo()
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
End Sub
（3）按功能键“F5”运行子过程，将弹出“输入个人信息”窗口，如图3-2所示。在对话框中输入内容后按“回车”，或单击“确定”按钮。
（4）接着输入“年龄”和“地址”信息，在“立即窗口”中将输出这些内容，如图3-3所示。
图3-2 输入个人信息 图3-3 输出结果
例019 退出确认（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
显示Critical Message图标
vbQuestion
32
显示Warning Query图标
vbExclamation
48
显示Warning Message图标
vbInformation
64
显示Information Message图标
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所示。代码窗口中将自动生成事件过程结构如下：
图3-5 对象列表 图3-6 事件列表
Private Sub Workbook_BeforeClose(Cancel As Boolean)
End Sub
（4）在上面生成的事件过程中输入以下代码：
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim intReturn As Integer
intReturn = MsgBox("真的退出系统吗？", vbYesNo + vbQuestion, "提示")
If intReturn <> vbYes Then Cancel = True
End Sub
（5）保存Excel工作簿。（6）关闭Excel工作簿时，将弹出如图3-7所示的对话框。单击“是”按钮将退出Excel，单击“否”按钮将返回Excel工作簿。
3.2 分支结构
分支结构，又叫选择结构。这种结构的程序将根据给定的条件来决定执行哪一部分代码，而跳过其他代码。
例020 突出显示不及格学生
1．案例说明
本例判断学生成绩表中的成绩，如果成绩不及格（低于60分），则将该成绩着重显示出来。如图3-8所示（左图为原成绩，右图突出显示不及格成绩）。
图3-8 突出显示不及格学生
2．关键技术
在本例中，需要进行一个判断（成绩是否低于60分），这时可使用If…Then语句。用If…Then语句可有条件地执行一个或多个语句。其语法格式如下：
If 逻辑表达式 Then
语句1
语句1
… …
语句n
End If
逻辑表达式也可以是任何计算数值的表达式，VBA将为零（0）的数值看做False，而任何非零数值都被看做True。该语句的功能为：若逻辑表达式的值是True，则执行位于Then与End If之间的语句；若逻辑表达式的值是False，则不执行Then与End If之间的语句，而执行End If后面的语句。其流程图如图3-9所示。
If…Then结构还有一种更简单的形式：单行结构条件语句。其语法格式如下：
If 逻辑表达式 Then 语句
该语句的功能为：若逻辑表达式的值是True，则执行Then后的语句；若逻辑表达式的值是False，则不执行Then后的语句，而执行下一条语句。
3．编写代码
（1）打开“学生成绩表”。
（2）按快捷键“Alt+F11”进入VBE环境。
（3）单击菜单“插入/模块”命令向工程中插入一个模块，并编写以下代码：
Sub 显示不及格学生()
Dim i As Integer
For i = 3 To 11
If Sheets(1).Cells(i, 2).Value < 60 Then
Sheets(1).Cells(i, 2).Select
Selection.Font.FontStyle = "加粗"
Selection.Font.ColorIndex = 3
End If
Next
End Sub
（4）关闭VBE开发环境返回Excel。
（5）在功能区“开发工具”选项卡的“控件”组中，单击“插入”按钮弹出“表单控件”面板，如图3-10所示。
图3-10 插入按钮
（6）在“表单控件”面板中单击“按钮”，拖动鼠标在工作表中绘制一个按钮。当松开鼠标时，将弹出“指定宏”对话框，如图3-11所示。
（7）在“指定宏”对话框中，单击选中“显示不及格学生”宏，单击“确定”按钮。
（8）右击工作表中的按钮，弹出快捷菜单如图3-12所示，单击“编辑文字”菜单，修改按钮中的提示文字为“显示不及格学生”。
图3-11 指定宏 图3-12 编辑文字
（9）单击“显示不及格学生”按钮，执行宏代码，成绩表中不及格成绩将突出显示为粗体、红色，如图3-13所示。
图3-13 执行程序
例021 从身份证号码中提取性别
1．案例说明
在很多信息系统中都需要使用到身份证号码，身份证号码中包含有很多信息，如可从其中提取性别。我国现行使用的身份证号码有两种编码规则，即15位居民身份证和18位居民身份证。
15位的身份证号的编码规则。
dddddd yymmdd xx p
18位的身份证号的编码规则。
dddddd yyyymmdd xx p y
其中：
— dddddd为地址码（省地县三级）18位中的和15位中的不完全相同。
— yyyymmdd yymmdd 为出生年月日。
— xx序号类编码。
— p性别。
— 18位中末尾的y为校验码。
2．关键技术
在If…Then语句中，条件不成立时不执行任何语句。在很多时候需要根据条件是否成立分别执行两段不同的代码，这时可用If…Then…Else语句，其语法格式如下：
If 逻辑表达式 Then
语句序列1
Else
语句序列2
End If
VBA判断“逻辑表达式”的值，如果它为True，将执行“语句序列1”中的各条语句，当“逻辑表达式”的值为False时，就执行“语句序列2”中的各条语句。其流程图如图3-14所示。3．编写代码
（1）新建Excel工作簿，在VBE中插入一个模块。
（2）在模块中编写以下代码：
Sub 根据身份证号码确定性别()
sid = InputBox("请输入身份证号码：")
i = Len(sid)
If i <> 15 And i <> 18 Then '判断身份证号长度是否正确
MsgBox "身份证号码只能为15位或18位！"
Exit Sub
End If
If i = 15 Then '长度为15位
s = Right(sid, 1) '取最右侧的数字
Else '长度为18度
s = Mid(sid, 17, 1) '取倒数第2位数
End If
If Int(s / 2) = s / 2 Then '为偶数
sex = "女"
Else
sex = "男"
End If
MsgBox "性别：" + sex
End Sub
（3）切换到Excel环境，添加一个按钮“从身份证号码提取性别”，并指定执行上步创建的宏。
（4）单击“从身份证号码提取性别”按钮，弹出如图3-15所示对话框。
（5）输入身份证号码后单击“确定”按钮，将在如图3-16所示对话框中显示性别。
图3-15 输入身份证号码 图3-16 显示性别
例022 评定成绩等级
1．案例说明
本例将成绩表中的百分制成绩按一定规则划分为A、B、C、D、E五个等级，如图3-17所示。
图3-17 评定成绩等级
其中各等级对应的成绩分别为：
— A：大于等于90分；
— B：大于等于80分，小于90分；
— C：大于等于70分，小于80分；
— D：大于等于60分，小于70分；
— E：小于60分。
2．关键技术
本例共有五个分支，使用If…Then…Else这种二路分支结构也可完成，但需要复杂的嵌套结构才能解决该问题。其实VBA中提供了一种If…Then…ElseIf的多分支结构，其语法格式如下：
If 逻辑表达式1 Then
语句序列1
ElseIf 逻辑表达式2 Then
语句序列2.
ElseIf 逻辑表达式3 Then
语句序列3
... …
Else
语句序列n
End If
在以上结构中，可以包括任意数量的ElseIf子句和条件，ElseIf子句总是出现在Else子句之前。
VBA首先判断“逻辑表达式1”的值。如果它为False，再判断“逻辑表达式2”的值，依此类推，当找到一个为True的条件，就会执行相应的语句块，然后执行End If后面的代码。如果所有“逻辑表达式”都为False，且包含Else语句块，则执行Else语句块。其流程图如图3-18所示。
图3-18 If…Then…ElseIf语句流程图
3．编写代码
（1）在Excel中打开成绩表。
（2）按快捷键“Alt+F11”进入VBE开发环境。
（3）单击“插入/模块”命令向工程中插入一个模块，并编写以下VBA代码：
Sub 评定等级()
Dim i As Integer
For i = 3 To 11
t = Sheets(1).Cells(i, 2).Value '取得成绩
If t >= 90 Then
j = "A"
ElseIf t >= 80 Then
j = "B"
ElseIf t >= 70 Then
j = "C"
ElseIf t >= 60 Then
j = "D"
Else
j = "E"
End If
Sheets(1).Cells(i, 3) = j
Next
End Sub
（4）返回Excel操作界面，在成绩表旁边增加一个按钮，并指定执行宏“评定等级”。
（5）单击“评定等级”按钮，即可在成绩表的C列显示出各成绩对应的等级，如图3-17所示。
例023 计算个人所得税
1．案例说明
在工资管理系统中，需要计算员工应缴纳的个人所得税。个人所得税税额按5%至45%的九级超额累进税率计算应缴税额，税率表如图3-19所示。
个人所得税的计算公式为：
应纳个人所得税税额=应纳税所得额×适用税率-速算扣除数
本例根据工资表中的相应数据计算出纳税额，并填充在工资表对应的列中。
图3-19 个人所得税税率表
2．关键技术
本例中计算个人所得税时共有九个分支。这时可在If…Then…ElseIf结构中添加多个ElseIf块来进行各分支的处理。对于多分支结构，可使用Select Case语句。Select Case语句的功能与If…Then…Else语句类似，但在多分支结构中，使用Select Case语句可使代码简洁易读。
Select Case结构的语法格式如下：
Select Case 测试表达式
Case 表达式列表1
语句序列1
Case 表达式列表2
语句序列2
… …
Case Else
语句序列n
End Select
在以上结构中，首先计算出“测试表达式”的值，然后，VBA将表达式的值与结构中的每个Case的值进行比较。如果相等，就执行与该Case语句下面的语句块，执行完毕再跳转到End Select语句后执行。其流程图如图3-20所示。
图3-20 Select Case语句流程图
在Select Case结构中，“测试表达式”通常是一个数值型或字符型的变量。“表达式列表”可以是一个或几个值的列表。如果在一个列表中有多个值，需要用逗号将各值分隔开。表达式列表可以按以下几种情况进行书写：
— 表达式：表示一些具体的取值。例如：Case 10,15,25。
— 表达式A To 表达式B：表示一个数据范围。例如，Case 7 To 17表示7～17之间的值。
— Is 比较运算符表达式：表示一个范围。例如，Case Is>60 表示所有大于90的值。
— 以上三种情况的混合。例如，Case 4 To 10, 15, Is>20。
3．编写代码
（1）在Excel中打开工资表工作簿。
（2）按快捷键“Alt+F11”进入VBE开发环境。
（3）单击菜单“插入/模块”命令插入一个模块。
（4）在模块中编写以下函数，用来计算所得税：
Function 个人所得税(curP As Currency)
Dim curT As Currency
curP = curP – 1600 '1600为扣除数
If curP > 0 Then
Select Case curP
Case Is <= 500
curT = curP * 0.05
Case Is <= 2000
curT = (curP - 500) * 0.1 + 25
Case Is <= 5000
curT = (curP - 2000) * 0.15 + 125
Case Is <= 20000
curT = (curP - 5000) * 0.2 + 375
Case Is <= 40000
curT = (curP - 20000) * 0.25 + 1375
Case Is < 60000
curT = (curP - 40000) * 0.3 + 3375
Case Is < 80000
curT = (curP - 60000) * 0.35 + 6375
Case Is < 100000
curT = (curP - 80000) * 0.4 + 10375
Case Else
curT = (curP - 100000) * 0.45 + 15375
End Select
个人所得税 = curT
Else
个人所得税 = 0
End If
End Function
（5）在模块中编写“计算”子过程，计算工资表中每个员工应缴所得税额，并填写在对应的列中。
Sub 计算()
For i = 4 To 9
Sheets(1).Cells(i, 8).Value = 个人所得税(Sheets(1).Cells(i, 6).Value)
Next
End Sub
（6）返回到Excel环境中，在工资表下方插入一个按钮，为按钮指定宏为“计算”。
（7）单击“计算”按钮，可计算出每个员工的所得税额，如图3-21所示。
图3-21 计算所得税
3.3 循环结构
在实际开发的应用系统中，经常需要重复执行一条或多条语句。这种结构称为循环结构。循环结构的思想是利用计算机高速处理运算的特性，重复执行某一部分代码，以完成大量有规则的重复性运算。
VBA提供了多个循环结构控制语句：Do…Loop结构、While…Wend结构、For…Next结构、For Each…Next结构。
例024 密码验证
1．案例说明
在信息管理系统中，很多时候都需要用户进行登录操作。在登录操作时要求用户输入密码，一般都要给用户三次机会，每次的输入过程和判断过程都相同。
本例使用Do…Loop循环完成密码验证过程。
2．关键技术
在VBA中，最常用的循环语句是Do…Loop循环。循环结构Do While…Loop的语法格式如下：
Do While 逻辑表达式
语句序列1
[Exit Do]
[语句序列2]
Loop
其中Do While和Loop为关键字，在Do While和Loop之间的语句称为循环体。
当VBA执行这个Do循环时，首先判断“逻辑表达式”的值，如果为False（或零），则跳过所有语句，执行Loop的下一条语句，如果为True（或非零），则执行循环体，当执行到Loop语句后，又跳回到Do While语句再次判断条件。在循环体中如果包含有Exit Do语句，当执行到Exit Do语句，马上跳出循环，执行Loop的下一条语句。其流程图如图3-22所示。
图3-22 Do While…Loop流程图
VBA的Do…Loop循环有4种结构，分别如下：
— Do While…Loop循环：先测试条件，如果条件成立则执行循环体。
— Do…Loop While循环：先执行一遍循环体，再测试循环条件，如果条件成立则执行循环体。
— Do Until…Loop循环：先测试条件，如果条件不成立则执行循环体。
— Do…Loop Until循环：先执行一遍循环体，再测试循环条件，如果条件不成立则执行循环体。
3．编写代码
（1）新建Excel工作簿，按快捷键“Alt+F11”进入VBE开发环境。
（2）单击菜单“插入/模块”命令向工程中插入一个模块。
（3）在模块中编写以下VBA代码：
Sub login()
Dim strPassword As String '保存密码
Dim i As Integer '输入密码的次数
Do
strPassword = InputBox("请输入密码") '输入密码
If strPassword = "test" Then '判断密码是否正确
Exit Do '退出循环
Else
MsgBox ("请输入正确的密码！")
End If
i = i + 1
Loop While i < 3
If i >= 3 Then '超过正常输入密码次数
MsgBox "非法用户，系统将退出！"
Application.Quit
Else
MsgBox "欢迎你使用本系统！"
End If
End Sub
（4）返回Excel操作界面，在工作表中插入一个按钮，设置提示文字为“密码验证”，并为该按钮指定执行的宏为“login”。（5）单击“密码验证”按钮，弹出如图3-23所示对话框，输入密码后单击“确定”按钮进行密码的验证。
例025 求最小公倍数和最大公约数
1．案例说明
几个数公有的倍数叫做这几个数的公倍数，其中最小的一个叫做这几个数的最小公倍数。如12、18、20这三个数的最小公倍数为180。
最大公约数是指某几个整数的共有公约数中最大的那个数。如2、4、6这三个数的最大公约数为2。
本例使用辗转相除法求两个自然数m、n的最大公约数和最小公倍数。
2．关键技术
本例首先求出两数m、n的最大公约数，再将m、n数的乘积除以最大公约数，即可得到最小公倍数。求最大公约数的算法流程图如图3-24所示。
图3-24 最大公约数算法流程图
本例使用Do…Loop循环，并且没有设置循环条件。一般情况下，这种循环是一个死循环（也就是说程序将一直循环下去），因此，在这种循环结构中必须添加一个判断语句，当达到指定的条件时退出循环。如本例中使用以下语句退出循环：
If r = 0 Then Exit Do
3．编写代码
（1）新建Excel工作簿，按快捷键“Alt+F11”进入VBE环境。
（2）单击菜单“插入/模块”命令向工程中插入一个模块。
（3）在模块中编写以下子过程：
Sub 最小公倍数和最大公约数()
Dim m As Integer, n As Integer
Dim m1 As Integer, n1 As Integer
Dim t As Integer
m = InputBox("输入自然数m：")
n = InputBox("输入自然数n：")
m1 = m
n1 = n
If m1 < n1 Then
m1 = n
n1 = m '交换m和n的值
End If
Do
r = m1 Mod n1
If r = 0 Then Exit Do
m1 = n1
n1 = r
Loop
str1 = m & "," & n & "的最大公约数=" & n1 & vbCrLf
str1 = str1 & "最小公倍数=" & m * n / n1
MsgBox str1
End Sub
（4）返回Excel操作环境，向工作表中插入一个按钮，为按钮指定执行上步创建的宏。
（5）单击按钮，弹出如图3-25所示的输入提示框，分别输入两个数后，得到如图3-26所示的结果。
图3-25 输入数据 图3-26 最大公约数和最小公倍数
例026 输出ASCII码表
1．案例说明
目前计算机中用得最广泛的字符集及其编码，是由美国国家标准局（ANSI）制定的ASCII码。ASCII码由8位二进制组成，一共可包含256个符号。本例使用循环语句输出ASCII中的可见字符，如图3-27所示。
图3-27 ASCII码表
2．关键技术
使用Do…Loop循环时，可以不知道循环的具体次数。如果知道循环的次数，可以使用For…Next循环语句来执行循环。For循环的语法如下：
For 循环变量=初始值 To 终值 [Step 步长值]
语句序列1
[Exit For]
[语句序列2]
Next [循环变量]
在For循环中使用循环变量来控制循环，每重复一次循环之后，循环变量的值将与步长值相加。步长值可正可负，如果步长值为正，则初始值必须小于等于终值，才执行循环体，否则退出循环。如果步长值为负，则初始值必须大于等于终值，这样才能执行循环体。如果没有设置Step，则步长值默认为1。For…Next循环结构的流程图如图3-28所示。
For循环一般都可计算出循环体的执行次数，计算公式如下：
循环次数=[(终值-初值)/步长值]+1
这里用中括号表示取整。
在事先不知道循环体需要执行多少次时，应该用Do循环。而在知道循环体要执行的次数时，最好使用For…Next循环。
图3-28 For…Next流程图
3．编写代码
（1）新建Excel工作簿，按快捷键“Alt+F11”进入VBE环境。
（2）单击菜单“插入/模块”命令向工程中插入一个模块。
（3）在模块中编写以下子过程：
Sub ascii()
Dim a As Integer, i As Integer
i = 3
For a = 32 To 126
Sheets(1).Cells(i, 1) = a
Sheets(1).Cells(i, 2) = Chr(a)
i = i + 1
Next
End Sub
（4）返回Excel操作环境，向工作表中插入一个按钮，为按钮指定执行上步创建的宏。
（5）单击按钮，得到如图3-27所示的结果。
例027 计算选中区域数值之和
1．案例说明
在某些情况下，需要统计工作表中选定区域数值单元格的数值之和（例如，临时查看应发奖金之和），在Excel的状态栏就可查看选中单元格的数值之和。本例编写VBA代码，使用循环结构来完成该项功能。
2．关键技术
用户在Excel工作表中选定单元格的数量是不固定的，若需统计所选单元格数值之和，这时可使用For Each循环来进行处理，对选中区域的每个单元格进行判断，然后再累加数值单元格的值。
For Each…Next循环语句的语法格式如下：
For Each 元素 In 对象集合
[语句序列1]
[Exit For]
[语句序列2]
Next
使用For Each循环结构，可在对象集合每个元素中执行一次循环体。如果集合中至少有一个元素，就会进入For Each循环体执行。一旦进入循环，便先针对“对象集合”中第一个元素执行循环中的所有语句。如果“对象集合”中还有其他的元素，则会针对它们执行循环中的语句，当“对象集合”中的所有元素都执行完了，便会退出循环，然后从Next语句之后的语句继续执行。
在循环体中可以放置任意多个Exit For语句，随时退出循环。Exit For经常在条件判断之后使用，例如If…Then，并将控制权转移到紧接在Next之后的语句。
3．编写代码
（1）新建Excel工作簿，按快捷键“Alt+F11”进入VBE环境。
（2）单击菜单“插入/模块”命令向工程中插入一个模块。
（3）在模块中编写以下子过程：
Sub 求和()
Dim r
Dim t As Long
For Each r In Selection
If IsNumeric(r.Value) Then
t = t + r.Value
End If
Next
MsgBox "所选区域数值之和为：" & t
End Sub
（4）返回Excel操作环境，向工作表中插入一个按钮，修改按钮的提示字符为“求和”，为按钮指定执行上步创建的宏“求和”。
（5）在工作表“Sheet1”中输入数据，如图3-29左图所示。
（6）拖动鼠标选中如图3-29左图所示数据区域，单击“求和”按钮，求和结果将显示在如图3-29右图所示对话框中。
图3-29 计算选中区域数值之和
例028 换零钱法（多重循环）
1．案例说明
将十元钱换成1角、2角、5角、1元、2元、5元的零钱若干，求出一共有多少种方法进行计算？
2．关键技术
在VBA中，循环结构内的循环体又可以是循环结构，这种情况称为循环的嵌套。VBA允许在同一过程里嵌套多种类型的循环。
在编写嵌套循环程序的代码时，一定要注意每个循环语句的配对情况。如图3-30所示，其中左图是正确的嵌套关系，第一个Next关闭了内层的For循环，而最后一个Loop关闭了外层的Do循环。同样，在嵌套的If语句中，End If语句自动与最靠近的前一个If语句配对。嵌套的Do…Loop结构的工作方式也是一样的，最内圈的Loop语句与最内圈的Do语句匹配。图3-30右图则是错误的嵌套关系。
语句序列2
图3-30 正确的嵌套（左）与错误的嵌套（右）
3．编写代码
（1）新建Excel工作簿，按快捷键“Alt+F11”进入VBE环境。
（2）单击菜单“插入/模块”命令向工程中插入一个模块。
（3）零钱换法最简单的算法是：使用多重循环，将10元钱能换成的各种可能都考虑进去（如10可换为100个1角，可换为50个2角，等等）。根据这种算法在模块中编写以下子过程：
Sub 换零钱1()
Dim t As Integer
For i = 0 To 100 '1角
For j = 0 To 50 '2角
For k = 0 To 20 '5角
For l = 0 To 10 '1元
For m = 0 To 5 '2元
For n = 0 To 2 '5元
If i + 2 * j + 5 * k + 10 * l + 20 * m + 50 * n = 100 Then
t = t + 1
Sheets(1).Cells(t + 1, 1) = i
Sheets(1).Cells(t + 1, 2) = j
Sheets(1).Cells(t + 1, 3) = k
Sheets(1).Cells(t + 1, 4) = l
Sheets(1).Cells(t + 1, 5) = m
Sheets(1).Cells(t + 1, 6) = n
End If
Next
Next
Next
Next
Next
Next
MsgBox "10元换为零钱共有" & t & "种方法！"
End Sub
（4）运行该子过程，Excel工作表中每一行将填写一种可能的换法，如图3-31所示。
（5）因为换零钱的方法很多，根据计算机的速度不同该程序的运行速度也不同，最后将通过对话框显示出总的换法次数，如图3-32所示。
图3-31 零钱换法 图3-32 换法总数
（6）在循环嵌套中，内层循环体执行的次数等各外层循环数数之积，如本例代码内循环执行次数为：
101×51×21×11×6×3=21417858次
（7）对于嵌套循环，一般都可以对代码进行一定的优化，使程序的执行效率更高。本例最简单的优化代码如下：
Sub 换零钱2()
Dim t As Long
For j = 0 To 50 '2角
For k = 0 To 20 '5角
For l = 0 To 10 '1元
For m = 0 To 5 '2元
For n = 0 To 2 '5元
t2 = 2 * j + 5 * k + 10 * l + 20 * m + 50 * n
If t2 <= 100 Then
t = t + 1
i = 100 - t2
Sheets(1).Cells(t + 1, 1) = i
Sheets(1).Cells(t + 1, 2) = j
Sheets(1).Cells(t + 1, 3) = k
Sheets(1).Cells(t + 1, 4) = l
Sheets(1).Cells(t + 1, 5) = m
Sheets(1).Cells(t + 1, 6) = n
End If
Next
Next
Next
Next
Next
MsgBox "10元换为零钱共有" & t & "种方法！"
End Sub
（8）以上程序中内循环的执行数数如下：
51×21×11×6×3=212058次
可以看出减少最外层循环的101次，可使用内循环体提高100倍的执行效率。
本例程序还有很多优化方法，这里就不再介绍。
3.4 使用数组
在程序中，如果要处理大量的数据，为每个数据定义一个变量将使程序变得很难阅读，并且代码很烦琐。
对于大量有序的数据，可以使用数组对其进行存储和处理。在其他程序设计语言中，数组中的所有元素都必须为同样的数据类型，在VBA中，数组中各元素可以是相同的数据类型，也可以是不同的数据类型。
例029 数据排序
1．案例说明
在Excel中可以方便地对单元格区域中的数据进行排序。本例使用VBA程序首先让用户输入10个数据，然后使用冒泡排序法对这10个数进行排序。
2．关键技术
在程序中处理大量数据时，使用数组来保存是比较好的方法。数组使用之前可以使用Dim、Static、Private或Public语句来声明。在VBA中，数组最大可以达到60维，最常用的是一维数组和二维数组。
定义一维数组的语法格式如下：
Dim 数组名([下界 To] 上界) As 数据类型
其中“下界”可以省略，只给出数组的上界（即可以使用的最大下标值），这时默认值为0，即数组的下标从0开始至定义的上界，如：
Dim MyArray(10) As String
定义了一个名为MyArray的数组，共有11个元素，分别为MyArray(0)、MyArray(1)、…、MyArray(10)。
如果希望下标从1开始，可以通过Option Base语句来设置，其语法格式如下：
Option Base 1
使用该语句指定数组下标的默认下界，只能设为0或1。
—
该语句只能出现在用户窗体或模块的声明部分，不能出现在过程中，且必须放在数组定义之前。3．编写代码
（1）新建Excel工作簿，按快捷键“Alt+F11”进入VBE环境。
（2）单击菜单“插入/模块”命令向工程中插入一个模块。
（3）在模块中编写以下代码：
Option Base 1
Sub 数据排序()
Dim i As Integer, j As Integer
Dim k
Dim s(10) As Integer
For i = 1 To 10
s(i) = Application.InputBox("输入第" & i & "个数据：", "输入数组", , , , , , 1)
Next
For i = 1 To 9
For j = i + 1 To 10
If s(i) < s(j) Then
t = s(i)
s(i) = s(j)
s(j) = t
End If
Next
Next
For Each k In s
Debug.Print k
Next
End Sub
在VBA中使用Inputbox函数接受用户输入数据时，返回的值为文本型。以上代码中使用了Application对象的InputBox方法来接受用户输入数据，该方法的语法格式如下：
Application.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)
设置Type参数可指定返回的数据类型，如本例设置其值为2，则返回的值为数值型。
（4）运行上面的宏，弹出如图3-33所示的对话框，提示用户输入数据。循环程序要求用户输入10个数据。
（5）最后在“立即窗口”输出排序的结果，如图3-34所示。
图3-33 输入数据 图3-34 排序结果
例030 彩票幸运号码
1．案例说明
本例结合数组和随机函数的知识，生成指定数量的彩票幸运号码。本例生成的彩票号码每注由7位数构成，首先让用户输入产生的注数，再使用循环语句生成指定注数的号码。
2．关键技术
本例代码中使用了两个关键技术：动态数组和随机函数。
（1）动态数组
本例使用二维数组保存所有的彩票号码，二维数组的定义格式如下：
Dim 数组名(第1维上界, 第2维上界) As 数据类型
或
Dim 数组名(第1维下界 To 第1维上界, 第2维下界 To 第2维上界) As 数据类型
在本例中，因为生成的彩票数量是由用户输入的数据决定的。因此这里使用动态数组。
动态数组是指在程序运行时大小可以改变的数组，定义动态数组一般分两个步骤：首先在用户窗体、模块或过程中使用Dim或Public声明一个没有下标的数组（不能省略括号），然后在过程中用ReDim语句重定义该数组的大小。
ReDim语句在过程级别中使用，用于为动态数组变量重新分配存储空间。其语法格式如下：
ReDim [Preserve] 数组名(下标) [As 数据类型]
可以使用ReDim语句反复地改变数组的元素以及维数的数目，但是不能在将一个数组定义为某种数据类型之后，再使用ReDim将该数组改为其他数据类型，除非是Variant所包含的数组。
在默认情况下，使用ReDim语句重定义数组的维数和大小时，数组中原来保存的值将全部消失，如果使用Preserve关键字，当改变原有数组最后一维的大小时，可以保持数组中原来的数据。
如果使用了Preserve关键字，就只能重新定义数组最后一维的大小，并不能改变维数的数目。
（2）随机函数Rnd
随机函数Rnd可返回小于1但大于或等于0的一个小数。其语法格式如下：
Rnd[(number)]
可选的number参数是Single或任何有效的数值表达式。根据number参数值的不同，Rnd函数生成的随机数也不同：
— number<0，则每次使用相同的number作为随机数种得到的相同结果。
— number>0，则将生成随机序列中的下一个随机数。
— number=0，则将生成最近生成的数。
— 省略number，则生成序列中的下一个随机数。
—
在调用Rnd之前，先使用无参数的Randomize语句初始化随机数生成器，该生成器具有根据系统计时器得到的种子。为了生成某个范围内的随机整数，可使用以下公式：
Int((上限 – 下限 + 1) * Rnd + 下限)
3．编写代码
（1）新建Excel工作簿，按快捷键“Alt+F11”进入VBE环境。
（2）单击菜单“插入/模块”命令向工程中插入一个模块。
（3）在模块中编写以下代码：
Option Base 1
Sub 幸运号码()
Dim n As Integer, i As Integer, j As Integer
Dim l() As Integer
n = Application.InputBox("请输入需要产生幸运号码的数量：", "幸运号码", , , , , , 2)
ReDim l(n, 7) As Integer
For i = 1 To n
For j = 1 To 7
Randomize
l(i, j) = Int(10 * Rnd)
Next
Next
For i = 1 To n
For j = 1 To 7
Debug.Print l(i, j);
Next
Debug.Print
Next
End Sub
（4）运行上面的宏，弹出如图3-35所示的对话框，提示用户输入数据。输入生成幸运号码的数量。
（5）单击“确定”按钮后在“立即窗口”输出生成的幸运号码，如图3-36所示。
图3-35 输入数据 图3-36 生成幸运号码
例031 用数组填充单元格区域
1．案例说明
在Excel中要处理大量数据时，可使用循环从各单元格读入数据，经过加工处理后再写回单元格区域中。这种方式比在数组中处理数据的速度要慢。因此，如果有大量的数据需要处理时，可先将数据保存到数组中，经过加工处理后，再将数组的数据填充到单元格区域。
本例演示将二维数组中的数据填充到工作表中的方法。
2．关键技术
在Excel工作表中，工作表是一个二维结构，由行和列组成。这种特性与二维数组类似，因此可以很方便地将工作表单元格区域与二维数组之间进行转换。通过以下语句可将单元格区域赋值给一个二维数组：
myarr = Range(Cells(1, 1), Cells(5, 5))
反过来，也可将二维数组中的值快速的赋值给一个单元格区域，如以下语句将二维数组myarr中的值赋值给单元格区域Rng：
Rng.Value = arr
3．编写代码
（1）新建Excel工作簿，按快捷键“Alt+F11”进入VBE环境。
（2）单击菜单“插入/模块”命令向工程中插入一个模块。
（3）在模块中编写以下代码：
Option Base 1
Sub 数组填充单元格区域()
Dim i As Long, j As Long
Dim col As Long, row As Long
Dim arr() As Long
row = Application.InputBox(prompt:="输入行数：", Type:=2)
col = Application.InputBox(prompt:="输入列数：", Type:=2)
ReDim arr(row, col)
For i = 1 To row
For j = 1 To col
arr(i, j) = (i - 1) * col + j
Next
Next
Set Rng = Sheets(1).Range(Cells(1, 1), Cells(row, col))
Rng.Value = arr
End Sub
（4）返回Excel操作环境，向工作表中添加一个按钮，设置提示文字为“填充数据”，指定该按钮的宏为“数组填充单元格区域”。
（5）单击“填充数据”按钮，弹出如图3-37所示对话框，分别输入数组的行和列。
图3-37 输入行和列
（6）VBA代码生成一个二维数组，最后填充到工作表中，如图3-38所示。
图3-38 填充数据
通过Excel相关对象可对工作表中的数据进行操作，如处理单元格区域的公式、对数据进行查询、排序、筛选等操作。本章演示使用VBA进行处理数据的实例。
12.1 处理公式
使用VBA代码可对工作表中的公式单元格进行处理，如判断单元格是否包含公式、复制公式、将单元格公式转换为具体的值等。
例254 判断单元格是否包含公式
1．案例说明
打开本例工作簿如图12-1所示，单击左上角的“公式单元格”按钮，将弹出如图12-1右图所示的提示框，显示当前工作表中定义了公式的单元格。
图12-1 显示有公式的单元格
2．关键技术
本例使用Range对象的HasFormula属性来判断指定单元格是否包含公式，如果区域中所有单元格均包含公式，则该属性值为True；如果所有单元格均不包含公式，则该属性值为False；其他情况下为null。
本例对当前单元格区域中的单元格逐个进行判断，并显示出具有公式的单元格。
3．编写代码
“公式单元格”按钮的VBA代码如下：
Sub 显示公式单元格()
Dim rng As Range
Set rng = ActiveSheet.Range("A1").CurrentRegion
For Each c In rng.Cells
If c.HasFormula Then
MsgBox "单元格" & c.Address & " 定义了公式！"
End If
Next
End Sub
例255 自动填充公式
1．案例说明
打开本例工作簿如图12-2所示，在如图所示工作表中，单元格J3和D16定义了公式，单击“填充公式”按钮，单元格J3的公式将向下填充，单元格D16的公式向右填充，结果如图12-3所示。
图12-2 原工作表
图12-3 复制公式
2．关键技术
本例使用Range对象的AutoFill方法，对指定区域中的单元格执行自动填充。该方法的语法格式如下：
表达式.AutoFill(Destination, Type)
该方法有两个参数，其含义如下：
— Destination：要填充的单元格。目标区域必须包括源区域。
— Type：指定填充类型。该填充类型可使用xlAutoFillType枚举类型，其值如表12-1所示。
表12-1 xlAutoFillType枚举值
名 称
值
描 述
xlFillCopy
1
将源区域的值和格式复制到目标区域，如有必要可重复执行
xlFillDays
5
将星期中每天的名称从源区域扩展到目标区域中。格式从源区域复制到目标区域，如有必要可重复执行
xlFillDefault
0
Excel确定用于填充目标区域的值和格式
xlFillFormats
3
只将源区域的格式复制到目标区域，如有必要可重复执行
xlFillMonths
7
将月名称从源区域扩展到目标区域中。格式从源区域复制到目标区域，如有必要可重复执行
xlFillSeries
2
将源区域中的值扩展到目标区域中，形式为系列（如，“1, 2”扩展为“3, 4, 5”）。格式从源区域复制到目标区域，如有必要可重复执行
xlFillValues
4
只将源区域的值复制到目标区域，如有必要可重复执行
xlFillWeekdays
6
将工作周每天的名称从源区域扩展到目标区域中。格式从源区域复制到目标区域，如有必要可重复执行
xlFillYears
8
将年从源区域扩展到目标区域中。格式从源区域复制到目标区域，如有必要可重复执行
xlGrowthTrend
10
将数值从源区域扩展到目标区域中，假定源区域的数字之间是乘法关系（如，“1, 2,”扩展为“4, 8, 16”，假定每个数字都是前一个数字乘以某个值的结果）。格式从源区域复制到目标区域，如有必要可重复执行
xlLinearTrend
9
将数值从源区域扩展到目标区域中，假定数字之间是加法关系（如，“1, 2,”扩展为“3, 4, 5”，假定每个数字都是前一个数字加上某个值的结果）。格式从源区域复制到目标区域，如有必要可重复执行
3．编写代码
“填充公式”按钮的VBA代码如下：
Sub 填充公式()
Dim i As Long, j As Long
With Range("A1").CurrentRegion
i = .Rows.Count - 1
j = .Columns.Count - 1
End With
Range("J3").AutoFill _
Destination:=Range(Cells(3, 10), Cells(i, 10))
Range("D16").AutoFill _
Destination:=Range(Cells(16, 4), Cells(16, j))
End Sub
以上代码首先获取当前区域的行和列，接着使用AutoFill方法在垂直方向和水平方向填充相应的公式。
例256 锁定和隐藏公式
1．案例说明
打开本例工作簿如图12-4所示，单击“锁定隐藏公式”按钮，当前工作表中的所有公式单元格将被锁定，不允许用户修改，而其他单元格的数据用户可进行修改。同时，公式单元格定义的公式将被隐藏，单击选取具有公式的单元格时，将不会显示公式。
图12-4 锁定和隐藏公式
2．关键技术
要锁定和隐藏单元格，可通过Range对象的以下两个属性来进行设置。
— Locked属性：指明对象是否已被锁定。
— FormulaHidden属性：指明在工作表处于保护状态时是否隐藏公式。
当设置以上两个属性为True时，对指定区域锁定和隐藏。但要真正锁定和隐藏单元格，必须使用Protect方法对工作表进行保护。
3．编写代码
“锁定隐藏公式”按钮的VBA代码如下：
Sub 锁定和隐藏公式()
If ActiveSheet.ProtectContents = True Then
MsgBox "工作表已保护！"
Exit Sub
End If
Worksheets("Sheet1").Range("A1").CurrentRegion.Select
Selection.Locked = False
Selection.FormulaHidden = False
Selection.SpecialCells(xlCellTypeFormulas).Select
Selection.Locked = True
Selection.FormulaHidden = True
Worksheets("Sheet1").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Worksheets("Sheet1").EnableSelection = xlNoRestrictions
End Sub
例257 将单元格公式转换为数值
1．案例说明
打开本例工作簿如图12-5所示，在当前工作表中单元格区域“J3:J15”和“D16:I15”中都定义了公式，单击选择这两个区域中的任意一个单元格，编辑栏中将显示该单元格的公式。
单击工作表左上角的“公式转为数值”按钮，当前工作表中所有公式单元格的公式定义都将被具体计算值所替代，这时再修改引用单元格的值，这两个区域的值不会再变化了。
图12-5 将公式转为数值
2．关键技术
将单元格公式转换为计算结果的表示方法很简单，只需通过以下的赋值运算即可：
rng.Value = rng.Value
以上赋值语句中，rng表示Range对象，该语句首先通过右侧的表达式rng.Value获取指定单元格的值（如果是公式，则获取公式的计算结果），再将该值赋值给单元格的Value变量，从而取代单元格原有的内容（公式）。
3．编写代码
“公式转为数值”按钮的VBA代码如下：
Sub 公式转为数值()
Dim rng As Range, c As Range
Set rng = ActiveSheet.Range("A1").CurrentRegion
For Each c In rng.Cells
If c.HasFormula Then
c.Value = c.Value
End If
Next
End Sub
以上代码首先获取工作表的当前区域，再逐个单元格判断，如果单元格有公式，则进行转换。
例258 删除所有公式
1．案例说明
在Excel中，当单元格的数据发生改变后，引用该单元格的公式单元格的值也会随之变化。有时希望经过计算后，具有公式的单元格的值不再随着引用单元格而变化。这时可以删除工作表中的公式，取消与引用单元格的关联。
打开本例工作簿如图12-6所示，在如图所示的工作表中部分单元格具有公式，单击选择单元格I16，在编辑栏中可看到具体的公式。
图12-6 具有公式的工作表
单击“删除所有公式”按钮，将打开如图12-7所示的对话框，询问用户是否删除提示工作簿中的所有公式，单击“是”按钮工作簿中各工作表中的公式都将被删除，如图12-8所示选中单元格I16，编辑栏中可以看到显示的是具体的值，公式已被删除。
图12-7 确认操作
图12-8 删除公式的工作表
2．关键技术
本例代码与上例类似，不同的是本例将对所有打开工作簿进行处理，对每个工作簿的每张工作表进行循环，将具有公式的单元格转换为具体的数值。
3．编写代码
“删除所有公式”按钮的VBA代码如下：
Sub 删除所有公式()
Dim wb1 As Workbook, ws1 As Worksheet
Dim rng As Range, rng1 As Range
For Each wb1 In Workbooks
With wb1
If MsgBox("是否删除工作簿“" & wb1.Name & "”中的所有公式？", _
vbQuestion + vbYesNo) = vbYes Then
For Each ws1 In .Worksheets
On Error Resume Next
Set rng1 = ws1.UsedRange.SpecialCells(xlCellTypeFormulas)
'获取公式单元格区域引用
For Each rng In rng1
rng.Value = rng.Value '将公式转换成数值
Next
Next
End If
End With
Next
End Sub
例259 用VBA表示数组公式
1．案例说明
打开本例工作簿如图12-9所示。在Excel中，可以通过定义数组公式计算销售总金额。但是如果销售日报表中销售商品的数量不确定（占用表格的行是动态的），使用固定的数组公式就不太方便。
图12-9 销售日报表
本例使用VBA动态定义数组公式，在图12-9所示工作表中输入数据，然后单击“汇总金额”按钮，在单元格F5中将根据录入数据的行数自动生成数组公式，如图12-10所示，在编辑栏可看到数组公式为：
{=SUM(B4:B9*C4:C9)}
2．关键技术
使用Range对象的FormulaArray属性，可获取或设置区域的数组公式。如果指定区域不包含数组公式，则该属性返回null。
图12-10 生成数组公式
3．编写代码
“汇总金额”按钮的VBA代码如下：
Sub 汇总金额()
Dim r As Long
r = ActiveSheet.Range("A3").End(xlDown).Row
Range("F5").FormulaArray = "=SUM(B4:B" & r & "*C4:C" & r & ")"
End Sub
12.2 数据查询
在Excel中，数据查询是最常用的操作。在“开始”选项卡的“编辑”组中单击“查找和选择”按钮，从下拉的菜单按钮中选择相应的命令即可进行查询操作。在VBE中，可使用Find方法进行查询相关的操作，本节实例演示查询数据的VBA代码。
例260 查找指定的值
1．案例说明
打开本例工作簿如图12-11所示，单击左上角的“查找”按钮，弹出“查找”对话框如图12-12所示，在该对话框中输入要查找的值（如本例中输入200），单击“确定”按钮，查找的结果显示在如图12-13所示的对话框中，同时工作表中对应单元格也加亮显示，如图12-14所示。
图12-11 查找工作表 图12-12 输入查找值
图12-13 查找结果 图12-14 加亮显示
2．关键技术
本例的查找使用了Range对象的两个方法：Find方法和FindNext方法。
（1）Find方法
使用该方法可以在区域中查找特定信息。其语法格式如下：
表达式.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
该方法的参数很多，其中What参数是必须指定的，其余参数都可省略。各参数的含义如下：
— What：要搜索的数据。可为字符串或任意Excel数据类型。
— After：表示搜索过程将从其之后开始进行的单元格。此单元格对应于从用户界面搜索时的活动单元格的位置。After必须是区域中的单个单元格。要记住搜索是从该单元格之后开始的；直到此方法绕回到此单元格时，才对其进行搜索。如果不指定该参数，搜索将从区域的左上角的单元格之后开始。
— LookIn：信息类型。
— LookAt：设置匹配文本的方式。可为常量xlWhole（匹配全部搜索文本）或xlPart（匹配任一部分搜索文本）。
— SearchOrder：指定搜索区域的次序。可为常量xlByRows（按行）或xlByColumns（按列）搜索。
— SearchDirection：搜索的方向。可为常量xlNext（在区域中搜索下一匹配值）或xlPrevious（在区域中搜索上一匹配值）。
— MatchCase ：如果为True，则搜索区分大小写。默认值为False。
— MatchByte：只在已经选择或安装了双字节语言支持时适用。如果为True，则双字节字符只与双字节字符匹配。如果为False，则双字节字符可与其对等的单字节字符匹配。
— SearchFormat：搜索的格式。
使用该方法将返回一个Range对象，它代表第一个在其中找到该信息的单元格。如果未发现匹配项，则返回Nothing。Find方法不影响选定区域或当前活动的单元格。
—
每次使用此方法后，参数LookIn、LookAt、SearchOrder和MatchByte的设置都将被保存。如果下次调用此方法时不指定这些参数的值，就使用保存的值。设置这些参数将更改“查找”对话框中的设置，如果省略这些参数，更改“查找”对话框中的— 设置将更改使用的保存值。要避免出现这一问题，每次使用此方法时最好明确设置这些参数。
（2）FindNext方法
FindNext方法继续由Find方法开始的搜索。查找匹配相同条件的下一个单元格，并返回表示该单元格的Range对象。该操作不影响选定内容和活动单元格。其语法格式如下：
表达式.FindNext(After)
参数After指定一个单元格，查找将从该单元格之后开始。此单元格对应于从用户界面搜索时的活动单元格位置。
—
After必须是查找区域中的单个单元格。搜索是从该单元格之后开始的；直到本方法环绕到此单元格时，才检测其内容。如果未指定本参数，查找将从区域的左上角单元格之后开始。当查找到指定查找区域的末尾时，FindNext方法将环绕至区域的开始继续搜索。发生环绕后，为停止查找，可保存第一次找到的单元格地址，然后测试下一个查找到的单元格地址是否与其相同。
3．编写代码
“查找”按钮的VBA代码如下：
Sub 查找指定值()
Dim result As String, str1 As String, str2 As String
Dim c As Range
result = Application.InputBox(prompt:="请输入要查找的值：", Title:="查找", Type:=2)
If result = "False" Or result = "" Then Exit Sub
Application.ScreenUpdating = False
Application.DisplayAlerts = False
With ActiveSheet.Cells
Set c = .Find(result, , , xlWhole, xlByColumns, xlNext, False)
If Not c Is Nothing Then
str1 = c.Address
Do
c.Interior.ColorIndex = 4 '加亮显示
str2 = str2 & c.Address & vbCrLf
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> str1
End If
End With
MsgBox "查找到指定数据在以下单元格中：" & vbCrLf & vbCrLf _
& str2, vbInformation + vbOKOnly, "查找结果"
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
以上代码首先让用户输入查找的值，接着使用Find方法查找第一个满足条件的单元格，再使用循环查找当前工作簿中下一个满足条件的单元格，并在循环中对满足条件的单元格设置不同的底纹，以突出显示。
例261 带格式查找
1．案例说明
打开本例工作簿如图12-15所示，单击左上角的“查找指定格式”按钮，单元格A2将被选中，并填上不同的底色。
图12-15 带格式查找
2．关键技术
本例使用Application对象的FindFormat属性，设置要查找的单元格格式类型的搜索条件，然后使用Find方法按格式进行查找。
3．编写代码
“查找指定格式”按钮的VBA代码如下：
Sub 查找指定格式()
With Application.FindFormat.Font
.Name = "宋体"
.FontStyle = "Bold"
.Size = 11
End With
Cells.Find(what:="", SearchFormat:=True).Activate
Selection.Interior.ColorIndex = 4 '加亮显示
End Sub
以上代码首先使用FindFormat属性设置查找的格式条件，接着使用Find方法按格式查找并激活满足条件的单元格，最后加亮显示激活单元格。
例262 查找上一个/下一个数据
1．案例说明
打开本例工作簿如图12-16所示，单击右上角的“查找”按钮，将弹出输入查找条件对话框，在对话框中输入查找条件单击“确定”按钮，即可在当前工作表中查找满足条件的单元格，找到满足条件的单元格后，选中该单元格。
单击“向前查找”或“向后查找”按钮，可从当前单元格向前或向后查找满足前面设置条件的单元格，并选中该单元格。
如果在使用“查找”按钮输入查找条件之前，就直接单击“向前查找”或“向后查找”按钮，也将弹出如图12-17所示的“查找”对话框输入查询条件。
图12-16 查找上一下/下一个数据 图12-17 输入查找条件
要重设查找条件，单击“查找”按钮打开如图12-17所示对话框即可。
2．关键技术
（1）FindNext方法
使用该方法继续由Find方法开始的搜索。查找匹配相同条件的下一个单元格，并返回表示该单元格的Range对象。该方法的语法格式如下：
表达式.FindNext(After)
参数After指定一个单元格，查找将从该单元格之后开始。此单元格对应于从用户界面搜索时的活动单元格位置。After必须是查找区域中的单个单元格。搜索是从该单元格之后开始的；直到本方法环绕到此单元格时，才检测其内容。如果未指定本参数，查找将从区域的左上角单元格之后开始。
当查找到指定查找区域的末尾时，本方法将环绕至区域的开始继续搜索。发生环绕后，为停止查找，可保存第一次找到的单元格地址，然后测试下一个查找到的单元格地址是否与其相同。
（2）FindPrevious方法
该方法继续由Find方法开始的搜索。查找匹配相同条件的上一个单元格，并返回代表该单元格的Range对象。其语法格式如下：
表达式.FindPrevious(After)
参数After指定一个单元格，查找将从该单元格之前开始。此单元格对应于从用户界面搜索时的活动单元格的位置。
3．编写代码
（1）在VBE中插入一个模块，使用以下代码声明一个模块变量：
Dim c As Range
（2）“查找”按钮的VBA代码如下：
Sub 查找()
result = Application.InputBox(prompt:="请输入要查找的值：", Title:="查找", Type:=2)
If result = "False" Or result = "" Then Exit Sub
Set c = ActiveSheet.Cells.Find(result, , , xlWhole, xlByColumns, xlNext, False)
If Not c Is Nothing Then
c.Activate
End If
End Sub
以上代码首先提示用户输入查询条件，再使用Find方法向下查找。
（3）“向前查找”按钮的VBA代码如下：
Sub 向前查找()
Dim result As String, str1 As String, str2 As String
If c Is Nothing Then
result = Application.InputBox(prompt:="请输入要查找的值：", Title:="查找", Type:=2)
If result = "False" Or result = "" Then Exit Sub
Set c = ActiveSheet.Cells.Find(result, , , xlWhole, xlByColumns, xlPrevious, False)
Else
Set c = ActiveSheet.Cells.FindPrevious(c)
End If
If Not c Is Nothing Then
c.Activate
End If
End Sub
以上代码首先判断模块变量c是否为空（判断执行该子过程之前是否设置了查询条件），若为空，则打开对话框让用户输入查询条件，并使用Find方法向前查找。若模块变量c不为空，则调用FindPrevious方法向前查找。
（4）“向后查找”按钮的VBA代码如下：
Sub 向后查找()
Dim result As String, str1 As String, str2 As String
If c Is Nothing Then
result = Application.InputBox(prompt:="请输入要查找的值：", Title:="查找", Type:=2)
If result = "False" Or result = "" Then Exit Sub
Set c = ActiveSheet.Cells.Find(result, , , xlWhole, xlByColumns, xlNext, False)
Else
Set c = ActiveSheet.Cells.FindNext(c)
End If
If Not c Is Nothing Then
c.Activate
End If
End Sub
例263 代码转换
1．案例说明
打开本例工作簿如图12-18所示，在单元格C3中输入“101”，按回车键或Tab键后，单元格C3中输入的值将转换为“财务部”，如图12-19所示。
图12-18 输入代码
单击工作表的“编码”标签，可看到编码表中编码与名称的对应关系，如图12-20所示。
图12-19 转换代码 图12-20 编码表
2．关键技术
本例使用查表的方法，将工作表中指定列中输入的代码转换为对应的值。在如图12-20所示的“编码”表中输入编码内容。
本例的关键技术是使用工作表事件Change事件来进行代码的转换。
当用户更改工作表中的单元格，或外部链接引起单元格的更改时发生Change事件。该事件的参数Target为数据正在被更改的区域。
3．编写代码
在工作表“Sheet1”的Change事件中编写以下VBA代码：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim t, rng As Range, i As Long, c As Range
If Target.Column = 3 And Target.Row > 2 And Target.Value <> "" Then
t = Target.Value
With Worksheets("编码")
i = .Range("A1").End(xlDown).Row
Set rng = .Range(.Cells(2, 1), .Cells(i, 1))
Set c = rng.Find(what:=t)
If c Is Nothing Then Exit Sub
Target.Value = c.Offset(0, 1).Value
End With
End If
End Sub
以上代码首先对更改单元格的行和列进行判断，如果是第3列第2行以下单元格，则执行编码转换的代码。在转换代码时先获取更改单元格的值，再从“编码”工作表中查找相应的编码，并将查到的编码对应的名称赋值给当前单元格，完成代码的转换。
例264 模糊查询
1．案例说明
打开本例工作簿如图12-21所示，单击“模糊查询”按钮，弹出如图12-22所示的对话框，在对话框中输入查询条件“刘”，单击“确定”按钮，即可在工作表中查找含有“刘”字的单元格，并为单元格填充底色，如图12-23所示。
图12-21 模糊查询 图12-22 查询条件
图12-23 加亮显示查询结果
2．关键技术
本例使用Like运算符进行模糊查询。Like运算符可用来比较两个字符串。其使用方法如下：
result = string Like pattern
Like运算符的语法具有以下几个部分：
— result：运算的结果。
— string：被查询的字符串。
— pattern：查询字符串，该字符串可建立模式匹配。
如果string与pattern匹配，则result为 True；如果不匹配，则result为False。但是如果string或pattern中有一个为Null，则result为Null。
pattern中的字符可使用以下匹配模式：
— ?：可为任何单一字符。
— *：零个或多个字符。
— #：任何一个数字(0–9)。
— [charlist]：charlist中的任何单一字符。
— [!charlist]：不在charlist中的任何单一字符。
在中括号([ ])中，可以用由一个或多个字符(charlist)组成的组与string中的任一字符进行匹配，这个组几乎包括任何一个字符代码以及数字。
例如：
MyCheck = "张三" Like "张*" ' 返回 True
MyCheck = "F" Like "[A-Z]" ' 返回 True
MyCheck = "F" Like "[!A-Z]" ' 返回 False
MyCheck = "a2a" Like "a#a" ' 返回 True
3．编写代码
“模糊查询”按钮的VBA代码如下：
Sub 模糊查询()
Dim result As String, str1 As String
Dim c As Range, rng As Range
result = Application.InputBox(prompt:="请输入要查找的值：", _
Title:="模糊查找", Type:=2)
If result = "False" Or result = "" Then Exit Sub
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set rng = ActiveSheet.Range("A1").CurrentRegion
str1 = "*" & result & "*"
For Each c In rng.Cells
If c.Value Like str1 Then
c.Interior.ColorIndex = 4
End If
Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
以上代码首先让用户输入查询条件，接着使用For循环逐个单元格进行比较，在比较时使用Like进行模糊查询，如果单元格中包含有指定条件的值，则设置单元格的底色。
例265 网上查询快件信息
1．案例说明
使用本例代码可查询申通快递的快件投递情况。打开本例工作簿如图12-24所示，单击“查询快件”按钮打开如图12-25所示对话框，在对话框中输入快件编号，单击“确定”按钮，经过一段时间后得到查询结果如图12-26所示。
图12-24 查询工作表 图12-25 输入快件编号
图12-26 查询结果
—
本例使用的快件编号进行了处理（虚拟编号），在使用本例代码之前应确保计算已接入互联网。2．关键技术
（1）QueryTable对象
QueryTable对象代表一个利用从外部数据源（如SQL Server、Microsoft Access数据库、网络数据等）返回的数据生成的工作表表格。
QueryTable对象是QueryTables集合的成员。
（2）Add方法
使用QueryTables集合对象的Add方法可新建一个查询表。其语法格式如下：
表达式.Add(Connection, Destination, Sql)
该方法参数的含义如下：
— Connection：查询表的数据源。可为连接数据库的连接字符串，也可以是一个Web查询。Web查询字符串的格式如下：
URL;
其中“URL;”是必需的，字符串的其余部分作为Web查询的URL。
— Destination：查询表目标区域（生成的查询表的放置区域）左上角的单元格。目标区域必须位于QueryTables对象所在的工作表中。
— Sql：在ODBC数据源上运行的SQL查询字符串。当使用的数据源为ODBC数据源时，该参数可省略。
（3）Refresh方法
使用QueryTable对象的Refresh方法可更新外部数据区域(QueryTable)。该方法的语法格式如下：
表达式.Refresh(BackgroundQuery)
参数BackgroundQuery如果为True，则在数据库建立连接并提交查询之后，将控制返回给过程。QueryTable在后台进行更新。如果为False，则在所有数据被取回到工作表之后，将控制返回给过程。如果没有指定该参数，则由BackgroundQuery属性的设置决定查询模式。
在Excel建立一个成功的连接之后，将存储完整的连接字符串，这样，以后在同一编辑会话中调用Refresh方法时就不会再显示提示。通过检查Connection属性的值可以获得完整的连接字符串。
如果成功地完成或启动查询，则Refresh方法返回True；如果用户取消连接或参数对话框，该方法返回False。
（4）使用Web查询
在申能快递的网站上可查询快件的投递情况，在浏览器中输入以下网址：
http://www.sto.cn/querybill/webform1.aspx?wen=&Submit2=%B2%E9%D1%AF
将打开如图12-27所示的查询页面，在文本区中输入快件编号，单击“查询”按钮即可在网页上显示指定编号的快件投递情况。
图12-27 通过网页查询快件投递情况
如果要在Excel中通过VBA查询快件投递情况，只需要将前面的URL地址中的“wen=”字符串后面加上快件编号即可。
3．编写代码
“查询快件”按钮的VBA代码如下：
Sub 查询快件()
Dim str As String, strURL As String
str = Application.InputBox(prompt:="请输入快件的编号：", _
Title:="申通快件查询", Type:=2)
If str = "False" Then Exit Sub
strURL = "URL;http://www.sto.cn/querybill/webform1.aspx?wen="
strURL=strURL & str & "&Submit2=%E6%9F%A5%E8%AF%A2"
With ActiveSheet.QueryTables.Add(Connection:=strURL, Destination:=Range("A2"))
.Name = "abc"
.FieldNames = True
.WebSelectionType = xlSpecifiedTables '导入指定表
.WebFormatting = xlWebFormattingNone '不导入任何格式
.WebTables = "1,2" '导入第一个和第二个表格中的数据
.BackgroundQuery = True '查询异步执行（在后台执行）
.Refresh BackgroundQuery:=False '更新数据
End With
End Sub
例266 查询基金信息
1．案例说明
打开本例工作簿，单击“查询基金信息”按钮，将在当前工作表中显示当前基金的信息如图12-28所示。
图12-28 基金信息
2．关键技术
在网站http://tw.stock.yahoo.com/us/worldinx.html中可查询基金的信息，如图12-29所示。
在图12-29所示的基金信息网页中，上面用6个表格显示了一些超链接信息。最下方的表格显示具体各基金的数据，本例通过Web查询只需要获取下方的表格即可。通过查看HTML代码，可知该表格是第7个表格，所以需要设置QueryTable对象的WebTables属性为7。
3．编写代码
“查询基金信息”按钮的VBA代码如下：
Sub 查询基金信息()
Dim strURL As String
strURL = "URL;http://fund.sohu.com/r/cxo.php"
With ActiveSheet.QueryTables.Add(Connection:=strURL, Destination:=Range("A2"))
.Name = "worldinx"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "7"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
图12-29 网站查询基金信息
例267 查询手机所在地
1．案例说明
打开本例工作簿如图12-30所示，单击“手机所在地”按钮打开如图12-31所示对话框，输入手机号码后，单击“确定”按钮即可查询出手机所在地，如图12-32所示。
2．关键技术
本例与前面各例使用的Web查询不同。本例使用http://www.123cha.com/网站来查询手机所在地。其查询的HTML代码如下：
图12-30 查询手机所在地 图12-31 输入手机号码
图12-32 手机所在地
从以上HTML代码可以看出，查询手机所在地使用的是POST方法（另一种方式是GET方式，前面两例使用的这种方式），这种方法将传递一个查询变量到目标页面，需要提供以下两个参数：
— 第一个是查询页面，即QueryTable对象的Connection参数。该参数应该是
完全手册Excel VBA典型实例大全：通过368个例子掌握111
完全手册Excel VBA典型实例大全：通过368个例子掌握1
完全手册Excel VBA典型实例大全：通过368个例子掌握
完全手册Exce手动l VBA典型实例大全：通过368个例子掌握
完全手册Excel VBA典型实例大全：通过368个例子2
完全手册Excel VBA典型实例大全：通过368个例子1
完全手册Excel VBA典型实例大全
完全手册Excel VBA典型实例大全
Excel VBA典型368个例子
Excel VBA完全手册
Excel中两列比较的一个典型实例
Excel2003 函数完全手册Excel
Excel 2003 函数完全手册
Excel2003 函数完全手册Excel'
Excel 2003 函数完全手册1
Excel 2003 函数完全手册2
Excel 2003函数应用完全手册
Excel 2003函数应用完全手册
Excel 2003函数应用完全手册
excel中如何通过VBA打开word文件和ppt文件？
Excel VBA Examples(2)
EXCEL VBA 基础
EXCEL VBA 基础
Excel VBA Examples(2)