A workaround for a conditional function call with some optional parameters?

Question

Consider a function with several optional parameters. For instance:.

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

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

An example is the Collection class. There are 3 optional parameters in the add method. Many custom classes carry this class - for example, to create an indexable Person class from a custom Person class or something like that. The wrapper should deal with 6 combinations: adding only the key, adding the key and defining Before, adding the key and specifying After, not adding the key and specifying Before, not adding the key and specifying After, no Key and no Before. It will be even worse if you write a wrapper on something like Workbook.Save, which has a dozen optional parameters.

I don’t know an alternative to some tedious construction 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 ... 

It is clear that the number of nested Ifs overhead and common code is approximately doubled for each additional optional variable.

In .NET, there is a System.Type.Missing type that can be passed, which makes it a lot easier.

As in C #, it can be as clean as:

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

(this says: "If a is null, then passed to Missing (or perhaps behind the scenes it will reorganize the call itself), the call is equivalent to not passing anything for the optional argument a, etc.)

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

Is there any way I am missing a VBA workaround?

Answer 1

(Editor: I added an improvement to HarveyFrench 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 

test output:

 Nothing passed bd passed abcd passed Nothing passed bd passed a passed 

Obviously, actions in 16 cases can be adapted to the calling convention foo (for example, you can send foo(a,d) rather than foo(a,,,d) if you need to). I have not tested all 16 cases explicitly, but it seems to work. What I did was somewhat mechanical. You can write a dispatcher generator — a function that takes the name of a string function, an array of required parameters, an array of optional parameters and a value that plays the role of Null and returns the dispatcher as a string that can be copied from the direct window to the code module. I thought about doing it here, but it did not seem to him appropriate for the manager of the evidence-based concept.

Answer 2

John Coleman's answer is "as good as VBA"

I think foo_dispatcher will also need the following code modification (in many cases):

 Function foo_dispatcher(a, b, c, 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, 4) caseNum = caseNum + IIf(IsNull(d) or IsEmpty(d) or IsMissing(d), 0, 4) ' ... 

and in test cases should also be

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