《Excel VBA程序开发自学宝典(第2版)》.doc

Excel VBA程序开发自学 （第2版）第06章编写SUB过程及开发函数VBA的主体结构就是过程。VBA包括子过程、函数过程和属性过程三种，本书主要介绍子过程（也称Sub过程）和函数过程（也称Function过程）。本章要点u 认识过程u Sub过程u Function过程u 关于过程参数u 开发自定义函数u 编写函数帮助6.1认识过程VBA中每一个程序都包含过程。录制的宏是一个过程，一个自定义函数也是一个过程。掌握好单个过程的编写思路，就可以组合成一个大中型插件或者专业程序。6.1.1过程的分类与调用方式过程主要分为三类：子过程、函数过程和属性过程。这三类过程的格式如下。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】来执行该过程。假设在VBE界面中的Sheet1代码窗口中有一个Sub过程“汇总”，在模块1中有一个名为“新建菜单”的Sub过程，那么通过快捷键【Alt+F8】打开“宏”对话框后，将在对话框中产生两个可执行程序名，其中工作表命令窗口的Sub过程会连同工作表名一起出现在宏名列表中，而模块中的过程则仅仅列出过程名。用户选择目标程序并单击【执行】按钮即可启动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 Function3.返回工作表中，在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 Sub4.删除“UserForm_Click”事件的代码。6.1.3过程的命名规则过程的命名与变量的命名规则一致。但需要补充的一点是：过程名可以和本过程的私有变量同名，但却不能和公有变量同名。例如：Sub 身份证() Dim 身份证 As String 身份证 = a1.TextEnd Sub以上代码中过程与变量同名，但这是允许的。 Dim 身份证 As StringSub 身份证() 身份证 = a1.TextEnd 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 statementsEnd Sub其中各参数的详细功能如表6-1所示。 表6-1Sub语句参数详解参数部分功能解释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 123End SubPrivate 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 SubEnd SubSub B() x = 888 EndEnd SubSub C() MsgBox xEnd 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 SubSub B() Exit Sub End SubSub C() EndEnd 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.AutoFitEnd 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.AutoFitEnd Sub以上代码在借助冒号将前四行代码缩至两行，但执行过程仍然为四步。对于同行中有多句代码时，按从左向右的顺序执行。那么读者一定可以想到，使用冒号和不使用冒号的执行结果岂不是完全一致？仅仅改变了行数？答案是“有时一致，有时不一致”。如果以上的代码按如下方式编写，那么通过冒号改变行数后执行结果完全一致：Sub 设置A1单元格() Range(A1) = 中华人民共和国 Range(A1).Interior.Color = 65535 Range(A1).Font.ColorIndex = 3 Range(A1).Font.Size = 20 Range(A1).EntireColumn.AutoFitEnd Sub而在下面的情况中，使用冒号后却可以得到完全不同的结果：Sub 判断是否及格1() IF B2 = 60 Then C3 = 及格: Exit Sub IF B3 = 60 Then C3 = 及格End SubSub 判断是否及格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可以在代码中设置一个或者多个标签，然后让程序在满足某条件时跳转到标签处，从而改变过程执行流程。标签的规则是：u 可以是标点符号以外的字符组合u 以冒号（:）结尾u 与大小写无关u 必须位于一行的最左端u 配合GoTo使用例如，建立一个名为“总表”的工作表，代码如下：Sub 新建总表() For i = 1 To Sheets.Count IF Sheets(i).Name = 总表 Then GoTo err Next i Sheets.Add ActiveSheet.Name = 总表 Enderr: 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子过程主要有两种方式。u Call语句Call语句的功能是将一个过程的控制权转移到另一个过程。它的语法为：Call name argumentlist，即Call 过程名 参数。其中Call是可选的，即在其他过程调用过程一时可以有以下两种形式：Sub 过程一() MsgBox 你好！End SubPrivate Sub 过程二() 过程一End SubPrivate Sub 过程三() Call 过程一End Sub过程二和过程三都是合法的过程调用。u 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 SubSub 终止() Application.OnTime Now() + TimeValue(00:00:01), 时间, , falseEnd 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.1Function过程的特点Function过程的功能较Sub过程的应用范围稍小，Function过程仅仅用于返回一个值或者多个数的组合，即数组，而Sub过程既可以返回值，还可以对引用的对象进行修改。例如，引用单元格A1的值后对单元格A1设置新的格式，或者修改工作表名称等。Function可以获取工作表名称，但无法修改工作表的名称。Function过程可以不使用参数，类似于工作表函数Rand和Now等，但绝大部分函数是需要一个参数或者多个参数的，最多时可达255个参数。6.3.2Function的语法解析Function的语法如下：Public | Private | Friend Static Function name (arglist) As type statements name = expression Exit Function statements name = expressionEnd FunctionFunction语句的各参数详解如表6-2所示。表6-2Function语句参数详解参数部分功能解释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 FunctionSub 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 FunctionSub 测试() MsgBox 递归(1000)End Sub将代码录入到模块中后，执行过程“测试”，立即弹出错误提示“溢出堆栈空间”。为了避免递归造成的错误，甚至程序崩溃，尽量不要调用自身，开发函数、插件时多方面查核是否可能造成循环引用、递归现象。当然，有目的、有条件的递归是可以给工作带来便利的。另外，谈到函数就不能不说它的“刷新”性能，即在工作表中使用函数时，当在其他区域的数据更新时，当前单元格的函数是否重新运算，专业术语称之为“易失性”。用户定义的函数是否有易失性可以使用以下语句来控制：Application.Volatile 该语句的作用是无论何时在工作表的任意单元格中进行计算，函数都必须重新进行计算。即工作表刷新时调用函数再运算一次，从而实现数据更新，使公式结果同步。6.4关于过程的参数Sub过程和Function过程都可以使用参数。有参数的过程相对于无参数的过程更具灵活性，相当于给了用户更多自定义的空间。6.4.1Sub过程的参数及应用Sub过程的语法是：Private | Public | Friend Static Sub name (arglist) statements Exit Sub statementsEnd Sub其中“(arglist)”即表示它支持可选的参数，可以不用参数，也可以使用参数；可以使用一个参数，也可以使用多个参数。其中参数(arglist)的具体语法如下：Optional ByVal | ByRef ParamArray varname( ) As type = defaultvalue表6-3 Sub过程参数详解部分功能详解Optional可选的。表示参数不是必需的关键字。如果使用了该选项，则 arglist 中的后续参数都必须是可选的，而且必须都使用 Optional 关键字声明。如果使用了 ParamArray，则任何参数都不能使用 OptionalByVal可选的。表示该参数按值传递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 SubPrivate 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 SubOK: IF 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 SubSub 确认权限一() 手工指定姓名 Call 姓名(Application.InputBox(请输入您的姓名, 确认权限, , , , , , 2)End SubSub 确认权限二() 以当前表A1的值进行判断 Call 姓名(ActiveSheet.Range(A1)End SubSub 确认权限三() 以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 = 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”后将弹出“当前范围内的声明重复”的编译错误。即使再修改为以下方式仍然报错：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节对自定义函数的基础知识做了详解，本节则进行实例