Best way to handle optional parameters in UDF?

Question

I have a function similar to this

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

What is the best way to handle the cases where arguments are missing? Is using an If-Else structure similar to

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

the only way? Or will WorksheetFunction.SumIfs(...) ignore arguments of Nothing?

You can use IsMissing only with Variant, not with Range, but you can use and check for default values. — Vincent G, Aug 07 '18 at 14:15
You should look at Paramarray http://www.tushar-mehta.com/publish_train/xl_vba_cases/1005%20ParamArray.shtml — SJR, Aug 07 '18 at 14:15
Very hard to tell without knowing what your real function's purpose, and likely opinion-based on this site. However, if you can share the real code, [codereview.se] can probably help. — Mathieu Guindon, Aug 07 '18 at 14:28
You might be interested in how I handled optional range/criteria pairs in a [TEXTJOINIFS UDF](https://stackoverflow.com/questions/50716550/textjoin-for-xl2010-xl2013-with-criteria/50719050?s=1|86.6482#50719050). — , Aug 07 '18 at 14:53

score 1 · Answer 1 · 2018-08-07T14:33:43.367

1

Use a ParamArray and cycle through the ranges passed across.

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

edited Aug 07 '18 at 14:33

answered Aug 07 '18 at 14:28

1

That works for most cases where the UDF does its own thing, but falls apart when the optional parameters are then passed as optional arguments to another function, as seems to be the case for the OP. IMO that question would be a better fit on [codereview.se]... **if** it included the real, actual, complete code. – Mathieu Guindon Aug 07 '18 at 14:36

Best way to handle optional parameters in UDF?

1 Answers1