如何修改Excel UDF使其可处理单个单元格或单元格数组？

背景 #

我编写了以下UDF用于检查单元格内容是否符合数据验证规则（例如，若单元格数据验证设为A、B、C的列表，当单元格内容为“A”“B”或“C”时返回TRUE，否则返回FALSE）：

'Checks whether the contents of a given cell meets the data validation rules
Function IsValid(Cell) As Variant
    IsValid = Cell.Validation.Value
End Function

但我希望将此UDF用于驱动FILTER函数（该函数需要值数组和布尔值数组作为参数）。

问题：VBA中的重载/参数集？ #

是否可以修改此UDF，使其同时支持：

• 接收Cell As Variant并返回单个True/False值
• 接收Cell As Variant()并返回True/False值数组

解决方案 #

当然可以实现，VBA虽不支持传统函数重载，但可以通过判断输入参数的类型和维度，分别处理单单元格和单元格数组的情况。修改后的代码如下：

'检查指定单元格或单元格区域内容是否符合数据验证规则
Function IsValid(Cell As Variant) As Variant
    Dim resultArr() As Boolean
    Dim rng As Range
    Dim cell As Range
    Dim i As Long, row As Long, col As Long
    
    '判断输入是否为单元格区域
    If TypeName(Cell) = "Range" Then
        '单个单元格的情况
        If Cell.Cells.Count = 1 Then
            On Error Resume Next
            IsValid = Cell.Validation.Value
            On Error GoTo 0
        '多单元格区域的情况
        Else
            ReDim resultArr(1 To Cell.Rows.Count, 1 To Cell.Columns.Count)
            For Each cell In Cell
                On Error Resume Next
                resultArr(cell.Row - Cell.Row + 1, cell.Column - Cell.Column + 1) = cell.Validation.Value
                '无数据验证的单元格默认返回False
                If Err.Number <> 0 Then
                    resultArr(cell.Row - Cell.Row + 1, cell.Column - Cell.Column + 1) = False
                    Err.Clear
                End If
                On Error GoTo 0
            Next cell
            IsValid = resultArr
        End If
    '处理数组形式的输入
    ElseIf IsArray(Cell) Then
        ReDim resultArr(LBound(Cell, 1) To UBound(Cell, 1), LBound(Cell, 2) To UBound(Cell, 2))
        For row = LBound(Cell, 1) To UBound(Cell, 1)
            For col = LBound(Cell, 2) To UBound(Cell, 2)
                If TypeName(Cell(row, col)) = "Range" Then
                    On Error Resume Next
                    resultArr(row, col) = Cell(row, col).Validation.Value
                    If Err.Number <> 0 Then
                        resultArr(row, col) = False
                        Err.Clear
                    End If
                    On Error GoTo 0
                Else
                    '非单元格对象默认返回False
                    resultArr(row, col) = False
                End If
            Next col
        Next row
        IsValid = resultArr
    '处理单值或非单元格输入
    Else
        On Error Resume Next
        IsValid = IIf(TypeName(Cell) = "Range", Cell.Validation.Value, False)
        On Error GoTo 0
    End If
End Function

功能说明 #

• 传入单个单元格时，返回单个布尔值，和原函数逻辑一致；
• 传入单元格区域（比如A1:A10）时，返回对应大小的布尔值数组，可直接用于FILTER函数；
• 增加错误处理：无数据验证的单元格默认返回False，避免函数报错；
• 兼容数组形式的输入（比如其他函数返回的单元格数组）。

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