There are two ways in which a routine can change the number of arguments that has to be provided to it:
- declare some of the trailing arguments as
Optional
- declare the last argument as
ParamArray
A single routine can use either or both.
An Optional parameter may have a strict type (e.g. Optional s As String), but then it will be impossible to detect whether it was passed. If you don't pass a value for such argument, the correct flavour of "blank" will be used, which is indistinguishable from passing that blank value manually.
So, having Public Sub Bob(Optional S As String), you cannot detect from inside of Bob whether it was called as Bob or as Bob vbNullString.
An optional parameter may have a default value, which suffers from the same problem. So, having Public Sub Bob(Optional S As String = "Default Value"), you cannot detect if Bob was called as Bob or as Bob "Default Value".
To be able to truly detect whether an optional parameter was passed, they have to be typed as Variant. Then a special function, IsMissing, can be used inside the routine to detect if a parameter was passed.
Public Sub Bob(Optional a, Optional b, Optional c, Optional d)
Debug.Print IsMissing(a), IsMissing(b), IsMissing(c), IsMissing(d)
End Sub
Bob 1, , 3 ' Prints False, True, False, True
ParamArray can only be the last argument, and it allows an infinite* number of arguments to be passed starting from this position. All these arguments arrive packed in a single Variant array (no option for static typing here).
The IsMissing function does not work on the ParamArray argument (always returns False). The way to know how many arguments were passed is to compare UBound(args) with LBound(args). Note that this only tells you how many argument "slots" were used, but some of them can be in fact missing!
Public Sub BobArray(ParamArray a())
Dim i As Long
For i = LBound(a) To UBound(a)
Debug.Print IsMissing(a(i)), ;
Next
Debug.Print
End Sub
BobArray ' Prints empty line (the For loop is not entered due to UBound < LBound)
Sheet1.BobArray 1, 2, 3 ' Prints False, False, False
Sheet1.BobArray 1, , 3 ' Prints False, True, False
Note that you cannot pass "missing" value for the trailing arguments of the ParamArray, i.e. this is illegal:
Sheet1.BobArray 1, , 3, ' Does not compile
However, you can work around this using the trick described below.
An interesting use case that you touch in your question is preparing an array of all arguments in advance, passing it to the function, filling all the arguments "placeholders", but still expecting the function to detect that some of the arguments are missing (not passed).
Normally this is not possible, because if anything is passed (even "blank" values, such as Empty, Null, Nothing of vbNullString), then it still counts as passed, and IsMissing() will return False.
Fortunately, the special Missing value is nothing but a specially constructed Variant, and even without knowing how to construct that value manually, we can trick the compiler to give it away:
Public Function GetMissingValue(Optional ByVal IgnoreMe As Variant) As Variant
If IsMissing(IgnoreMe) Then
GetMissingValue = IgnoreMe
Else
Err.Raise 5, , "I told you to ignore me, didn't I"
End If
End Function
Dim missing As Variant
missing = GetMissingValue()
Dim arglist1(1 To 4) As Variant
arglist1(1) = 42
arglist1(2) = missing
arglist1(3) = missing
arglist1(4) = "!"
Bob arglist1(1), arglist1(2), arglist1(3), arglist1(4) ' Prints False, True, True, False
Now, we can work around the inability to pass "missing" to the trailing "slots" of ParamArray:
Dim arglist1(1 To 4) As Variant
arglist1(1) = 42
arglist1(2) = missing
arglist1(3) = missing
arglist1(4) = missing
BobArray arglist1(1), arglist1(2), arglist1(3), arglist1(4) ' Prints False, True, True, True
Note, however, that this workaround will only work if you call BobArray directly. If you use Application.Run, it will not work because the Run method will discard any trailing "missing" arguments before passing them onto the called routine:
Dim arglist1(1 To 4) As Variant
arglist1(1) = 42
arglist1(2) = missing
arglist1(3) = missing
arglist1(4) = missing
Application.Run "BobArray", arglist1(1), arglist1(2), arglist1(3), arglist1(4)
' Prints False, because only one argument is passed
