Excel VBA自定义函数SumByColor返回#VALUE!错误问题咨询

• Find方法未做异常处理：当Range("Carto!BA2:FA2").Find(what:=DateBefore)没有匹配到对应日期时，返回空对象Nothing，此时调用.Column属性会直接触发运行时错误，自定义函数（UDF）出错就会返回#VALUE!。硬编码时刚好匹配到对应值，所以可以正常运行。
• 单元格引用未限定工作表：代码中Split(Cells(2, ColumnNumber).Address, "$")(1)里的Cells没有指定所属工作表，默认取调用函数时的当前活动工作表，如果当前活动工作表不是Carto，就会取错单元格地址，后续构造的RangeBefore区域无效，引发错误。
• 未声明变量类型：代码中Sum、DateBefore、ColumnNumber等变量都没有显式声明类型，隐式声明为Variant类型，一旦赋值过程中出现类型不匹配就会触发错误。
• Find方法参数缺失：Find方法会继承上一次手动查找的参数设置，如果上次使用了部分匹配、区分大小写等设置，就可能导致本次查找明明有匹配值也返回空结果。

Option Explicit
Public Function SumByColor(CellColor As Range) As Long
    ' 校验入参是否为单个单元格
    If CellColor.Cells.Count <> 1 Then
        SumByColor = CVErr(xlErrValue)
        Exit Function
    End If
    
    Dim Sum As Long
    Sum = 0
    
    Dim DateBefore As Date
    ' 显式限定工作表引用，避免活动工作表变更导致取数错误
    DateBefore = ThisWorkbook.Worksheets("Bilan").Range("B2").Value
    
    Dim FindRng As Range
    Dim ColumnNumber As Long
    ' 补全Find方法所有参数，避免继承之前的查找设置
    Set FindRng = ThisWorkbook.Worksheets("Carto").Range("BA2:FA2").Find( _
        What:=DateBefore, _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        MatchCase:=False _
    )
    
    ' 处理未找到匹配值的异常情况
    If FindRng Is Nothing Then
        SumByColor = 0 ' 可根据需求替换为返回CVErr(xlErrNA)
        Exit Function
    End If
    ColumnNumber = FindRng.Column
    
    Dim ColumnLetter As String
    ' 限定Cells所属工作表
    ColumnLetter = Split(ThisWorkbook.Worksheets("Carto").Cells(2, ColumnNumber).Address, "$")(1)
    
    Dim RangeBefore As Range
    Set RangeBefore = ThisWorkbook.Worksheets("Carto").Range(ColumnLetter & "3:" & ColumnLetter & "10000")
    
    Dim rCell As Range
    For Each rCell In RangeBefore
        If rCell.Interior.Color = CellColor.Interior.Color Then
            Sum = Sum + 1
        End If
    Next
    SumByColor = Sum
End Function

• 调用函数时必须传入单个单元格作为参数，比如=SumByColor(A1)，不能传入数值或者多单元格区域。
• 单元格颜色变更不会自动触发公式重算，修改颜色或者日期后按F9即可刷新计算结果。

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