13 自定义函数和模块¶

一、什么是 VBA 自定义函数¶

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

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

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

2.1 简单自定义函数示例¶

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

Function sName()
    Application.Volatile
    sName = ActiveSheet.Name
End Function

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

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

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

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

1
2
3

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

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

1 2	`=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 If
End 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 result
End Sub

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

1	`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 = vbWhite
End Sub

' 数据模块主要处理和数据相关的内容
Sub 数据模块()
    Range("e2") = "=sum(b2:d2)"
    Range("e2").AutoFill Range("e2:e8"), xlFillValues
End Sub

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

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

Sub 主模块()
    a = 1
    b = 2
    求和 a, b
    MsgBox "主模块：" & a + b
End Sub

Sub 求和(ByVal a, ByVal b)
    a = 3
    b = 4
    MsgBox "子模块：" & a + b
End 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 Sub

Function func()
    Dim arr()
    arr = Array(1, 2, 3)
    func = arr
End Function

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

'创建字典返回
Sub test()
    Set d1 = dic()
    MsgBox d1.Item("A")
End Sub

Function dic()
    Set d = CreateObject("scripting.dictionary")
    d.Add "A", "你好"
    Set dic = d '用set创建对象返回
End Function

本期系统给大家介绍了VBA中关于函数和模块的调用，以及自定义函数的方式，希望能帮助到大家，下期再见！