VBA自定义SPECIALDAYS函数返回#VALUE!错误的排查与解决

错误原因 #

• numDays 计算逻辑错误：直接将VBA日期变量拼接进Evaluate的字符串时，日期会被转换为VBA内部数值序列，工作表函数Eomonth无法识别该格式，导致计算失败返回#VALUE!
• 潜在引用错误：定义myArea时未指定所属工作表，当FirstCell不在活动工作表时会出现范围引用偏差
• 冗余逻辑：无需通过Evaluate调用工作表函数，VBA原生语法即可实现当月天数计算，稳定性和性能更优

修正后完整代码 #

Function SPECIALDAYS(FirstCell As Range, myDate As Date) As Long
    Dim myCell As Range
    Dim myArea As Range
    Dim numDays As Integer

    ' 原生VBA语法计算当月天数
    numDays = Day(DateSerial(Year(myDate), Month(myDate) + 1, 0))
    ' 基于所属工作表限定统计范围，避免跨表引用错误
    Set myArea = FirstCell.Parent.Range(FirstCell, FirstCell.Offset(0, numDays - 1))
    
    ' 保留原有填充色统计逻辑
    For Each myCell In myArea
        If myCell.Interior.ColorIndex <> -4142 Then
            SPECIALDAYS = SPECIALDAYS + 1
        End If
    Next myCell
End Function

使用说明 #

在工作表单元格直接输入公式调用即可，示例：
=SPECIALDAYS(B2, "2022-4-1")

• 第一个参数选择统计起始单元格
• 第二个参数输入对应年月的任意日期即可
• 备注：修改单元格填充色后不会自动触发重算，按F9键可手动刷新结果

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