vba - 在 UDF 中处理可选参数的最佳方法是什么？

Question

我有一个类似的功能

Public Function func(ByRef mandatory_arg1 As Range, ByRef mandatory_arg2 As Range, _
                     Optional ByRef optional_Arg1 As Range, Optional ByRef optional_arg2 As Range, _
                     Optional ByRef optional_arg3 As Range, Optional ByRef optional_arg4 As Range, _
                     Optional ByRef optional_arg5 As Range, Optional ByRef optional_arg6 As Range) As Double
    func = WorksheetFunction.SumIfs(mandatory_arg1, ...) / WorksheetFunction.SumIfs(mandatory_arg2, ...)
End Function

处理缺少参数的情况的最佳方法是什么？正在使用类似于的 If-Else 结构

if IsMissing(optional_Arg1) or IsMissing(optional_Arg2) Then
    ' ...
EndIf

唯一的办法？或者会WorksheetFunction.SumIfs(...)忽略Nothing?

标签： vbaexceloptional-parameters

Answer 1

使用 ParamArray 并循环遍历传递的范围。

Public Function func(ByRef mandatory_arg1 As Range, ByRef mandatory_arg2 As Range, _
                     ParamArray rngs()) As Double
    dim i as long

    If IsMissing(rngs) Then
        'no optional ranges
    End If


    For i = LBound(rngs) To UBound(rngs)
        'process rngs(i)
    Next i

end function