一文讲清自定义函数和过程

一、什么是 VBA 自定义函数

虽然说EXCEL已经有100多个函数了，但是往往在有些情境下系统自带的函数解决不了问题，因为例如SUM，COUNT这些函数只能对数值处理，比如要对相同底色的单元格求和，常规的函数就无法实现，因此我们需要自定义函数。

在 Excel VBA 中，自定义函数 是由开发者自己编写的函数，它与 Excel 内置函数（如 SUM、VLOOKUP 等）类似，可以接收参数并返回计算结果，用于满足内置函数无法实现的需求。

二、通过EXCEL单元格进行调用

2.1 简单自定义函数示例

比如我们可以在 VBA 编辑器中（按 Alt + F11 打开），插入一个 模块 (Module) ，然后写一个最简单的自定义函数：这个函数实现返回当前sheet的名称：

Function sName()    Application.Volatile    sName = ActiveSheet.NameEnd Function

注意：函数名即为返回值容器，把最终结果赋给函数名称即可，通过定义上述函数，我们可以在单元格区域内得到当前sheet的名称，这里的Application.Volatile主要是为了防止自定义函数的易失性，加上这句话之后，这个函数可以及时响应表格变化，比如sheet的名字修改后，此函数返回的结果立马变化。

但是上面的函数是没有参数，如果需要添加参数，则可以使用下面的格式：

Function 函数名(参数列表) As 返回类型    ' 函数体，负责计算逻辑    函数名 = 返回值       ' 将值赋给函数名来“返回”End Function

我们给一个具体的功能，例如对两数求和：

Function AddTwoNumbers(x As Double, y As Double) As Double    AddTwoNumbers = x + yEnd Function

→ 这意味着在 Excel 单元格中你可以这样调用，Excel 能自动识别自定义函数

=AddTwoNumbers(3, 5)   '返回8=AddTwoNumbers(B2,C2) '如果B2和C2单元格中是具体的数字，也可以调用

2.2 函数参数细节

1) 必须参数：在 () 中声明的参数（如上例的 x、y），调用时必须提供。

2) 可选参数：可以用 Optional 声明，这个参数不填的时候为默认值

Function GetTextText(rng As Range, Optional upperCase As Boolean = False) As String    If upperCase Then        GetTextText = UCase(rng.Value) '返回大写的值    Else        GetTextText = rng.Value    End IfEnd Function

在单元格里：=GetTextText(A1,TRUE)，会得到大写的值，而如果不传可选参数，它会用默认值。

3) ParamArray参数：经常在需要选择多个区域的时候，使用此参数，例如我们对多个区域进行颜色求和

'按颜色统计单元格Function colorSum(ck As Range, ParamArray arr())    '这里arr是一个多个区域的数组    Dim total As Long    For Each s In arr '遍历数组中的每个区域        For Each ss In s '遍历每个区域中的每个单元格            '判断单元格的颜色是否与指定颜色相同            If ss.Interior.Color = ck.Interior.Color Then                    total = total + ss.Value '如果相同，累加单元格的值            End If        Next ss    Next s    colorSum = total '返回累加的结果End Function

在表格中，我们就可以这样调用 =colorSum(F2,$B$1:$B$10,$D$1:$D$10)，ParamArray 会自动识别，除了之前的参数，后面还有其他什么参数，因此后面的两个区域就被装入 arr 中，更加方便进行遍历。

三、在 VBA 代码里调用

3.1 Function的调用

比如还是之前我们讲的AddTwoNumbers这个函数，在其他模块中我们可以直接调用，并将返回的值存在result中

Sub Test()    Dim result As Double    result = AddTwoNumbers(2, 4)    MsgBox resultEnd Sub

同样我们也可以在VBA 中使用 Call 来调用函数，但注意：如果使用 Call 调用一个有返回值的 Function，则返回值会被丢弃，这是因为在 VBA 里使用 Call 时，函数返回值不会被捕获，也就是说：

Call AddTwoNumbers(1, 2)   ' 返回值不会被保存

3.2 子sub的调用

简单理解： Sub（子过程） 做事，但不返回值； Function（函数） 做事并返回结果。

为了直观理解模块的调用，我们用以下这段代码解释，两个子模块分别干两件事，然后主模块，即功能模块负责调用即可

Sub 功能模块()    数据模块 ' 调用数据模块执行数据模块的功能    格式模块 ' 调用格式模块执行格式模块的功能End Sub' 格式模块主要处理和格式相关的内容Sub 格式模块()    Range("a1:e8").Borders.LineStyle = xlContinuous    Range("a1:e1").Interior.Color = vbRed    Range("a1:e1").Font.Color = vbWhiteEnd Sub' 数据模块主要处理和数据相关的内容Sub 数据模块()    Range("e2") = "=sum(b2:d2)"    Range("e2").AutoFill Range("e2:e8"), xlFillValuesEnd Sub

通过主sub对子模块的调用，让整个代码逻辑清晰，模块分明，便于维护，另外需要注意的是，模块间参数的传递，主要有byval和byref两种：

• bybal：相当于把参数拷贝一份，子模块中对其的修改不会影响初始值
• byref：相当于直接把参数对象传进去，子模块中对其的修改会影响参数本身

Sub 主模块()    a = 1    b = 2    求和 a, b    MsgBox "主模块：" & a + bEnd SubSub 求和(ByVal a, ByVal b)    a = 3    b = 4    MsgBox "子模块：" & a + bEnd Sub

如上，参数的传递方式为ByVal，则子模块对a 和 b 的修改不影响主模块，因此主模块输出：主模块：3，而如果将传递方式修改为byref，则子模块中将a 和 b 改成3和4之后，主模块输出也是主模块：7，因为这种传递会修改参数本身

最后需要补充说明一下，什么时候用call，什么时候不用：

• 如果你需要返回值，保存到某个变量中，一般不用，因为Call只是执行一个过程，不返回结果
• 如果你只是执行一个过程，调用一个模块，可以写Call，这样明确你调用的是子模块或者函数

写Call的时候，一定要写上括号，例如Call 求和(a, b)，而不写的时候，可以不用括号，如求和 a, b

3.3 数组和字典的传参调用

数组的传递和正常值的传递基本一致，直接将数组赋值给函数func即可，func就可以作为一个数组返回

'传递数组值Sub testArr()    b = func()    MsgBox Join(b)End SubFunction func()    Dim arr()    arr = Array(1, 2, 3)    func = arrEnd Function

而对于字典而言，就不可以直接赋值了，而是通过set定义一个对象返回，在返回值处，也用set接收，如此传递

'创建字典返回Sub test()    Set d1 = dic()    MsgBox d1.Item("A")End SubFunction dic()    Set d = CreateObject("scripting.dictionary")    d.Add "A", "你好"    Set dic = d '用set创建对象返回End Function

以上就是本期的所有内容啦，如果你也是EXCEL爱好者，经常需要处理表格数据，欢迎关注，给你送一份我摸爬滚打多年的实战手册！