Excel中VBA自定义函数运行触发#ARG!参数错误的原因排查

• SpecialCells无匹配时的硬报错
  代码使用rng.SpecialCells(xlCellTypeConstants, 2)筛选范围内的文本常量单元格，该方法的特性是目标范围内无符合条件的单元格时，会直接抛出运行时错误1004，这个错误在UDF环境中会被Excel直接转为工作表错误值，部分版本显示为#ARG!。初期运行正常是因为初始表格里存在带LE标识的文本常量，后续如果编辑过程中删除了所有文本常量、把原文本改成了公式、或者加筛选把符合条件的行全部隐藏，就会直接触发该错误。
• 变量未显式声明
  循环里用到的遍历变量cell没有定义类型，属于隐式变体，既容易出现意外的类型转换问题，在开启强制变量声明的环境下会直接报错。
• 缺失的值类型校验
  1. 累加用的hrs被声明为Date类型，虽然Date的数值存储格式和时长序列值兼容，但Date有固定的数值范围限制，一旦累加的时长超过类型上限就会触发不匹配错误；
  2. 直接累加cell.Offset(1).Value时，如果偏移位置的单元格是空值、文本内容、错误值，会直接触发类型转换失败，最终返回错误值；
  3. 没有校验传入的rng参数有效性，如果传入多区域联合范围，SpecialCells的处理逻辑会完全不符合预期。
• UDF场景下SpecialCells稳定性不足
  SpecialCells的匹配结果依赖单元格的实时状态（是否为常量、是否隐藏），表格触发自动重算时，只要单元格的常量属性、可见性发生变化，就会导致函数计算逻辑异常，不如直接遍历单元格的逻辑稳定。

Option Explicit
Public Function total(rng As Range, ByVal sgn As String) As Double
    Dim hrs As Double
    Dim cell As Range
    Dim offsetVal As Variant
    
    hrs = 0
    ' 遍历范围内所有单元格，替代易触发报错的SpecialCells
    For Each cell In rng.Cells
        ' 匹配非公式、值等于指定标识的单元格
        If cell.HasFormula = False And CStr(cell.Value) = sgn Then
            offsetVal = cell.Offset(1).Value
            ' 仅当偏移单元格为有效数值时才累加
            If IsNumeric(offsetVal) Then
                ' 小时值转换为Excel标准的时长序列值
                hrs = hrs + CDbl(offsetVal) / 24
            End If
        End If
    Next cell
    total = hrs
End Function

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