CODEWITHSUNDEEP
vba
kainC
kainC

Reputation: 400

VBA specify no value for optional argument

I want to add to a collection using a statement like

myCollection.Add myItem, After:=Iif(myCollection.Count > 0, myCollection.Count, vbNull)

In other words, I want to decide if I want to specify the parameter "After", and if I don't then I want to pass a non-value value in it's place.

It would be a lot cleaner than having to put in a separate call for each scenario.

Is this possible in VBA?

Upvotes: 1

Views: 2258

Answers (3)

Comintern
Comintern

Reputation: 22195

I'm going to begin with a caveat that calling Collection.Add with an IIf statement like that is not "cleaner" than having 2 different calls. IIf is a function and carries the overhead of a function call. Also, since both the true part and the false part are passed as parameters, both of them are evaluated regardless of whether the condition is true or false. That means you'll always call myCollection.Count twice. In addition to being much less efficient, it is much less readable. Unless you're code golfing, there is rarely a good reason to use IIf at all. This is much, much better on several levels:

If myCollection.Count > 0 Then
    myCollection.Add myItem, , myCollection.Count
Else
    myCollection.Add myItem
End If

Also, as mentioned in the comments, Collection does preserve order of added items, so the above code is identical to:

myCollection.Add myItem

OK, with that out of the way, if you really feel compelled to do this... Another option is to simply provide your own overload of IIf with optional parameters:

Private Function IIf(condition As Boolean, Optional truepart As Variant, _
                     Optional falsepart As Variant)
    IIf = VBA.IIf(condition, truepart, falsepart)
End Function

Note that this "hides" the built-in VBA function within the scope that you declare the function in, so you should comment it heavily and make sure that it doesn't break other calls to IIf elsewhere in your code. After you provide the overload, you can use it just like the built-in, but omit parameters:

Public Sub Example()
     ValueOfIIf IIf(True, , "Foo")
End Sub

Public Sub ValueOfIIf(result As Variant)
    Debug.Print IsMissing(result)    '<--True
End Sub

Upvotes: 5

Blackhawk
Blackhawk

Reputation: 6120

Yes, it is possible in VBA (Updated to avoid the complication of CopyMemory)

Add a new module mdlMissingVariant to your VBA project with the following code:

Public Function getMissingVariant(Optional v As Variant) As Variant
    getMissingVariant = v
End Function

Now, anytime you want to trick VBA into thinking you didn't provide a value for a parameter, use getMissingVariant() (don't provide a parameter) instead. For example, define the following function:

Public Sub testOpt(Optional v As Variant)
    Debug.Print IsMissing(v)
End Sub

And test with...

testOpt getMissingVariant()

...to see that the VBA IsMissing function detects that the parameter was not provided. The reason this works is a Variant is a 16-byte structure that includes a 2-byte code describing the type of data it contains. One of these types (10, in fact) is VT_ERROR. When you don't provide a parameter to an optional Variant, internally VBA changes the type of the Variant to "VT_ERROR" and assigns a value of &H80020004 (Long), which is the Windows error code for "Parameter not found".

EDIT

I stand corrected - as @Nik points out, the "missing" Variant can be used as a parameter of IIF, so your code will work fine!

Upvotes: 5

NikT
NikT

Reputation: 1990

Aside from that in this particular case I believe it's redundant since they should be added to collection in order anyway without specifying the "after"; two things come to mind:

1) You can use the iif function and take advantage of the "missing" property (not sure if there's a better way). 

Sub my_collection(Optional b As Variant)

    Dim x As Collection
    Set x = New Collection

    'if you just run my_collection without passing anything
    'b will be "missing"
    x.Add "Test", After:=IIf(x.Count, x.Count, b)

End Sub

since you never pass b to the my_collection, b will always be "missing" (you can test with IsMissing() )

2) Function to add the values

Sub my_collection()

    Dim x As Collection
    Set x = New Collection
    Dim v As String
    v = "test"
    Call add_to_collection(x, v)

End Sub
Sub add_to_collection(ByRef y As Collection, q As Variant)
    If y.Count Then
        y.Add q, After:=y.Count
    Else
        y.Add q
    End If
End Sub

Upvotes: 2

Related Questions