VBA - workaround for conditionally calling a function with certain optional parameters?

Question

Consider a function with several optional parameters. E.g.:

Function foo(Optional a, Optional b, Optional c, Optional d)

If I want to call the specific function only with the arguments are not null, or some other test, (because the function raises an error otherwise, unless the argument is missing).

An example would be the Collection class. There are 3 optional parameters to the Add method. Many custom classes wrap this class - such as to create an indexable class Persons of a 'Person' custom class or something like that. A wrapper has to deal with 6 combinations: adding a Key only, adding a Key and specifying Before, adding a Key and specifying After, not adding a Key and specifying Before, not adding a Key and specifying After, no Key and no Before/After. It would get even worse if writing a wrapper to something like Workbook.Save that has a dozen Optional parameters.

I am not aware of an alternative to some tedious construct like:

If a <> Null Then
    If b <> Null Then
        If c <> Null Then
            If d <> Null Then
                foo a, b, c, d
            Else
                foo a, b, c
            End If
        Else
            If d <> Null Then
                foo a, b, d
            Else
                foo a, b
            End If
        End If
    Else
    '... Etc ...

Clearly the number of nested Ifs and general code overhead roughly doubles for each additional optional variable.

In .NET there is the type System.Type.Missing that can be passed in, allowing the expression to be a lot more simple.

Like in C# it can be as clean as:

foo(a ?? Missing, b ?? Missing, c ?? Missing, d ?? Missing);

(this says 'if a is null then pass in Missing (or perhaps behind the scenes it refactors the call itself), a call equivalent to not passing in anything for the optional argument a, and etc)

And if it were implemented, an equivalent could be in VBA using in-line iff ('IIF(boolean, true, false)')

Is there a workaround for VBA that I am missing?

Answer 1

(On edit: I incorporated HarveyFrench's improvement of the code)

You can make it a little less nested:

Function foo(Optional a, Optional b, Optional c, Optional d)
    Dim passed As String
    If Not IsMissing(a) Then passed = "a "
    If Not IsMissing(b) Then passed = passed & "b "
    If Not IsMissing(c) Then passed = passed & "c "
    If Not IsMissing(d) Then passed = passed & "d "
    foo = IIf(Len(passed) = 0, "Nothing ", passed) & "passed"
End Function

Function foo_dispatcher(Optional a, Optional b, Optional c, Optional d)
    Dim caseNum As Long
    caseNum = IIf(IsNull(a) Or IsEmpty(a) Or IsMissing(a), 0, 8)
    caseNum = caseNum + IIf(IsNull(b) Or IsEmpty(b) Or IsMissing(b), 0, 4)
    caseNum = caseNum + IIf(IsNull(c) Or IsEmpty(c) Or IsMissing(c), 0, 2)
    caseNum = caseNum + IIf(IsNull(d) Or IsEmpty(d) Or IsMissing(d), 0, 1)

    Select Case caseNum
        Case 0: foo_dispatcher = foo()
        Case 1: foo_dispatcher = foo(, , , d)
        Case 2: foo_dispatcher = foo(, , c)
        Case 3: foo_dispatcher = foo(, , c, d)
        Case 4: foo_dispatcher = foo(, b)
        Case 5: foo_dispatcher = foo(, b, , d)
        Case 6: foo_dispatcher = foo(, b, c)
        Case 7: foo_dispatcher = foo(, b, c, d)
        Case 8: foo_dispatcher = foo(a)
        Case 9: foo_dispatcher = foo(a, , , d)
        Case 10: foo_dispatcher = foo(a, , c)
        Case 11: foo_dispatcher = foo(a, , c, d)
        Case 12: foo_dispatcher = foo(a, b)
        Case 13: foo_dispatcher = foo(a, b, , d)
        Case 14: foo_dispatcher = foo(a, b, c)
        Case 15: foo_dispatcher = foo(a, b, c, d)
    End Select
End Function

Sub test()
    Debug.Print foo_dispatcher(Null, Null, Null, Null)
    Debug.Print foo_dispatcher(Null, 1, Null, 2)
    Debug.Print foo_dispatcher(1, 2, 3, 4)
    Debug.Print foo_dispatcher()
    Debug.Print foo_dispatcher(, 1, , 2)
    Debug.Print foo_dispatcher(a:=1, d:=Null)
End Sub

Output of test:

Nothing passed
b d passed
a b c d passed
Nothing passed
b d passed
a passed

Obviously the actions in the 16 cases can be tailored to the calling convention of foo (e.g. you can dispatch to foo(a,d) rather than foo(a,,,d) if you need). I didn't check all 16 cases explicitly but it seems to work. What I did was somewhat mechanical. You can write a dispatch-generator - a function which takes a string function name, an array of required parameters, an array of optional parameters, and a value which plays the role of Null and returns a the dispatcher as a string which can be copy-pasted from the immediate window to a code module. I thought of doing that here but it didn't seem worth it for a proof-of-concept dispatcher.