VBA学习笔记七：自定义函数

gogoel

已于 2024-08-29 11:09:18 修改

阅读量2.2k

点赞数 21

分类专栏： VBA学习笔记文章标签： excel 学习

于 2024-07-22 10:30:19 首次发布

本文链接：https://blog.youkuaiyun.com/gogoel/article/details/140602641

版权

VBA学习笔记专栏收录该内容

7 篇文章

订阅专栏

第7部分自定义函数

7.1 自定义函数概述

7.1.1 什么是自定义函数

定义：用户定义的函数，用于执行特定任务或计算
用途：简化复杂计算、提高代码重用性、增强代码可读性
区别：与内置函数不同，自定义函数由用户定义和控制，但自定义函数可以和Excel内置函数嵌套使用

7.1.2 自定义函数的创建

位置：自定义函数必须放在标准模块中，不能放在工作表模块或工作簿模块中
步骤：打开 VBA 编辑器、插入模块、编写函数代码

语法：

Function 自定义函数名称(参数) As 返回值类型
    ' 代码
    自定义函数名称 = 返回值
End Function

示例：自定义一个求和函数

Function AddNumbers(a As Double, b As Double) As Double
    AddNumbers = a + b
End Function

7.1.3 参数的使用

形参与实参：函数声明中的参数为形参，调用函数时传入的参数为实参
可选参数：使用 `Optional` 关键字
默认值：给可选参数设置默认值
参数类型声明：指定参数的数据类型

7.1.4 返回值

定义：函数执行后返回的结果，这个结果可以被另一个函数或公式使用
声明类型：在函数声明时指定（可选），可以确保函数返回的结果符合预期的数据类型，如果没有指定，默认为 Variant 类型
使用：通过将函数名赋值的方式来实现

7.1.5 通过示例介绍参数和返回值

示例：求和函数

' 这是一个求和函数，演示了返回值和参数的使用
Function AddNumbers(a As Double, Optional b As Double = 0) As Double
    ' 参数a 和 b 是形参
    ' 参数类型声明为 Double，表示它们是双精度浮点数
    
    ' 如果调用时不传递参数b，则b使用默认值0
    AddNumbers = a + b  ' AddNumbers 是函数的返回值

End Function

Sub TestAddNumbers()
    Dim result As Double
    ' 调用 AddNumbers 函数时传入 5 和 10 作为实参
    result = AddNumbers(5, 10)
    MsgBox "5 + 10 = " & result  ' 显示结果，result 是返回值
    
    ' 调用 AddNumbers 函数时只传入 5，b 使用默认值 0
    result = AddNumbers(5)
    MsgBox "5 + 0 = " & result  ' 显示结果，result 是返回值
End Sub

形参：a 和 b 是形参，在函数声明中定义，用于接收调用时传入的实际值
可选参数：Optional b As Double = 0 表示 b 是一个可选参数，默认等于 0
返回值：result = AddNumbers(5, 10) 中，5 和 10 是传递给 AddNumbers 函数的实参，result 是函数的返回值

7.2 自定义函数内置

说明：自定义函数内置后可以像 Excel 的函数一样使用，可以简化重复任务，执行复杂的计算
步骤：
- 1. 先编写自定义函数
- 2. 文件另存为 Excel 加载宏：保存文件时选择 `Excel 加载项 (*.xlam)` 格式
- 3. 加载自定义函数：打开任意Excel工作簿 -> 点击 `开发工具` -> `Excel 加载项` -> `浏览`，选择保存的加载项文件。加载后，自定义函数将随 Excel 启动，并可以在任何工作簿中使用
示例：自定义计算两个数乘积的函数，并内置在本机的Excel中
```
Function MultiplyNumbers(a As Double, b As Double) As Double
    MultiplyNumbers = a * b
End Function
```
在Excel中可以直接使用，例如计算5和6的乘积

7.3 自定义函数代码调试

