Excel UDF在VBA子程序中正常运行，工作表调用返回#Value的修复方案

问题说明 #

我写了一个VBA用户定义函数（UDF），功能是把输入的数值数组转换成日期范围字符串。这个函数在VBA子程序里调用完全正常，但直接在Excel工作表单元格里调用时，会返回#Value错误。相关代码如下：

Function GetWorkingDays(WorkingDaysArr() As Variant, MonthYear As String) As String

    Dim Result As String, WorkingDaysCount, ConsecutiveDays, DayStart As Integer
    Result = ""
    WorkingDaysCount = UBound(WorkingDaysArr) - LBound(WorkingDaysArr)
    For i = 0 To WorkingDaysCount Step 1
        If Not IsEmpty(WorkingDaysArr(i)) Then
            ConsecutiveDays = ConsecutiveDays + 1
            If DayStart = 0 Then
                DayStart = i + 1
            End If
        Else
            If ConsecutiveDays > 0 Then
                Dim DayStartStr, DayEndStr As String
                DayStartStr = ConcatDateString(CStr(DayStart), MonthYear)
                DayEndStr = ConcatDateString(CStr(i), MonthYear)
                If DayStartStr <> DayEndStr Then
                    Result = Result + DayStartStr + "-" + DayEndStr + ", "
                Else
                    Result = Result + DayStartStr + ", "
                End If
            End If
            DayStart = 0
            ConsecutiveDays = 0
        End If
    Next i
    Result = Left(Result, Len(Result) - 2)
    GetWorkingDays = Result
    
End Function

Function ConcatDateString(day As String, MonthYear As String) As String

    ConcatDateString = day + "." + MonthYear
    If Len(day) = 1 Then
        ConcatDateString = "0" + ConcatDateString
    End If
    
End Function

Sub test()
Dim varData(31) As Variant
varData(0) = "val"
varData(1) = "val"
varData(2) = "val"
varData(3) = "val"
varData(4) = "val"
varData(27) = "val"
varData(28) = "val"
varData(30) = "val"
MsgBox GetWorkingDays(varData, "06.23")
End Sub

错误原因 #

1. 参数类型不兼容：UDF第一个参数声明为固定大小的Variant数组，但工作表调用时，Excel传递的是Range对象，不是VBA数组，直接导致类型不匹配，触发#Value错误。
2. 遗漏最后一段连续日期：原循环结束后，如果最后几个元素是非空的，这段日期范围不会被添加到结果里。
3. 变量声明不规范：WorkingDaysCount、ConsecutiveDays、i这些变量没显式声明类型，VBA会默认设为Variant，可能引发隐式转换错误。
4. 字符串拼接风险：用+拼接字符串，要是遇到数值类型会自动做加法运算，而非拼接，容易出错。

修复后的完整代码 #

Function GetWorkingDays(WorkingDaysRange As Range, MonthYear As String) As String
    Dim WorkingDaysArr As Variant
    ' 将工作表Range转换为二维数组处理
    WorkingDaysArr = WorkingDaysRange.Value
    
    Dim Result As String
    Dim WorkingDaysCount As Integer, ConsecutiveDays As Integer, DayStart As Integer
    Dim i As Integer
    Result = ""
    ConsecutiveDays = 0
    DayStart = 0
    
    ' 遍历二维数组（Excel Range转的数组索引从1开始）
    For i = LBound(WorkingDaysArr, 1) To UBound(WorkingDaysArr, 1)
        If Not IsEmpty(WorkingDaysArr(i, 1)) Then
            ConsecutiveDays = ConsecutiveDays + 1
            If DayStart = 0 Then
                DayStart = i
            End If
        Else
            If ConsecutiveDays > 0 Then
                ' 拼接日期范围，用&避免数值运算风险
                Result = Result & GetDateString(DayStart, MonthYear) & "-" & GetDateString(i - 1, MonthYear) & ", "
                DayStart = 0
                ConsecutiveDays = 0
            End If
        End If
    Next i
    
    ' 处理循环结束后剩下的连续日期
    If ConsecutiveDays > 0 Then
        Result = Result & GetDateString(DayStart, MonthYear) & "-" & GetDateString(UBound(WorkingDaysArr, 1), MonthYear) & ", "
    End If
    
    ' 移除末尾多余的逗号和空格
    If Len(Result) > 2 Then
        Result = Left(Result, Len(Result) - 2)
    End If
    
    GetWorkingDays = Result
End Function

Function GetDateString(day As Integer, MonthYear As String) As String
    ' 用Format函数统一生成两位数字的日期格式
    GetDateString = Format(day, "00") & "." & MonthYear
End Function

Sub test()
    ' 用二维数组模拟工作表Range结构
    Dim varData(1 To 31, 1 To 1) As Variant
    varData(1, 1) = "val"
    varData(2, 1) = "val"
    varData(3, 1) = "val"
    varData(4, 1) = "val"
    varData(5, 1) = "val"
    varData(28, 1) = "val"
    varData(29, 1) = "val"
    varData(31, 1) = "val"
    ' 模拟工作表调用方式
    MsgBox GetWorkingDays(Sheet1.Range("A1:A31"), "06.23")
End Sub

核心修复点 #

• 适配工作表参数：把第一个参数改为Range类型，函数内部自动将Range转成二维数组，兼容工作表调用场景。
• 补充收尾逻辑：循环结束后检查是否还有未处理的连续日期，避免遗漏最后一段范围。
• 规范变量类型：所有变量显式声明类型，消除隐式转换带来的错误。
• 优化字符串处理：用&替代+做字符串拼接，用Format函数更可靠地生成两位数字的日期格式。

工作表调用方法 #

在Excel单元格中直接输入公式，例如：
=GetWorkingDays(A1:A31, "06.23")
其中A1:A31是存放日期标记的单元格范围，"06.23"是目标年月（格式为MM.YY）。

内容的提问来源于stack exchange，提问作者Coilzed