1、Excel VBA 程序开发自学 （第 2 版）第 06 章 编写 SUB 过程及开发函数VBA 的主体结构就是过程。VBA 包括子过程、函数过程和属性过程三种，本书主要介绍子过程（也称 Sub 过程）和函数过程（也称 Function 过程） 。本章要点 认识过程 Sub 过程 Function 过程 关于过程参数 开发自定义函数 编写函数帮助6.1 认识过程VBA 中每一个程序都包含过程。录制的宏是一个过程，一个自定义函数也是一个过程。掌握好单个过程的编写思路，就可以组合成一个大中型插件或者专业程序。6.1.1 过程的分类与调用方式过程主要分为三类：子过程、函数过程和属性过程。这三类过程的
2、格式如下。Sub 子过程()End SubFunction 函数过程(rng As Range)End FunctionProperty Get 属性过程 () As VariantEnd Property本书主要讲述 Sub 子过程和 Function 函数过程的开发。Sub 过程是 VBA 中应用最广的过程，录制宏所产生的过程就是 Sub 过程。Sub 过程的执行方式包括五种。1.【Alt+F8】执行如果在工作表命令窗口、ThisWorkBook 命令窗口或者标准模块窗口中存在 Sub 过程，那么在工作表界面可以通过快捷键【Alt+F8】来执行该过程。Excel VBA 程序开发自学 （第
3、 2 版）假设在 VBE 界面中的 Sheet1 代码窗口中有一个 Sub 过程“汇总” ，在模块 1 中有一个名为“新建菜单”的 Sub 过程，那么通过快捷键【Alt+F8 】打开“宏”对话框后，将在对话框中产生两个可执行程序名，其中工作表命令窗口的 Sub 过程会连同工作表名一起出现在宏名列表中，而模块中的过程则仅仅列出过程名。用户选择目标程序并单击【执行】按钮即可启动 Sub 子过程。2.快捷键执行Sub 过程可以与某个快捷键进行关联，在后续的使用中就可以利用这个快捷键来调用对应的过程。设置 Sub 过程的快捷键主要有两种方式：利用宏对话框设置及用 VBA 代码指定。后者在本书其他章节将
4、会讲述，在此演示一下“宏”对话框设置宏的快捷键的方法：假设 VB 工程中有两个名为“汇总”和“新建菜单”的 Sub 过程，在工作表界面中按下快捷键【Alt+F8】调出图 6.1 所示对话框，然后选择“新建菜单 ”，并单击“选项”按钮，在弹出的“宏选项”对话框中指定快捷键，如图 6.2 所示。图中设计宏程序“新建菜单”的快捷键是【Ctrl+q 】 。图 6.1 “宏”对话框 图 6.2 设置 Sub 过程的快捷键3.按钮执行在工作表中建一个按钮，并将按钮与 Sub 过程关联，从而实现单击按钮执行程序。将按钮关联到 Sub 过程的步骤为：1.单击菜单【开发工具】【表单控件】【按钮】 ；2.在工作表
5、中按下左键并向右下方拖动，从而绘制一个控件按钮；3.在弹出的对话框中选择“新建菜单” ，如图 6.3 所示；4.返回工作表后即可单击名为“按钮 1”的按钮来执行程序“新建菜单” 。Excel VBA 程序开发自学 （第 2 版）图 6.3 关联过程与按钮4.菜单调用最常见的是编写一个自定义菜单或者工具条来调用 Sub 过程。菜单与工具条的设计方法参见本书第 19 章及第 20 章。5.事件引发对于部分需要自启动的程序，通常利用事件引发，不需要人工干预。例如工作簿开启时就自动执行某程序，或者关掉窗体、鼠标移过窗体时执行某程序对于事件过程的运用参见本书第 8 章。6.工作表中使用公式调用Funct
6、ion 过程即自定义函数，可以像使用内置的工作表函数一样在公式中使用。调用 Function 过程的步骤如下：1.单击菜单【插入】【模块】 ；2.在模块中录入以下代码：Function 成绩(rng)成绩 = IIF(rng = 60, “及格“, “不及格“)End Function3.返回工作表中，在 A1 输入数值 50，在 B1 输入公式：=成绩(A1)可以发现公式可以像内置函数一样运行，它返回“不及格” ，正是期望的结果。6.1.2 插入过程的方式编写过程时可以手工录入代码，也可以让利用 VBA 提供的列表自动产生程序外壳。一个 Sub 过程分为程序外壳部分和主体部分。如图 6.4
7、所示。Excel VBA 程序开发自学 （第 2 版）图 6.4 Sub 过程的外壳与主体部分示意图其中外壳部分可以手工录入，也可以利用 VBE 提供的方式完成。1.非事件过程对于非事件的 Sub 过程，VBA 提供了一个专用窗体来选择性录入过程的外壳。具体步骤如下：1.在 VBE 界面中单击菜单【插入】【模块】 ；2.单击菜单【插入】【过程】打开“添加过程”对话框；3.在“名称”框中录入“汇总” ，并将“类型”选择“子过程” ，将“范围”设为“私有的” ，如图 6.5 所示。然后单击“确定”按钮。图 6.5 添加 Sub 过程外壳图执行以上程序后在模块中可以看到产生的代码为：Private
8、Sub 汇总()End Sub如果是 Function 函数过程，也可以按照上述方法录入过程的外壳。2.事件类过程VBA 支持很多类事件，大部分事件的代码都需要参数。而这些参数是很难记忆的，包括所有 VBA 专业程序员。为了快速且准确地录入事件类过程，可以通过 VBE 提供的对象与过程窗口的下拉列表完成。例如输入工作表 SelectionChange 事件的过程，方法如下：（1）使用快捷键【Alt+F11】 进入 VBE 界面，并用快捷键 【Ctrl+g 】打开工程资源管理器窗口；（2）双击 Sheet1 或者其他需要录入工作表事件的工作表名；Excel VBA 程序开发自学 （第 2 版）（
9、3）从对象窗口的下拉框中选择“Worksheet” ，代码窗口默认产生以下代码：Private Sub Worksheet_SelectionChange(ByVal Target As Range)End Sub图 6.6 从下拉列表选择对象因为 VBA 默认状态下就是弹出“Worksheet_SelectionChange”事件的代码，所以当选择对象为“Worksheet”后就产生了需要的代码。如果需要录入 “Worksheet_Change”事件的代码，则需要在选择对象“Worksheet”后，再选择过程“Change” ，然后将产生的“Worksheet_SelectionChange
10、”事件的代码删除，仅保留以下代码：Private Sub Worksheet_Change(ByVal Target As Range)End Sub对于此类包含参数的事件过程，应该尽量选择通过对象与过程窗口的下拉列表产生代码的方式，手工录入很容易产生误差。在用户窗体中很多事件也支持参数，而且有多个参数，通常也需要从列表中选择对象与过程的方式来录入代码。例如在窗体中录入鼠标移过事件的过程代码，步骤如下：1.单击菜单【插入】【用户窗体】 ；2.使用快捷键【Ctrl+g】显示工程资源管理器，并在 UserForm1（或者别的名称）上单击右键，选择菜单【查看代码】 ； 3.从对象窗口选择“UserF
11、orm1” ，此时默认产生“UserForm_Click ”事件的代码；再从过程窗口选择“MouseMove ”，代码窗口中将产生以下代码：Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)End Sub4.删除“UserForm_Click”事件的代码。6.1.3 过程的命名规则过程的命名与变量的命名规则一致。但需要补充的一点是：过程名可以和本过程的私有变量同名，但却不能和公有变量同名。例如：Sub 身份证()
12、Dim 身份证 As StringExcel VBA 程序开发自学 （第 2 版）身份证 = a1.TextEnd Sub以上代码中过程与变量同名，但这是允许的。Dim 身份证 As StringSub 身份证()身份证 = a1.TextEnd Sub这段代码却是非法的，只要运行程序就会弹出编译错误。为了避免错误及便于识别，需要使本过程的私有变量也尽量保持与过程名不相同。6.2 编写 Sub 过程本节开始了解关于 Sub 过程的基本概念，以及编写简单的 Sub 过程。6.2.1 Sub 过程的语法解析Sub 过程即利用 Sub 语句声明的子过程。所有宏录制器产生的过程全是 Sub 过程，无法
13、通过录制宏产生 Function 过程或者属性过程。Sub 语句声明过程的语法如下：Private | Public | Friend Static Sub name (arglist)statementsExit SubstatementsEnd Sub其中各参数的详细功能如表 6-1 所示。表 6-1 Sub 语句参数详解参数部分 功能解释Public 可选的。表示所有模块的所有其他过程都可访问这个 Sub 过程。 如果在包含 Option Private 的模块中使用，则这个过程在该工程外是不可使用的Private 可选的。表示只有在包含其声明的模块中的其他过程可以访问该 Sub 过程F
14、riend 可选的。只能在类模块中使用。表示该 Sub 过程在整个工程中都是可见的，但对对象实例的控制者是不可见的Static 可选的。表示在调用之间保留 Sub 过程的局部变量的值。Static 属性对在 Sub 外声明的变量不会产生影响，即使过程中也使用了这些变量name 必需的。Sub 的名称；遵循标准的变量命名约定arglist 可选的。代表在调用时要传递给 Sub 过程的参数的变量列表。多个变量则用逗号隔开statements 可选的。Sub 过程中所执行的任何语句组Sub 过程与所有变量一样，也区分公有和私有，而在说法上稍有区别。过程分模块级过程和工程级过程。Excel VBA 程
15、序开发自学 （第 2 版）1.模块级过程模块级过程即只能在当前模块调用的过程，它的特征有三个：（1）声明 Sub 过程前使用 Private；（2）只有当前模块可以调用，例如在“模块 1”中有以下代码：Private Sub 过程一()MsgBox 123End SubPrivate Sub 过程二()Call 过程一End Sub执行过程二时可以调用过程一，但如果过程二存放于“模块 2”中，则将弹出“子过程未定义”的错误提示。（3）不出现在“宏”对话框中，即使用快捷键【Alt+F8】所打开的对话框中无法查看到当前过程的名称列表。如果是 Function 过程，则无法在函数向导中查看到函数名。
16、提示：所有事件的代码都是过程级的，默认状态下只能在当前过程可以调用。2.工程级过程工程级过程是指在当前工程中任意地方都可以随意调用的过程。它的特征刚好与模块级过程相反：在“Sub”语句前置标识符“Public” 、非当前过程可以调用，可以出现在“宏”对话框中。如果一个过程没有使用“Public”和“Private”标识，则默认为公有过程，任何模块或者窗体中都可以调用。Sub 过程也支持参数，其参数的用法与 Function 过程的参数用法一致，本小节不详述，请参阅本书 6.4 节。3.Exit sub 与 End 的作用与区别Sub 过程可以在程序执行期间随时退出程序，通常是设定若干个条件。当
17、满足条件时使用“Exit Sub”语句来终止程序。 “Exit Sub”语句后面的代码不再执行。也可以使用“End”语句来退出程序。 “End”和“Exit Sub”在使用中有相同处，也有明显的差别。相同处是都可以中途终止运行程序，不同处则有以下两点。（1）是否释放公有变量从以下三段代码可以体现“End”和“Exit Sub”的差异：Dim x As LongSub A()x = 888Exit SubEnd SubSub B()x = 888EndEnd SubSub C()Excel VBA 程序开发自学 （第 2 版）MsgBox xEnd Sub代码中 X 是公有变量，当执行过程 A
18、后执行过程 C，那么变量 X 的值为 888，表示 X变量的值在过程中并没有释放， “Exit Sub”仅仅退出程序执行，公有变量的值保持不变。如果执行过程 B 再执行过程 C，那么 X 的值则为 0，说明在过程 B 中的“End”语句已经释放变量 X 的值。（2）是否终止所有程序仍然用三个过程来演示“End”和“Exit Sub”的差异：Sub A()Call BMsgBox “终止“End SubSub B()Exit Sub End SubSub C()EndEnd Sub执行程序 A 的结果是弹出对话框 “终止” ，而将过程 A 中的 “Call B”修改为“Call C”，那么什么反
19、应也没有。也就是“Exit Sub”是退出它所在的程序，而“End”则中止所有程序，包括调用它的程序。如果在窗体代码中， “Exit Sub”仅仅退出事件，而“End”则退出事件后关掉窗体，窗体中声明的所有变量全部释放。6.2.2 Sub 过程的执行流程如果录制宏并执行宏，可以看出宏代码的执行流程永远是从上到下。可以使用调试功能来查看流程。例如执行以下代码：Sub 设置 A1 单元格()Range(“A1“).SelectRange(“A1“) = “中华人民共和国“Range(“A1“).Interior.Color = 65535Range(“A1“).Font.ColorIndex =
20、3Range(“A1“).Borders.LineStyle = xlContinuousRange(“A1“).Font.Name = “黑体“Range(“A1“).Font.Size = 20Range(“A1“).EntireColumn.AutoFitEnd Sub将 VBE 窗口缩小，使自己能同时看到代码及 A1 单元格的情况下再按下快捷键【F8】 ，从而进入逐句调试阶段。注意：在 VBE 中使用【F8】键表示调试代码语句，每按一次【F8 】键即 执行一句，忽略变量与常量的声明语句，直到“Exit Sub”或者“ End”、“End Sub”为止。在 编写代码时非常有用，可以借助它
21、 检查代码的准确Excel VBA 程序开发自学 （第 2 版）性，同时 也可以查 看程序间的跳转 是否正常（当有标签设置和嵌套调用的时候）。当按下调试键【F8】时，当前执行的语句呈黄色显示，再次按下【F8】键时，则下一句呈黄色显示，而操作对象 A1 则对应产生变化。图 6.7 中已执行到第四句，所以 A2 单元格同步后的状态就是已录入“中华人民共和国” ，且并设置了黄色背景色。图 6.7 逐步执行代码当继续通过【F8】键执行完成的代码后，可以得出结论：所有录制的宏和未特别指定程序跳转的 VBA 代码总是按照从上至下的流程逐句执行。那么是否有例外呢？通常在以下三种情况会有例外。1.使用冒号实现
22、一行执行多句代码VBA 中允许借助冒号将多句代码写在同一行执行。对同行中的代码按从左向右的顺序执行。例如：Sub 设置 A1 单元格()Range(“A1“) = “中华人民共和国“: Range(“A1“).Interior.Color = 65535Range(“A1“).Font.ColorIndex = 3: Range(“A1“).Font.Size = 20Range(“A1“).EntireColumn.AutoFitEnd Sub以上代码在借助冒号将前四行代码缩至两行，但执行过程仍然为四步。对于同行中有多句代码时，按从左向右的顺序执行。那么读者一定可以想到，使用冒号和不使用冒号
23、的执行结果岂不是完全一致？仅仅改变了行数？答案是“有时一致，有时不一致” 。如果以上的代码按如下方式编写，那么通过冒号改变行数后执行结果完全一致：Sub 设置 A1 单元格()Range(“A1“) = “中华人民共和国“Range(“A1“).Interior.Color = 65535Range(“A1“).Font.ColorIndex = 3Range(“A1“).Font.Size = 20Range(“A1“).EntireColumn.AutoFitEnd SubExcel VBA 程序开发自学 （第 2 版）而在下面的情况中，使用冒号后却可以得到完全不同的结果：Sub 判断是否
24、及格 1()IF B2 = 60 Then C3 = “及格“: Exit SubIF B3 = 60 Then C3 = “及格“End SubSub 判断是否及格 2()IF B2 = 60 Then C3 = “及格“Exit SubIF B3 = 60 Then C3 = “及格“End Sub假设工作表中有图 6.8 所示数据，执行过程“判断是否及格 1”时，C3 单元格将出现“及格” ；而执行过程“判断是否及格 2”时则无任何反应。也就是说“Exit Sub”语句与 IF同行时，只有单元格 B2 的值大于或等于 60， “Exit Sub”语句才会执行。在本例中不符合条件，那么没有
25、退出程序，可以继续执行其后的代码。而“Exit Sub”语句单独占据一行时，不管单元格 B2 是否符合条件， “Exit Sub”都会执行，从而退出程序，不再对 B3 的值进行判断。图 6.8 数据2.使用标签改变执行流程VBA 可以在代码中设置一个或者多个标签，然后让程序在满足某条件时跳转到标签处，从而改变过程执行流程。标签的规则是： 可以是标点符号以外的字符组合 以冒号（:）结尾 与大小写无关 必须位于一行的最左端 配合 GoTo 使用例如，建立一个名为“总表”的工作表，代码如下：Sub 新建总表()For i = 1 To Sheets.CountIF Sheets(i).Name =
26、“总表“ Then GoTo errNext iSheets.AddActiveSheet.Name = “总表“Enderr:MsgBox “已经存在总表“End Sub以上代码首先利用 For 循环逐一检查工作表的名字，如果某个工作表的名字等于“总表”Excel VBA 程序开发自学 （第 2 版）则执行标签“Err”之后的代码，否则继续执行 For 循环，直到循环完成并新建一个工作表且命名为“总表” 。使用标签完成当前程序间的跳转时需要注意两点：（1）标签名后面必须带有冒号。（2）在标签之前根据需要，及时退出程序。在本例中，按照设计意图，只要工作簿中存在“总表”则执行标签“Err”之后的
27、语句，反之不执行。所以标签之前必须加入“End”或者“Exit Sub”来退出程序，否则任何情况下Err 后的语句都会被执行。在一个过程中还可以定义多个标签。例如：Sub 新建总表()MsgBox ActiveWorkbook.ProtectWindowsIF ActiveWorkbook.ProtectWindows = true Then GoTo 已加密For i = 1 To Sheets.CountIF Sheets(i).Name = “总表“ Then GoTo 已存在Next iSheets.AddActiveSheet.Name = “总表“End已存在:MsgBox “已经
28、存在总表“End已加密:MsgBox “当前工作簿窗口已锁定，无法建立新表“End Sub在此过程中，首先判断当前工作表的窗口是否锁定，如果锁定则执行“已加密”标签后的语句；然后再检查是否存在“总表” ，当有“总表”时执行“已存在”标签后的语句。本例中两个标签没有顺序上的差异，谁前谁后不影响代码的结果。3.Sub 过程的嵌套调用方式过程与过程之间是可以相互调用的，从而使代码的执行流程改变。通过 VBA 代码调用Sub 子过程主要有两种方式。 Call 语句Call 语句的功能是将一个过程的控制权转移到另一个过程。它的语法为：Call name argumentlist，即 Call 过程名 参
29、数。其中 Call 是可选的，即在其他过程调用过程一时可以有以下两种形式：Sub 过程一()MsgBox “你好！“End SubPrivate Sub 过程二()过程一End SubPrivate Sub 过程三()Call 过程一Excel VBA 程序开发自学 （第 2 版）End Sub过程二和过程三都是合法的过程调用。 Run 方法Run 方法可以运行一个宏或者调用一个函数。该方法可用于运行一个用 Visual Basic 或 Excel 宏语言编写的宏或者运行 DLL 或 XLL 中的函数。实例如下：Sub 过程四()Application.Run “过程一“End Sub其中“A
30、pplication.Run”也可以简写为 “Run”。6.2.3 过程的递归所有过程都是可以递归的，即可以调用自己来完成任务。实际工作中需要调用过程本身的实例极少，通常进入递归都是编码有问题而误入递归状态，结果耗尽系统资源。在某些情况下也可以故意调用自己来完成任务。例如下面 2 例。1.按条件新建工作表Sub 建立 10 个表()IF Sheets.Count = 10 Then Exit SubSheets.Add , Sheets(Sheets.Count), 1Call 建立 10 个表End Sub以上代码中，首先利用 IF 查找并检测当前工作簿的工作表数量，如果大于或等于 10 则
31、退出程序，否则在最后位置新建一个工作表，最后再调用自身继续执行，直到满足条件“大于或等于 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“), “时间“En
32、d SubSub 终止()Application.OnTime Now() + TimeValue(“00:00:01“), “时间“, , falseEnd Sub以上代码实现的效果是在单元格显示当前时间，包括时、分、秒，且每秒钟更新一次。通过递归方式让程序每秒钟执行一次实现时钟的效果，同时再利用另一个过程随时退出递归。当然也可以用快捷键【Ctrl+Break】 。Excel VBA 程序开发自学 （第 2 版）6.2.4 Sub 过程实例演示为了更好地理解 Sub 过程，通过两个实例来展示。1.统计选区信息：不带参数的 Sub 过程要求：对任意选区统计单元格个数、数值个数、非空单元格个数、
33、空白单元格个数及选区之和。代码如下：Sub 选区统计()Dim msg As Stringmsg = “单元格个数：“ & 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
34、 = 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(T
35、arget, 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”。 Excel VBA 程序开发自学 （第 2 版）6.3 认识 Function 过
36、程Function 过程即自定义函数，在插件中应用极广。本节介绍关于 Function 过程的语法及调用方法。6.3.1 Function 过程的特点Function 过程的功能较 Sub 过程的应用范围稍小， Function 过程仅仅用于返回一个值或者多个数的组合，即数组，而 Sub 过程既可以返回值，还可以对引用的对象进行修改。例如，引用单元格 A1 的值后对单元格 A1 设置新的格式，或者修改工作表名称等。 Function 可以获取工作表名称，但无法修改工作表的名称。Function 过程可以不使用参数，类似于工作表函数 Rand 和 Now 等，但绝大部分函数是需要一个参数或者多个
37、参数的，最多时可达 255 个参数。6.3.2 Function 的语法解析Function 的语法如下：Public | Private | Friend Static Function name (arglist) As typestatementsname = expressionExit Functionstatementsname = expressionEnd FunctionFunction 语句的各参数详解如表 6-2 所示。表 6-2 Function语句参数详解参数部分 功能解释Public 可选的。表示所有模块的所有其他过程都可以访问这个 Function 过程。如果是在
38、包含 Option Private 的模块中使用，则这个过程在该工程外是不可使用的Private 可选的。表示只有包含其声明的模块的其他过程可以访问该 Function 过程Friend 可选的。只能在类模块中使用。表示该 Function 过程在整个过程中都是可见的，但对于对象实例的控制者是不可见的Static 可选的。表示在调用之间将保留 Function 过程的局部变量值。Static 属性对在该 Function 外声明的变量不会产生影响，即使过程中也使用了这些变量name 必需的。Function 的名称，遵循标准的变量命名约定arglist 可选的。代表在调用时要传递给 Funct
39、ion 过程的参数变量列表。多个变量应用逗号隔开type 可选的。Function 过程的返回值的数据类型，可以是 Byte、 Boolean 、Integer、Long 、 Currency、Single、Double 、Decimal（目前尚不支持） 、Excel VBA 程序开发自学 （第 2 版）Date、String（除定长） 、Object 、Variant或任何用户定义类型statements 可选的。在 Function 过程中执行的任何语句组expression 可选的。Function 的返回值和 Sub 过程一样，Function 过程也有模块级过程和工程级过程之分。Fu
40、nction 前置“Public”即为工程级，前置“Private”则为模块级。Function 名称在声明时需要遵循与 Sub 过程一样的规则。如果自定义的 Function 名称与 VBA 内部名称一致，仍然可以正常执行，只是在代码中调用 Excel 内部函数时必须声明其对象库。例如：Function sqr(AA)sqr = AA (1 / 3)End FunctionSub test()MsgBox “VBA.SQR:“ & VBA.sqr(27) & Chr(10) & “SQR:“ & sqr(27)End Sub执行 test 过程时的结果如图 6.11 所示。图 6.11 自定
41、义 SQR 和内置 SQR 的分别从结果可以得知，在代码使用“VBA.SQR”可以调用 VBA 自带的 SQR 功能，而直接使用 SQR 则调用自定义的 SQR 函数的功能。虽然定义函数时允许与内部函数一致，但却不允许与定义的变量或者常量一致，不管这个变量或者常量是本过程私有的还是模块中公有的，否则将产生“发现二义性的名称”的编译错误。6.3.3 调用 Function 过程Function 过程通常以三种方式调用。（1）在工作表中通过公式调用：像内部函数一样在工作表中使用，也可以与其他函数嵌套。（2）在 VBA 代码中被其他过程调用：就像图 6.9 对应的那段代码一样在 Sub 过程调用函数
42、。（3）递归：Function 过程和 Sub 一样可以实现递归。如果不是刻意地、有计划地进入递归状态，可能会造成资源耗尽或者溢出堆栈空间。例如下面函数的调用：Function 递归( 参数)递归 = 递归(参数)End FunctionExcel VBA 程序开发自学 （第 2 版）Sub 测试()MsgBox 递归(1000)End Sub将代码录入到模块中后，执行过程“测试” ，立即弹出错误提示“溢出堆栈空间” 。为了避免递归造成的错误，甚至程序崩溃，尽量不要调用自身，开发函数、插件时多方面查核是否可能造成循环引用、递归现象。当然，有目的、有条件的递归是可以给工作带来便利的。另外，谈到函
43、数就不能不说它的“刷新”性能，即在工作表中使用函数时，当在其他区域的数据更新时，当前单元格的函数是否重新运算，专业术语称之为“易失性” 。用户定义的函数是否有易失性可以使用以下语句来控制：Application.Volatile 该语句的作用是无论何时在工作表的任意单元格中进行计算，函数都必须重新进行计算。即工作表刷新时调用函数再运算一次，从而实现数据更新，使公式结果同步。6.4 关于过程的参数Sub 过程和 Function 过程都可以使用参数。有参数的过程相对于无参数的过程更具灵活性，相当于给了用户更多自定义的空间。6.4.1 Sub 过程的参数及应用Sub 过程的语法是：Private
44、| Public | Friend Static Sub name (arglist)statementsExit SubstatementsEnd Sub其中“(arglist)”即表示它支持可选的参数，可以不用参数，也可以使用参数；可以使用一个参数，也可以使用多个参数。其中参数(arglist)的具体语法如下：Optional ByVal | ByRef ParamArray varname( ) As type = defaultvalue表 6-3 Sub过程参数详解部分 功能详解Optional 可选的。表示参数不是必需的关键字。如果使用了该选项，则 arglist 中的后续参数都必
45、须是可选的，而且必须都使用 Optional 关键字声明。如果使用了 ParamArray，则任何参数都不能使用 OptionalByVal 可选的。表示该参数按值传递ByRef 可选的。表示该参数按地址传递。ByRef 是 Visual Basic 的默认选项Excel VBA 程序开发自学 （第 2 版）ParamArray 可选的。只用于 arglist 的最后一个参数，指明最后这个参数是一个 Variant 元素的 Optional 数组。使用 ParamArray 关 键 字 可 以 提 供 任 意 数 目 的 参 数 。 ParamArray 关 键 字不 能 与 ByVal、 B
46、yRef或 Optional 一 起 使 用varname 必需的。代表参数的变量的名称，遵循标准的变量命名约定type 可选的。传递给该过程的参数的数据类型，如果没有选择参数 Optional，则可以指定用户定义类型，或对象类型defaultvalue 可选的。任何常数或常数表达式。只对 Optional 参数合法。如果类型为 Object，则显式的默认值只能是 Nothing从表中可以看出，如果需要给 Sub 过程设置一个可选参数，则可以使用关键字 Optional来声明，如果需要设置多个可选参数，则可以使用关键字 ParamArray 来声明参数。下例即为使用一个参数的 Sub 过程：S
47、ub 过程一(msg As String)IF Len(msg) = 1 And i = 60 Then MsgBox “及格“ Else MsgBox “不及格“End SubSub Test()成绩 (59)End Sub执行“Test”过程可以正确判断成绩 59 分是否及格。但若改用 Function 过程则一定出错：Function 成绩( 成绩)IF 成绩 = 60 Then 成绩 = “及格“ Else 成绩 = “不及格“End FunctionSub Test()MsgBox 成绩(59)End Sub执行过程“Test”后将弹出“当前范围内的声明重复”的编译错误。即使再修改为
48、以下方式仍然报错：Function 成绩( 成绩)IF 成绩 = 60 Then MsgBox “及格“ Else MsgBox “不及格“End FunctionSub Test()Call 成绩 (59)End Sub正确的方式是：Function 成绩( 分数)IF 分数 = 60 Then 成绩 = “及格“ Else 成绩 = “不及格“End FunctionSub Test()MsgBox 成绩(59)End Sub6.5 开发自定义函数6.4 节对自定义函数的基础知识做了详解，本节则进行实例演示，通过带有不同参数的函数定义过程来增进读者的理解与编写功底。对于本节中开发函数中所涉
49、及的各种语法，如条件语句、循环语句、With 语句以及常见对象的命令请参阅本书第 10 章和第 11 章。也可以阅读完第 10 章及第 11 章后再返回本节练习开发函数。Excel VBA 程序开发自学 （第 2 版）6.5.1 开发不带参数的 Function 过程1.获取本机 IP 地址【要求】：利用函数取得本机 IP 地址。【代码】：Function IP() 声明函数，无参数Dim item通过 WMI 技术获取网卡当前设置的 IP 地址For Each item In GetObject(“winmgmts:“ & “.“ & “rootcimv2“).ExecQuery(“Select * from Win32_NetworkAdapterConfiguration“)If TypeName(item.IPAddress) Application.ThisCell.Address Then 如果变量 rng 的地址不等于当前公式所在单元格的地址If cell Is Nothing Then