解释：调试自定义函数时，如果函数有参数，不能直接使用F8逐步运行。可以通过设置断点或编写测试代码来调试
方法1：设置断点，运行工作表中的函数，VBA代码会在断点处暂停，可以逐步调试

方法2：编写测试宏来调用函数并调试

Sub TestFunction()
    Dim result As Double
    result = AddNumbers(5, 10)
    Debug.Print result
End Sub

Function AddNumbers(a As Double, b As Double) As Double
    AddNumbers = a + b
End Function

7.4 使用案例

判断日期是否为节假日

要求：第一列生成指定日期范围的所有日期，第二列判断每个日期是否为工作日或节假日，然后让用户选择要写入的位置，最后将内容写入到指定的单元格区域中

代码：

Sub GenerateDatesAndCheckWorkdays()
    Dim startDate As Date, endDate As Date
    Dim currentDate As Date
    Dim dateRange() As Variant
    Dim i As Long
    Dim ws As Worksheet
    Dim targetRange As Range

    ' 指定日期范围
    startDate = "2024-08-01"
    endDate = "2024-08-10"

    ' 初始化日期数组
    ReDim dateRange(1 To (endDate - startDate + 1), 1 To 2)

    ' 循环生成日期，并判断是否为工作日或节假日
    currentDate = startDate
    For i = 1 To UBound(dateRange)
        dateRange(i, 1) = currentDate
        ' 调用`CheckWorkDay`函数，检查某个日期是否为工作日
        dateRange(i, 2) = CheckWorkDay(currentDate)
        currentDate = currentDate + 1
    Next i

    ' 让用户选择指定单元格的左上角位置作为数据写入区域
    On Error Resume Next
    Set targetRange = Application.InputBox("请选择目标单元格的左上角位置", Type:=8)
    On Error GoTo 0
    
    If targetRange Is Nothing Then
        MsgBox "操作已取消", vbExclamation
        Exit Sub
    End If

    ' 将数组内容写入指定区域
    Set targetRange = targetRange.Resize(UBound(dateRange, 1), UBound(dateRange, 2))
    targetRange.Value = dateRange

    MsgBox "日期和工作日状态已成功写入", vbInformation
End Sub

Function CheckWorkDay(dateValue As Date) As String
    Dim response As String
    ' 调用 `GetWebData` 函数发送HTTP请求，返回服务器响应的内容
    response = GetWebData("http://tool.bitefu.net/jiari/?d=" & Format(dateValue, "yyyymmdd"))
    
    ' 判断返回的结果是否为0
    If response = "0" Then
        CheckWorkDay = "工作日"
    Else
        CheckWorkDay = "节假日"
    End If
End Function

Function GetWebData(url As String) As String
    ' 声明XMLHTTP对象用于发送HTTP请求
    Dim httpRequest As Object
    Set httpRequest = CreateObject("MSXML2.XMLHTTP.6.0")
    
    ' 初始化HTTP请求
    httpRequest.Open "GET", url, False
    
    ' 发送请求
    httpRequest.send
    
    ' 将服务器响应的内容返回
    GetWebData = httpRequest.responseText
End Function

代码解释：
- `GenerateDatesAndCheckWorkdays` 子过程：生成指定日期范围的所有日期，并判断每个日期是否为工作日或节假日
- `CheckWorkDay` 函数：通过调用 `GetWebData` 函数发送HTTP请求，检查某个日期是否为工作日，如果返回值为 `"0"`，表示是工作日；否则为节假日
- `GetWebData` 函数：发送HTTP GET请求，获取服务器返回的数据，返回服务器响应的内容
特别说明：
- 函数中使用了 VBA 的 `XMLHTTP` 对象与服务器进行通信，以获取指定URL的响应数据，关于此对象的说明可以参考这个博主写的https://www.cnblogs.com/caidongji/p/16905519.htmlhttps://www.cnblogs.com/caidongji/p/16905519.html