一、基础概念
在 Excel VBA 中，函数主要分为两种类型：
Sub 过程：执行操作但不返回值Function 函数：执行操作并返回结果
基本语法示例
1. Function 函数示例
' 定义一个返回字符串的公共函数
Public Function GetGreeting() As String
GetGreeting = "您好，这是一个VBA函数示例"
End Function
调用方式：
MsgBox GetGreeting()
2. Sub 过程示例
' 定义一个公共过程
Public Sub ShowMessage()
MsgBox "这是一个VBA过程示例"
End Sub
调用方式：
Call ShowMessage()
' 或者简写为
ShowMessage
关键注意事项
作用域控制：
使用 Public 关键字使函数/过程可在整个工作簿中调用使用 Private 关键字限制为仅在当前模块中使用
跨模块调用：
调用同一模块中的函数直接使用函数名调用其他工作表/窗体中的函数需使用完整路径，如：Sheet1.CustomFunction()
参数传递：
使用 ByVal 传递值副本（推荐）使用 ByRef 传递引用（可修改原变量）
二、自定义函数开发
应用场景分析
假设我们有一个包含航班信息的Excel表格：
A列单元格可能包含单行或多行文本多行文本使用换行符 CHAR(10) 分隔需要从文本中提取特定位置的日期、编号和时间信息
函数实现方案
1. 提取并格式化日期 (sDate)
' 功能：从文本第14字符开始提取5字符，格式化为标准日期
' 输入：目标单元格
' 输出：格式化后的日期字符串 "YYYY-MM-DD"
Public Function sDate(ByVal Target As Range) As String
On Error Resume Next ' 错误处理
sDate = Format$(Evaluate("=DATEVALUE(MID(" & Target.Address(False, True) & ",14,5))"), "yyyy-MM-dd")
If Err.Number <> 0 Then sDate = "无效日期"
End Function
2. 提取前导编号 (sNum)
' 功能：提取文本前6个字符
' 输入：目标单元格
' 输出：6位字符的字符串
Public Function sNum(ByVal Target As Range) As String
sNum = Left(Target.Value, 6)
End Function
3. 提取出发时间 (dTime)
' 功能：从第34字符开始提取4字符并格式化为时间
' 输入：目标单元格
' 输出：格式化时间 "HH:MM"
Public Function dTime(ByVal Target As Range) As String
dTime = Format$(Mid(Target.Value, 34, 4), "00:00")
End Function
4. 提取到达时间 (aTime)
' 功能：从第39字符开始提取4字符并格式化为时间
' 输入：目标单元格
' 输出：格式化时间 "HH:MM"
Public Function aTime(ByVal Target As Range) As String
aTime = Format$(Mid(Target.Value, 39, 4), "00:00")
End Function
多行文本处理增强版
' 增强版sDate函数，支持处理多行文本
Public Function sDateEx(ByVal Target As Range) As String
Dim textLines As Variant
Dim result As String
Dim i As Long
' 分割文本行
textLines = Split(Target.Value, vbLf)
' 处理每一行
For i = LBound(textLines) To UBound(textLines)
If Len(Trim(textLines(i))) >= 18 Then ' 确保有足够长度
result = result & Format$(DateValue(Mid(textLines(i), 14, 5)), "yyyy-MM-dd") & vbLf
End If
Next i
' 移除末尾多余换行符
If Len(result) > 0 Then
sDateEx = Left(result, Len(result) - 1)
Else
sDateEx = "无有效数据"
End If
End Function
自定义CalcRatio函数是一个用于计算比例关系的VBA函数，它接受4个可选参数(A1, A2, B1, B2)，根据其中3个已知值计算第4个缺失值。该函数主要用于解决比例关系问题，遵循"A1/A2 = B1/B2"的比例规则。
5. 算法设计
算法特点
参数验证算法：通过遍历检查每个参数的有效性缺失检测算法：统计缺失参数数量并记录位置比例计算算法：基于"A1/A2 = B1/B2"的比例关系推导公式
数据验证流程
将4个参数存储在params数组中使用missingIndex记录缺失参数的位置(0-3)使用missingCount统计缺失参数数量
6.1 参数验证部分
' 参数数组和缺失检查
Dim params(), missingIndex As Long, missingCount As Long, i As Long
params = Array(A1, A2, B1, B2)
For i = 0 To 3
If IsMissing(params(i)) Or IsEmpty(params(i)) Then
missingIndex = i
missingCount = missingCount + 1
ElseIf Not IsNumeric(params(i)) Or params(i) <= 0 Then
CalcRatio = "所有参数必须为大于0的数字!"
Exit Function
End If
Next
6.2 缺失校验部分
' 校验缺失数量
If missingCount <> 1 Then
CalcRatio = IIf(missingCount > 1, "缺失过多参数!", "无缺失参数!")
Exit Function
End If
6.3 计算逻辑部分
' 直接计算（因参数已确保>0，无需额外检查）
Select Case missingIndex
Case 0: CalcRatio = params(1) * params(2) / params(3) ' A1
Case 1: CalcRatio = params(0) * params(3) / params(2) ' A2
Case 2: CalcRatio = params(0) * params(3) / params(1) ' B1
Case 3: CalcRatio = params(1) * params(2) / params(0) ' B2
End Select
7. 函数应用示例
' 计算A1 (已知A2=2, B1=3, B2=6)
Debug.Print CalcRatio(, 2, 3, 6) ' 返回1
' 计算B2 (已知A1=1, A2=2, B1=3)
Debug.Print CalcRatio(1, 2, 3) ' 返回6
' 错误示例 - 多个参数缺失
Debug.Print CalcRatio(1, , 3) ' 返回"缺失过多参数!"
三、实践建议
错误处理：
始终添加错误处理代码（如 On Error 语句）对输入参数进行验证
性能优化：
避免在循环中使用 Evaluate 方法对于大量数据处理，考虑使用数组
代码可读性：
添加清晰的注释使用有意义的变量名保持一致的代码风格
应用示例：
Sub TestCustomFunctions()
Dim testCell As Range
Set testCell = Sheet1.Range("A1")
Debug.Print "航班号: " & sNum(testCell)
Debug.Print "出发日期: " & sDateEx(testCell)
Debug.Print "起飞时间: " & dTime(testCell)
Debug.Print "到达时间: " & aTime(testCell)
End Sub
四、常见问题解答
Q1：为什么我的自定义函数在工作表中不显示结果？
A1：请检查：
是否使用了 Public 关键字函数是否放在标准模块中（非工作表/窗体模块）是否包含必要的错误处理
Q2：如何处理包含特殊字符的文本？
A2：建议添加数据清洗步骤：
Function CleanText(inputText As String) As String
CleanText = Replace(inputText, Chr(160), " ") ' 替换不间断空格
CleanText = Application.WorksheetFunction.Clean(CleanText) ' 移除不可打印字符
End Function
Q3：如何提高多行文本处理的效率？
A3：考虑以下优化方案：
先将整个范围读入数组批量处理所有数据一次性输出结果