《Excel_VBA程序开发自学宝典(第2版)》

第06章　编写SUB过程及开发函数 VBA的主体结构就是过程。VBA包括子过程、函数过程和属性过程三种，本书主要介绍子过程（也称Sub过程）和函数过程（也称Function过程）。 本章要点 · 认识过程 · Sub过程 · Function过程 · 关于过程参数 · 开发自定义函数 · 编写函数帮助 6.1　认识过程 VBA中每一个程序都包含过程。录制的宏是一个过程，一个自定义函数也是一个过程。掌握好单个过程的编写思路，就可以组合成一个大中型插件或者专业程序。 6.1.1　过程的分类与调用方式 过程主要分为三类：子过程、函数过程和属性过程。这三类过程的 格式 pdf格式笔记格式下载页码格式下载公文格式下载简报格式下载 如下。 Sub 子过程() …… End Sub Function 函数过程(rng As Range) …… End Function Property Get 属性过程() As Variant …… End Property 本书主要讲述Sub子过程和Function函数过程的开发。 Sub过程是VBA中应用最广的过程，录制宏所产生的过程就是Sub过程。Sub过程的执行方式包括五种。 1.【Alt+F8】执行 如果在工作 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 命令窗口、ThisWorkBook命令窗口或者标准模块窗口中存在Sub过程，那么在工作表界面可以通过快捷键【Alt+F8】来执行该过程。 假设在VBE界面中的Sheet1代码窗口中有一个Sub过程“汇总”，在模块1中有一个名为“新建菜单”的Sub过程，那么通过快捷键【Alt+F8】打开“宏”对话框后，将在对话框中产生两个可执行程序名，其中工作表命令窗口的Sub过程会连同工作表名一起出现在宏名列表中，而模块中的过程则仅仅列出过程名。用户选择目标程序并单击【执行】按钮即可启动Sub子过程。 2.快捷键执行 Sub过程可以与某个快捷键进行关联，在后续的使用中就可以利用这个快捷键来调用对应的过程。 设置Sub过程的快捷键主要有两种方式：利用宏对话框设置及用VBA代码指定。后者在本书其他章节将会讲述，在此演示一下“宏”对话框设置宏的快捷键的 方法 快递客服问题件处理详细方法山木方法pdf计算方法pdf华与华方法下载八字理论方法下载 ： 假设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或者其他需要录入工作表事件的工作表名； （3）从对象窗口的下拉框中选择“Worksheet”，代码窗口默认产生以下代码： Private Sub Worksheet_SelectionChange(ByVal Target As Range) End Sub 图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 身份证 = [a1].Text End Sub 以上代码中过程与变量同名，但这是允许的。 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-1　Sub语句参数详解 参数部分 功能解释 Public 可选的。表示所有模块的所有其他过程都可访问这个 Sub 过程。 如果在包含 Option Private 的模块中使用，则这个过程在该工程外是不可使用的 Private 可选的。表示只有在包含其声明的模块中的其他过程可以访问该 Sub 过程 Friend 可选的。只能在类模块中使用。表示该 Sub 过程在整个工程中都是可见的，但对对象实例的控制者是不可见的 Static 可选的。表示在调用之间保留 Sub 过程的局部变量的值。Static 属性对在 Sub 外声明的变量不会产生影响，即使过程中也使用了这些变量 name 必需的。Sub 的名称；遵循标准的变量命名约定 arglist 可选的。代表在调用时要传递给 Sub 过程的参数的变量列表。多个变量则用逗号隔开 statements 可选的。Sub 过程中所执行的任何语句组 Sub过程与所有变量一样，也区分公有和私有，而在说法上稍有区别。过程分模块级过程和工程级过程。 1.模块级过程 模块级过程即只能在当前模块调用的过程，它的特征有三个： （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() MsgBox x End Sub 代码中X是公有变量，当执行过程A后执行过程C，那么变量X的值为888，表示X变量的值在过程中并没有释放，“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").Font.Name = "黑体" Range("A1").Font.Size = 20 Range("A1").EntireColumn.AutoFit End Sub 将VBE窗口缩小，使自己能同时看到代码及A1单元格的情况下再按下快捷键【F8】，从而进入逐句调试阶段。 注意：在VBE中使用【F8】键表示调试代码语句，每按一次【F8】键即执行一句，忽略变量与常量的声明语句，直到“Exit Sub”或者“End”、“End Sub”为止。在编写代码时非常有用，可以借助它检查代码的准确性，同时也可以查看程序间的跳转是否正常（当有标签设置和嵌套调用的时候）。 当按下调试键【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 以上代码在借助冒号将前四行代码缩至两行，但执行过程仍然为四步。对于同行中有多句代码时，按从左向右的顺序执行。 那么读者一定可以想到，使用冒号和不使用冒号的执行结果岂不是完全一致？仅仅改变了行数？ 答案 八年级地理上册填图题岩土工程勘察试题省略号的作用及举例应急救援安全知识车间5s试题及答案 是“有时一致，有时不一致”。如果以上的代码按如下方式编写，那么通过冒号改变行数后执行结果完全一致： 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 判断是否及格1() IF [B2] >= 60 Then [C3] = "及格": Exit Sub IF [B3] >= 60 Then [C3] = "及格" End Sub Sub 判断是否及格2() IF [B2] >= 60 Then [C3] = "及格" Exit Sub IF [B3] >= 60 Then [C3] = "及格" End Sub 假设工作表中有图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 ActiveSheet.Name = "总表" End err: MsgBox "已经存在总表" End Sub 以上代码首先利用For循环逐一检查工作表的名字，如果某个工作表的名字等于“总表”则执行标签“Err”之后的代码，否则继续执行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 ActiveSheet.Name = "总表" 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 过程一 End Sub 过程二和过程三都是合法的过程调用。 · Run方法 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】。 6.2.4 Sub过程实例演示 为了更好地理解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”。 6.3　认识Function过程 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-2　Function语句参数详解 参数部分 功能解释 Public 可选的。表示所有模块的所有其他过程都可以访问这个 Function 过程。如果是在包含 Option Private 的模块中使用，则这个过程在该工程外是不可使用的 Private 可选的。表示只有包含其声明的模块的其他过程可以访问该 Function 过程 Friend 可选的。只能在类模块中使用。表示该 Function 过程在整个过程中都是可见的，但对于对象实例的控制者是不可见的 Static 可选的。表示在调用之间将保留 Function 过程的局部变量值。Static 属性对在该 Function 外声明的变量不会产生影响，即使过程中也使用了这些变量 name 必需的。Function 的名称，遵循标准的变量命名约定 arglist 可选的。代表在调用时要传递给 Function 过程的参数变量列表。多个变量应用逗号隔开 type 可选的。Function 过程的返回值的数据类型，可以是 Byte、 Boolean 、Integer、Long、Currency、Single、Double、Decimal（目前尚不支持）、Date、String（除定长）、Object、Variant或任何用户定义类型 statements 可选的。在 Function 过程中执行的任何语句组 expression 可选的。Function 的返回值 和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 Sub 测试() MsgBox 递归(1000) End Sub 将代码录入到模块中后，执行过程“测试”，立即弹出错误提示“溢出堆栈空间”。 为了避免递归造成的错误，甚至程序崩溃，尽量不要调用自身，开发函数、插件时多方面查核是否可能造成循环引用、递归现象。当然，有目的、有条件的递归是可以给工作带来便利的。 另外，谈到函数就不能不说它的“刷新”性能，即在工作表中使用函数时，当在其他区域的数据更新时，当前单元格的函数是否重新运算，专业术语称之为“易失性”。 用户定义的函数是否有易失性可以使用以下语句来控制： 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过程参数详解 部分 功能详解 Optional 可选的。表示参数不是必需的关键字。如果使用了该选项，则 arglist 中的后续参数都必须是可选的，而且必须都使用 Optional 关键字声明。如果使用了 ParamArray，则任何参数都不能使用 Optional ByVal 可选的。表示该参数按值传递 ByRef 可选的。表示该参数按地址传递。ByRef 是 Visual Basic 的默认选项 ParamArray 可选的。只用于 arglist 的最后一个参数，指明最后这个参数是一个 Variant 元素的 Optional 数组。使用 ParamArray 关键字可以提供任意数目的参数。ParamArray 关键字不能与 ByVal、ByRef或 Optional 一起使用 varname 必需的。代表参数的变量的名称，遵循标准的变量命名约定 type 可选的。传递给该过程的参数的数据类型，如果没有选择参数 Optional，则可以指定用户定义类型，或对象类型 defaultvalue 可选的。任何常数或常数表达式。只对 Optional 参数合法。如果类型为 Object，则显式的默认值只能是 Nothing 从表中可以看出，如果需要给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 Sub 确认权限一() '手工指定姓名 Call 姓名(Application.InputBox("请输入您的姓名", "确认权限", "", , , , , 2)) End Sub Sub 确认权限二() '以当前表A1的值进行判断 Call 姓名(ActiveSheet.Range("A1")) End Sub Sub 确认权限三() '以Office安装用户名进行判断 Call 姓名(Application.UserName) End Sub 以上代码用于判断指定的用户名是否具有操作权限。在工作簿中有一个工作表名为“许可人员列表”，该表中A1:A10存放10个允许操作的人员名单。程序会将用户输入或者指定方式获取的姓名与表A1:A10中允许的姓名进行比较，如果与任何一个一致则提示“你具有操作权限”，否则提示“你无操作权限”。 在过程“确认权限一”、“确认权限二”和“确认权限三”中都可以调用过程“姓名”，只是参数不同。如果不使用过程“姓名(Name)”作过渡的话，那么过程“姓名(Name)”中的所有代码需要在后面三个过程中出现三次，每一个过程都需要对参数进行多次判断及循环，从而使整个工程的代码偏长。 提示：本例文件参见光盘：..\ 第六章\确认权限.xlsm 下例再演示具有两个参数但第二个参数是可选参数的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处理，所以结果为第一个工作表重命名为“汇总表”。 6.4.2 按值传递与按址传递的参数 声明过程的参数时，可以通过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 成绩(成绩) IF 成绩 >= 60 Then MsgBox "及格" Else MsgBox "不及格" End Sub Sub Test() 成绩 (59) End 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地址。 【代码】： Function IP() '声明函数，无参数 Dim item '通过WMI技术获取网卡当前设置的IP地址 For Each item In GetObject("winmgmts:\\" & "." & "\root\cimv2").ExecQuery("Select * from Win32_NetworkAdapterConfiguration") If TypeName(item.IPAddress) <> "Null" Then IP = item.IPAddress(0) Next End Function 【测试】： 将以上代码录入模块中，然后返回工作表界面。 在B2单元格录入公式： =IP() 公式将返回本机的IP地址，结果如图6.13所示。 图6.13 测试不随时间变化的时间函数 提示：如果本机有多个网址，本函数取最后一个网卡地址；如果需要取第一个网卡地址，在Next语句前加一句“Exit For”即可。 点评 VBA本身不具备查IP地址的功能，但WMI技术可以获取很多操作系统级别的信息，VBA调用WMI的语句后就变相地实现了获取IP的功能。 提示：本例文件参见光盘：..\ 第六章\自定义函数.xlsm 2.返回有公式的单元格地址 【要求】：返回当前工作表中全部有公式的单元格地址。 【代码】： Function FunAdd() '声明函数，无参数 Dim rng As Range, cell As Range '声明两个变量,Range类型 For Each rng In ActiveSheet.UsedRange '遍历当前表的已用区域 If rng.HasFormula Then '如果单元格有公式 If rng.Address <> Application.ThisCell.Address Then '如果变量rng的地址不等于当前公式所在单元格的地址 If cell Is Nothing Then '如果变量cell未初始化 Set cell = rng '将变量rng赋值给变量cell Else '否则 Set cell = Application.Union(cell, rng) '将变量cell与rng所代表的两个单元格对象合并一个对象,赋值给cell End If End If End If Next rng '如果变量cell未初始化(即未找到有符合条件的单元格)那么返回空文本,否则返回cell的地址 If cell Is Nothing Then FunAdd = "" Else FunAdd = cell.Address(0, 0) End Function 【测试】： 进入工作表，在单元格录入以下公式即可获取有公式的单元格地址，如图6.14所示。 =工作簿名() 图6.14返回有公式的单元格地址 提示：Thiscell表示当前公式所在单元格。为了避免函数返回的地址中包括本公式的单元格,采用IF排除Thiscell。 点评 单元格是否有公式，没有任何工作表函数可以判断。宏表函数可以实现，但不能批量计算，它只能判断一个单元格是否有公式。本例自定义函数可以弥补此不足。 6.5.2 开发带有一个参数的Function过程 1.将人民币金额转换为大写 【要求】：对于财务报表，金额默认为阿拉伯数字，现需将其转换成人民币大写形式。 【代码】： Function 大写(CELL As String) As String '声明函数名,有一个参数 Dim RMBS As String IF CELL = "" Or Not IsNumeric(CELL) Then 大写 = "": Exit Function '如果参数为空或者非数值则返回空白 IF CELL = 0 Then 大写 = "零元整": Exit Function '如果参数为0则返回"零元整" '将数值转换成中文大写,并将点替换成"元",将负号替换成"负" RMBS = Replace(Replace(Application.Text(Round(CELL, 2), "[DBnum2]"), ".", "元"), "-", "负") '加入角与分,同时将最后的"零"替换成"元整" RMBS = IIF(Left(Right(RMBS, 3), 1) = "元", Left(RMBS, Len(RMBS) - 1) & "角" & Right(RMBS, 1) & "分", IIF(Left(Right(RMBS, 2), 1) = "元", RMBS & "角", IIF(RMBS