VBA的主体结构就是过程。VBA包括子过程、函数过程和属性过程三种,本书主要介绍子过程(也称Sub过程)和函数过程(也称Function过程)。
本章要点
◆认识过程
◆Sub过程
◆Function过程
◆关于过程参数
◆开发自定义函数
◆编写函数帮助
6.1认识过程
VBA中每一个程序都包含过程。录制的宏是一个过程,一个自定义函数也是一个过程。掌握好单个过程的编写思路,就可以组合成一个大中型插件或者专业程序。
6.1.1 过程的分类与调用方式
过程主要分为三类:子过程、函数过程和属性过程。这三类过程的格式如下。
Sub 子过程()
……
End Sub
Function 函数过程(rng As Range)
……
End Function
Property Get 属性过程() As Variant
……
End Property
本书主要讲述Sub子过程和Function函数过程的开发。
Sub过程是VBA中应用最广的过程,录制宏所产生的过程就是Sub过程。Sub过程的执行方式包括五种。
1.【Alt+F8】执行
如果在工作表命令窗口、ThisWorkBook命令窗口或者标准模块窗口中存在Sub过程,那么在工作表界面可以通过快捷键【Alt+F8】来执行该过程。
假设在VBE界面中的Sheet1代码窗口中有一个Sub过程“汇总”,在模块1中有一个名
Sub子过程。
2.快捷键执行
Sub过程可以与某个快捷键进行关联,在后续的使用中就可以利用这个快捷键来调用对应的过程。
设置Sub过程的快捷键主要有两种方式:利用宏对话框设置及用VBA代码指定。后者在本书其他章节将会讲述,在此演示一下“宏”对话框设置宏的快捷键的方法:假设VB工程中有两个名为“汇总”和“新建菜单”的Sub过程,在工作表界面中按下快捷键【Alt+F8】调出图6.1所示对话框,然后选择“新建菜单”,并单击“选项”按钮,在弹出的“宏选项”对话框中指定快捷键,如图6.2所示。图中设计宏程序“新建菜单”的快捷键是【Ctrl+q】。
图6.1 “宏”对话框图6.2 设置Sub过程的快捷键
3.按钮执行
在工作表中建一个按钮,并将按钮与Sub过程关联,从而实现单击按钮执行程序。
将按钮关联到Sub过程的步骤为:
1.单击菜单【开发工具】→【表单控件】→【按钮】;
2.在工作表中按下左键并向右下方拖动,从而绘制一个控件按钮;
3.在弹出的对话框中选择“新建菜单”,如图6.3所示;
4.返回工作表后即可单击名为“按钮1”的按钮来执行程序“新建菜单”。
图6.3 关联过程与按钮
4.菜单调用
最常见的是编写一个自定义菜单或者工具条来调用Sub过程。菜单与工具条的设计方法参见本书第19章及第20章。
5.事件引发
对于部分需要自启动的程序,通常利用事件引发,不需要人工干预。例如工作簿开启时就自动执行某程序,或者关掉窗体、鼠标移过窗体时执行某程序……
对于事件过程的运用参见本书第8章。
6.工作表中使用公式调用
Function过程即自定义函数,可以像使用内置的工作表函数一样在公式中使用。
调用Function过程的步骤如下:
1.单击菜单【插入】→【模块】;
2.在模块中录入以下代码:
Function 成绩(rng)
成绩= IIF(rng >= 60, "及格", "不及格")
End Function
3.返回工作表中,在A1输入数值50,在B1输入公式:
=成绩(A1)
可以发现公式可以像内置函数一样运行,它返回“不及格”,正是期望的结果。
6.1.2 插入过程的方式
编写过程时可以手工录入代码,也可以让利用VBA提供的列表自动产生程序外壳。
一个Sub过程分为程序外壳部分和主体部分。如图6.4所示。
图6.4 Sub过程的外壳与主体部分示意图
其中外壳部分可以手工录入,也可以利用VBE提供的方式完成。
1.非事件过程
对于非事件的Sub 过程,VBA提供了一个专用窗体来选择性录入过程的外壳。具体步骤如下:
1.在VBE界面中单击菜单【插入】→【模块】;
2.单击菜单【插入】→【过程】打开“添加过程”对话框;
3.在“名称”框中录入“汇总”,并将“类型”选择“子过程”,将“范围”设为“私有的”,如图6.5所示。然后单击“确定”按钮。
图6.5 添加Sub过程外壳图
执行以上程序后在模块中可以看到产生的代码为:
Private Sub 汇总()
End Sub
如果是Function函数过程,也可以按照上述方法录入过程的外壳。
2.事件类过程
VBA支持很多类事件,大部分事件的代码都需要参数。而这些参数是很难记忆的,包括所有VBA专业程序员。为了快速且准确地录入事件类过程,可以通过VBE提供的对象与过程窗口的下拉列表完成。
例如输入工作表SelectionChange事件的过程,方法如下:
(1)使用快捷键【Alt+F11】进入VBE界面,并用快捷键【Ctrl+g】打开工程资源管理器窗口;
(2)双击Sheet1或者其他需要录入工作表事件的工作表名;
图6.6 从下拉列表选择对象
因为VBA默认状态下就是弹出“Worksheet_SelectionChange”事件的代码,所以当选择对象为“Worksheet”后就产生了需要的代码。如果需要录入“Worksheet_Change”事件的代码,则需要在选择对象“Worksheet”后,再选择过程“Change”,然后将产生的“Worksheet_SelectionChange”事件的代码删除,仅保留以下代码:
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub
对于此类包含参数的事件过程,应该尽量选择通过对象与过程窗口的下拉列表产生代码的方式,手工录入很容易产生误差。
在用户窗体中很多事件也支持参数,而且有多个参数,通常也需要从列表中选择对象与过程的方式来录入代码。例如在窗体中录入鼠标移过事件的过程代码,步骤如下:
1.单击菜单【插入】→【用户窗体】;
2.使用快捷键【Ctrl+g】显示工程资源管理器,并在UserForm1(或者别的名称)上单击右键,选择菜单【查看代码】;
3.从对象窗口选择“UserForm1”,此时默认产生“UserForm_Click”事件的代码;再从过程窗口选择“MouseMove”,代码窗口中将产生以下代码:
Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
End Sub
4.删除“UserForm_Click”事件的代码。
6.1.3 过程的命名规则
过程的命名与变量的命名规则一致。
但需要补充的一点是:过程名可以和本过程的私有变量同名,但却不能和公有变量同名。例如:
Sub 身份证()
Dim 身份证As String
Dim 身份证As String
Sub 身份证()
身份证= [a1].Text
End Sub
这段代码却是非法的,只要运行程序就会弹出编译错误。
为了避免错误及便于识别,需要使本过程的私有变量也尽量保持与过程名不相同。
6.2编写Sub过程
本节开始了解关于Sub过程的基本概念,以及编写简单的Sub过程。
6.2.1 Sub过程的语法解析
Sub过程即利用Sub语句声明的子过程。所有宏录制器产生的过程全是Sub过程,无法通过录制宏产生Function过程或者属性过程。
Sub语句声明过程的语法如下:
Private | Public | Friend] [Static] Sub name [(arglist)]
[statements]
[Exit Sub]
[statements]
End Sub
其中各参数的详细功能如表6-1所示。
表6-1Sub语句参数详解
Sub过程与所有变量一样,也区分公有和私有,而在说法上稍有区别。过程分模块级过程和工程级过程。
(1)声明Sub过程前使用Private;
(2)只有当前模块可以调用,例如在“模块1”中有以下代码:
Private Sub 过程一()
MsgBox 123
End Sub
Private Sub 过程二()
Call 过程一
End Sub
执行过程二时可以调用过程一,但如果过程二存放于“模块2”中,则将弹出“子过程未定义”的错误提示。
(3)不出现在“宏”对话框中,即使用快捷键【Alt+F8】所打开的对话框中无法查看到当前过程的名称列表。如果是Function过程,则无法在函数向导中查看到函数名。
2.工程级过程
工程级过程是指在当前工程中任意地方都可以随意调用的过程。它的特征刚好与模块级过程相反:在“Sub”语句前置标识符“Public”、非当前过程可以调用,可以出现在“宏”对话框中。
如果一个过程没有使用“Public”和“Private”标识,则默认为公有过程,任何模块或者窗体中都可以调用。
Sub过程也支持参数,其参数的用法与Function过程的参数用法一致,本小节不详述,请参阅本书6.4节。
3.Exit sub与End的作用与区别
Sub过程可以在程序执行期间随时退出程序,通常是设定若干个条件。当满足条件时使用“Exit Sub”语句来终止程序。“Exit Sub”语句后面的代码不再执行。
也可以使用“End”语句来退出程序。“End”和“Exit Sub”在使用中有相同处,也有明显的差别。相同处是都可以中途终止运行程序,不同处则有以下两点。
(1)是否释放公有变量
从以下三段代码可以体现“End”和“Exit Sub”的差异:
Dim x As Long
Sub A()
x = 888
Exit Sub
End Sub
Sub B()
x = 888
End
End Sub
Sub C()
量的值在过程中并没有释放,“Exit Sub”仅仅退出程序执行,公有变量的值保持不变。
如果执行过程B再执行过程C,那么X的值则为0,说明在过程B中的“End”语句已经释放变量X的值。
(2)是否终止所有程序
仍然用三个过程来演示“End”和“Exit Sub”的差异:
Sub A()
Call B
MsgBox "终止"
End Sub
Sub B()
Exit Sub
End Sub
Sub C()
End
End Sub
执行程序A的结果是弹出对话框“终止”,而将过程A中的“Call B”修改为“Call C”,那么什么反应也没有。也就是“Exit Sub”是退出它所在的程序,而“End”则中止所有程序,包括调用它的程序。如果在窗体代码中,“Exit Sub”仅仅退出事件,而“End”则退出事件后关掉窗体,窗体中声明的所有变量全部释放。
6.2.2 Sub过程的执行流程
如果录制宏并执行宏,可以看出宏代码的执行流程永远是从上到下。可以使用调试功能来查看流程。例如执行以下代码:
Sub 设置A1单元格()
Range("A1").Select
Range("A1") = "中华人民共和国"
Range("A1").Interior.Color = 65535
Range("A1").Font.ColorIndex = 3
Range("A1").Borders.LineStyle = xlContinuous
Range("A1")https://www.doczj.com/doc/3114584550.html, = "黑体"
Range("A1").Font.Size = 20
Range("A1").EntireColumn.AutoFit
End Sub
将VBE窗口缩小,使自己能同时看到代码及A1单元格的情况下再按下快捷键【F8】,从而进入逐句调试阶段。
当按下调试键【F8】时,当前执行的语句呈黄色显示,再次按下【F8】键时,则下一句呈黄色显示,而操作对象A1则对应产生变化。图6.7中已执行到第四句,所以A2单元格同步后的状态就是已录入“中华人民共和国”,且并设置了黄色背景色。
图6.7 逐步执行代码
当继续通过【F8】键执行完成的代码后,可以得出结论:所有录制的宏和未特别指定程序跳转的VBA代码总是按照从上至下的流程逐句执行。
那么是否有例外呢?通常在以下三种情况会有例外。
1.使用冒号实现一行执行多句代码
VBA中允许借助冒号将多句代码写在同一行执行。对同行中的代码按从左向右的顺序执行。例如:
Sub 设置A1单元格()
Range("A1") = "中华人民共和国": Range("A1").Interior.Color = 65535
Range("A1").Font.ColorIndex = 3: Range("A1").Font.Size = 20
Range("A1").EntireColumn.AutoFit
End Sub
以上代码在借助冒号将前四行代码缩至两行,但执行过程仍然为四步。对于同行中有多句代码时,按从左向右的顺序执行。
那么读者一定可以想到,使用冒号和不使用冒号的执行结果岂不是完全一致?仅仅改变了行数?
答案是“有时一致,有时不一致”。如果以上的代码按如下方式编写,那么通过冒号改变行数后执行结果完全一致:
Sub 设置A1单元格()
Range("A1") = "中华人民共和国"
Range("A1").Interior.Color = 65535
Range("A1").Font.ColorIndex = 3
Range("A1").Font.Size = 20
Range("A1").EntireColumn.AutoFit
假设工作表中有图6.8所示数据,执行过程“判断是否及格1”时,C3单元格将出现“及格”;而执行过程“判断是否及格2”时则无任何反应。也就是说“Exit Sub”语句与IF同行时,只有单元格B2的值大于或等于60,“Exit Sub”语句才会执行。在本例中不符合条件,那么没有退出程序,可以继续执行其后的代码。而“Exit Sub”语句单独占据一行时,不管单元格B2是否符合条件,“Exit Sub”都会执行,从而退出程序,不再对B3的值进行判断。
图6.8 数据
2.使用标签改变执行流程
VBA可以在代码中设置一个或者多个标签,然后让程序在满足某条件时跳转到标签处,从而改变过程执行流程。
标签的规则是:
◆可以是标点符号以外的字符组合
◆以冒号(:)结尾
◆与大小写无关
◆必须位于一行的最左端
◆配合GoTo使用
例如,建立一个名为“总表”的工作表,代码如下:
Sub 新建总表()
For i = 1 To Sheets.Count
IF Sheets(i).Name = "总表" Then GoTo err
Next i
Sheets.Add
https://www.doczj.com/doc/3114584550.html, = "总表"
End
err:
MsgBox "已经存在总表"
End Sub
以上代码首先利用For循环逐一检查工作表的名字,如果某个工作表的名字等于“总表”
(1)标签名后面必须带有冒号。
(2)在标签之前根据需要,及时退出程序。
在本例中,按照设计意图,只要工作簿中存在“总表”则执行标签“Err”之后的语句,反之不执行。所以标签之前必须加入“End”或者“Exit Sub”来退出程序,否则任何情况下Err后的语句都会被执行。
在一个过程中还可以定义多个标签。例如:
Sub 新建总表()
MsgBox ActiveWorkbook.ProtectWindows
IF ActiveWorkbook.ProtectWindows = true Then GoTo 已加密
For i = 1 To Sheets.Count
IF Sheets(i).Name = "总表" Then GoTo 已存在
Next i
Sheets.Add
https://www.doczj.com/doc/3114584550.html, = "总表"
End
已存在:
MsgBox "已经存在总表"
End
已加密:
MsgBox "当前工作簿窗口已锁定,无法建立新表"
End Sub
在此过程中,首先判断当前工作表的窗口是否锁定,如果锁定则执行“已加密”标签后的语句;然后再检查是否存在“总表”,当有“总表”时执行“已存在”标签后的语句。本例中两个标签没有顺序上的差异,谁前谁后不影响代码的结果。
3.Sub过程的嵌套调用方式
过程与过程之间是可以相互调用的,从而使代码的执行流程改变。通过VBA代码调用Sub 子过程主要有两种方式。
Call语句
Call语句的功能是将一个过程的控制权转移到另一个过程。
它的语法为:[Call] name [argumentlist],即Call 过程名参数。
其中Call是可选的,即在其他过程调用过程一时可以有以下两种形式:
Sub 过程一()
MsgBox "你好!"
End Sub
Private Sub 过程二()
过程一
End Sub
Private Sub 过程三()
Call 过程一
Run方法可以运行一个宏或者调用一个函数。该方法可用于运行一个用Visual Basic 或Excel 宏语言编写的宏或者运行DLL或XLL中的函数。实例如下:
Sub 过程四()
Application.Run "过程一"
End Sub
其中“Application.Run”也可以简写为“Run”。
6.2.3 过程的递归
所有过程都是可以递归的,即可以调用自己来完成任务。
实际工作中需要调用过程本身的实例极少,通常进入递归都是编码有问题而误入递归状态,结果耗尽系统资源。
在某些情况下也可以故意调用自己来完成任务。例如下面2例。
1.按条件新建工作表
Sub 建立10个表()
IF Sheets.Count >= 10 Then Exit Sub
Sheets.Add , Sheets(Sheets.Count), 1
Call 建立10个表
End Sub
以上代码中,首先利用IF查找并检测当前工作簿的工作表数量,如果大于或等于10则退出程序,否则在最后位置新建一个工作表,最后再调用自身继续执行,直到满足条件“大于或等于10”为止。
因代码中人为设置了退出递归的条件,所以这类递归不会造成程序崩溃,资源耗尽。如果将代码中的“IF Sheets.Count >= 10 Then Exit Sub”删除,那么程序循环执行的结果就是电脑死机,除非中途人工中断程序执行:使用快捷键【Ctrl+Break】。
2.设计时钟
Sub 时间()
[a1] = WorksheetFunction.Text(Now(), "hh:mm:ss")
Application.OnTime Now() + TimeValue("00:00:01"), "时间"
End Sub
Sub 终止()
Application.OnTime Now() + TimeValue("00:00:01"), "时间", , false
End Sub
以上代码实现的效果是在单元格显示当前时间,包括时、分、秒,且每秒钟更新一次。通过递归方式让程序每秒钟执行一次实现时钟的效果,同时再利用另一个过程随时退出递归。当然也可以用快捷键【Ctrl+Break】。
为了更好地理解Sub过程,通过两个实例来展示。
1.统计选区信息:不带参数的Sub过程
要求:对任意选区统计单元格个数、数值个数、非空单元格个数、空白单元格个数及选区之和。
代码如下:
Sub 选区统计()
Dim msg As String
msg = "单元格个数:" & Selection.Count & Chr(10)
msg = msg & "数字个数:" & WorksheetFunction.Count(Selection) & Chr(10)
msg = msg & "非空单元格:" & WorksheetFunction.CountA(Selection) & Chr(10)
msg = msg & "空白单元格个数:" & WorksheetFunction.CountBlank(Selection) & Chr(10)
msg = msg & "选区之和:" & WorksheetFunction.Sum(Selection)
MsgBox msg, 64, "选区统计"
End Sub
假设工作表中存在图6.9所示数据,选择A1:D9区域后利用快捷键【Alt+F8】执行“选区统计”过程,其统计结果如图6.10所示。
图6.9工作表数据图6.10选区统计结果
2.将单元格数据转换为首字母大写:带有参数的Sub过程
要求:在工作表中选择任意一个带英文的单元格时,将其转换为每个单词首字母大写。
(1)插入模块1,并录入以下代码:
Sub 转换(Target)
Selection(1) = StrConv(Target, vbProperCase)
End Sub
(2)双击工程资源管理器中的“Sheet1”,进入工作表代码窗口后录入代码:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call 转换(Target(1))
End Sub
(3)返回工作表“Sheet1”,单击任意单元格,如果存在英文单词,则每个单词首字母大写,否则保持不变。如单元格中有句子“You are on it”,那么单击该单元格后将被转换为“You Are On It”。
Function过程即自定义函数,在插件中应用极广。本节介绍关于Function过程的语法及调用方法。
6.3.1 Function过程的特点
Function过程的功能较Sub过程的应用范围稍小,Function过程仅仅用于返回一个值或者多个数的组合,即数组,而Sub过程既可以返回值,还可以对引用的对象进行修改。例如,引用单元格A1的值后对单元格A1设置新的格式,或者修改工作表名称等。Function可以获取工作表名称,但无法修改工作表的名称。
Function过程可以不使用参数,类似于工作表函数Rand和Now等,但绝大部分函数是需要一个参数或者多个参数的,最多时可达255个参数。
6.3.2 Function的语法解析
Function的语法如下:
[Public | Private | Friend] [Static] Function name [(arglist)] [As type]
[statements]
[name = expression]
[Exit Function]
[statements]
[name = expression]
……
End Function
Function语句的各参数详解如表6-2所示。
表6-2Function语句参数详解
和Sub过程一样,Function过程也有模块级过程和工程级过程之分。Function前置“Public”即为工程级,前置“Private”则为模块级。
Function名称在声明时需要遵循与Sub过程一样的规则。
如果自定义的Function名称与VBA内部名称一致,仍然可以正常执行,只是在代码中调用Excel内部函数时必须声明其对象库。例如:
Function sqr(AA)
sqr = AA ^ (1 / 3)
End Function
Sub test()
MsgBox "VBA.SQR:" & VBA.sqr(27) & Chr(10) & "SQR:" & sqr(27)
End Sub
执行test过程时的结果如图6.11所示。
图6.11自定义SQR和内置SQR的分别
从结果可以得知,在代码使用“VBA.SQR”可以调用VBA自带的SQR功能,而直接使用SQR则调用自定义的SQR函数的功能。
虽然定义函数时允许与内部函数一致,但却不允许与定义的变量或者常量一致,不管这个变量或者常量是本过程私有的还是模块中公有的,否则将产生“发现二义性的名称”的编译错误。
6.3.3 调用Function过程
Function过程通常以三种方式调用。
(1)在工作表中通过公式调用:像内部函数一样在工作表中使用,也可以与其他函数嵌套。
(2)在VBA代码中被其他过程调用:就像图6.9对应的那段代码一样在Sub过程调用函数。
(3)递归:Function过程和Sub一样可以实现递归。如果不是刻意地、有计划地进入递归状态,可能会造成资源耗尽或者溢出堆栈空间。例如下面函数的调用:
Function 递归(参数)
递归= 递归(参数)
End Function
将代码录入到模块中后,执行过程“测试”,立即弹出错误提示“溢出堆栈空间”。
为了避免递归造成的错误,甚至程序崩溃,尽量不要调用自身,开发函数、插件时多方面查核是否可能造成循环引用、递归现象。当然,有目的、有条件的递归是可以给工作带来便利的。
另外,谈到函数就不能不说它的“刷新”性能,即在工作表中使用函数时,当在其他区域的数据更新时,当前单元格的函数是否重新运算,专业术语称之为“易失性”。
用户定义的函数是否有易失性可以使用以下语句来控制:
Application.Volatile
该语句的作用是无论何时在工作表的任意单元格中进行计算,函数都必须重新进行计算。即工作表刷新时调用函数再运算一次,从而实现数据更新,使公式结果同步。
6.4关于过程的参数
Sub过程和Function过程都可以使用参数。有参数的过程相对于无参数的过程更具灵活性,相当于给了用户更多自定义的空间。
6.4.1 Sub过程的参数及应用
Sub过程的语法是:
[Private | Public | Friend] [Static] Sub name [(arglist)]
[statements]
[Exit Sub]
[statements]
……
End Sub
其中“(arglist)”即表示它支持可选的参数,可以不用参数,也可以使用参数;可以使用一个参数,也可以使用多个参数。
其中参数(arglist)的具体语法如下:
[Optional] [ByVal | ByRef] [ParamArray] varname[( )] [As type] [= defaultvalue]
表6-3 Sub过程参数详解
从表中可以看出,如果需要给Sub过程设置一个可选参数,则可以使用关键字Optional 来声明,如果需要设置多个可选参数,则可以使用关键字ParamArray来声明参数。
下例即为使用一个参数的Sub过程:
Sub 过程一(msg As String)
IF Len(msg) <> 0 Then MsgBox msg, 64, "友情提示"
End Sub
Private Sub 过程二()
Call 过程一("你好")
End Sub
如果执行过程二,将弹出图6.12所示对话框。
图6.12 提示信息
可能看到以上代码时有读者会有疑问,直接在过程二中执行Msgbox不是更简单吗?例如改成以下代码:
Private Sub 过程二()
MsgBox "你好", 64, "友情提示"""
End Sub
在本例中确实二合一后更简单,但当有很多过程需要执行类似操作时,则对一个过程进行判断比每个过程都判断一次更简单。例如:
Sub 姓名(name As String)
Dim i As Byte, rng As Range
For i = 1 To Sheets.Count
IF ThisWorkbook.Sheets(i).name = "许可人员列表" Then: GoTo OK
Next i
MsgBox "不存在“许可人员列表”", 64
Exit Sub
OK:
IF Len(name) < 2 Or Len(name) > 4 Then MsgBox "长度只能2到4,请重新录入", 64: Exit Sub
Set rng = ThisWorkbook.Sheets("许可人员列表").Range("a1:a10").Find(name)
IF rng Is Nothing Then MsgBox "你无操作权限" Else MsgBox "你具有操作权限"
End Sub
以上代码用于判断指定的用户名是否具有操作权限。在工作簿中有一个工作表名为“许可人员列表”,该表中A1:A10存放10个允许操作的人员名单。程序会将用户输入或者指定方式获取的姓名与表A1:A10中允许的姓名进行比较,如果与任何一个一致则提示“你具有操作权限”,否则提示“你无操作权限”。
在过程“确认权限一”、“确认权限二”和“确认权限三”中都可以调用过程“姓名”,只是参数不同。如果不使用过程“姓名(Name)”作过渡的话,那么过程“姓名(Name)”中的所有代码需要在后面三个过程中出现三次,每一个过程都需要对参数进行多次判断及循环,从而使整个工程的代码偏长。
下例再演示具有两个参数但第二个参数是可选参数的Sub过程:
Sub 改名(Sht_Name As String, Optional i As Byte = 1)
Dim j As Byte
For j = 1 To Sheets.Count
IF Sheets(j).name = Sht_Name Then MsgBox "已存在:" & Sht_Name, 64: End Next j
IF i >= 1 And i <= Sheets.Count Then Sheets(i).name = Sht_Name
End Sub
Private Sub 过程二()
Call 改名("总表", 12)
End Sub
Private Sub 过程三()
Call 改名("汇总表")
End Sub
以上过程用于工作表改名,根据指定的工作表新名称与工作表序号对工作表重命名。
在以上代码中,过程“改名”具有两个参数,第一参数用于指定工作表新名称,第二参数用于指定工作表序号。如果忽略第二参数,则当作1处理。
将三段代码复制到模块中,执行“过程二”。因其第一参数为“总表”,第二参数为2,那么执行结果即工作簿中第二个工作表重命名为“总表”。
而执行“过程三”后,因忽略了第二参数,默认当作1处理,所以结果为第一个工作表重命名为“汇总表”。
声明过程的参数时,可以通过Byval和ByRef将参数设置为按值传递或者按地址传递。例如所有工作表事件和工作簿事件的参数都是按值传递。采用Byval声明参数。
Byval表示参数按值传递,这种情况下过程访问的是变量的复本,过程不可改变变量的真正值。
ByRef表示按地址传递,过程访问的是变量本身,过程可以改变变量的真正值。默认情况下,都按值传递参数。
通过以下案例可以看出两者的分别:
Sub test(ByVal 变量1 As String, ByRef 变量2 As String) '声明两个变量,一为按值传递,一为按址传递
变量1 = "1" '不能改变主程序传过来的实参
变量2 = "A" '能够改变主程序传过来的实参
End Sub
Sub 比较()
Dim 值As String, 址As String '声明两个变量
值= "2": 址= "B" '对两个变量赋值
MsgBox "调用前:" & Chr(10) & "变理“值”为:" & 值& Chr(10) & "变理“址”为:" & 址'检查赋值后的变量值
test 值, 址'调用带参数的SUB过程
'检查过程test对两个变量的值的影响,其中变量“值”未曾变,过程test仅仅改变了它的副本的值,变量本身的值不变
MsgBox "调用后:" & Chr(10) & "变理“值”为:" & 值& Chr(10) & "变理“址”为:" & 址
End Sub
在“test”过程中,分别有一个按值传递和一个按址传递的参数,并在过程中对两个参数赋值。在过程“比较”中,将两个变量作为test过程的参数,结果是只能改变按值传递的参数,按值传递的参数总保持不变。
6.4.3 Function过程的参数
Function过程的参数与Sub过程的参数在语法上完全一致,可以使用相同的参数。
但是Function过程只能返回引用对象的某个属性值或者运算结果,无法改变对象的属性、格式等,所以部分带有参数的Sub过程可以直接改用Function实现,而部分却无法实现。
Function和Sub一样可以使用一个或者多个参数,也可以使用可选参数。但不同的是Function过程只能返回值,所以在声明Function过程时,其所有参数可以指定数据类型,Function过程本身也可以指定数据类型。例如:
Function Str(rng as range) as string
Function与Sub过程的另一个区别是Sub过程的参数允许与Sub过程名一致,而Function 的参数绝不能与Function过程名一致。例如:
Sub 成绩(成绩)
执行“Test”过程可以正确判断成绩59分是否及格。但若改用Function过程则一定出错:Function 成绩(成绩)
IF 成绩>= 60 Then 成绩= "及格" Else 成绩= "不及格"
End Function
Sub Test()
MsgBox 成绩(59)
End Sub
执行过程“Test”后将弹出“当前范围内的声明重复”的编译错误。即使再修改为以下方式仍然报错:
Function 成绩(成绩)
IF 成绩>= 60 Then MsgBox "及格" Else MsgBox "不及格"
End Function
Sub Test()
Call 成绩(59)
End Sub
正确的方式是:
Function 成绩(分数)
IF 分数>= 60 Then 成绩= "及格" Else 成绩= "不及格"
End Function
Sub Test()
MsgBox 成绩(59)
End Sub
6.5 开发自定义函数
6.4节对自定义函数的基础知识做了详解,本节则进行实例演示,通过带有不同参数的函数定义过程来增进读者的理解与编写功底。
对于本节中开发函数中所涉及的各种语法,如条件语句、循环语句、With语句以及常见对象的命令请参阅本书第10章和第11章。也可以阅读完第10章及第11章后再返回本节练习开发函数。
6.5.1 开发不带参数的Function过程
1.获取本机IP地址
【要求】:利用函数取得本机IP地址。