完全手册Excel VBA典型实例大全
来源:百度文库 编辑:神马文学网 时间:2024/05/22 05:36:18
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)决定消息框的强制响应性。
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
(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
该语句的功能为:若逻辑表达式的值是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
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
(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,则生成序列中的下一个随机数。
—
为了生成某个范围内的随机整数,可使用以下公式:
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方法不影响选定区域或当前活动的单元格。
—
— 设置将更改使用的保存值。要避免出现这一问题,每次使用此方法时最好明确设置这些参数。
(2)FindNext方法
FindNext方法继续由Find方法开始的搜索。查找匹配相同条件的下一个单元格,并返回表示该单元格的Range对象。该操作不影响选定内容和活动单元格。其语法格式如下:
表达式.FindNext(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参数。该参数应该是