Excel VBA. How to pass objects as optional args and be able detect them?

collections, excel, vba

Solution

If Object2 Is Nothing Then
    Debug.Print "obj2 is nothing"
Else
    MyCollection.Add Object2
End If

the less pretty way but less code is

If Not Object2 Is Nothing then
    MyCollection.Add Object2
End if

Public Sub AddExtended(ParamArray arr())
    Dim item
    Debug.Print "the count: " & UBound(arr) + 1
    For Each item In arr
        If TypeOf item Is customClass Then
            Debug.Print "type of item is customClass"
            'MyCollection.Add item
        End If
    Next
End Sub

and for example call that

Dim o1 As New customClass
Dim o2 As New customClass

Call AddExtended(o1, o2, o2)
'AddExtended o1, o2, o2

you can also make use of custom collections

see this and this

Problem

I have created a custom collection class, in a class module, in Excel. I want to put a function to populate the collection with some custom objects, so I can pass one or more objects at one time. The function I created is: ``` Public Sub Add( Object1 As customClass, _ Optional Object2 As customClass, _ Optional Object3 As customClass, _ Optional Object4 As customClass, _ Optional Object5 As customClass) ``` The problem is that I don't know how to detect how many args were passed to the function... How can I detect them? In the other hand I was trying something like this: ``` Dim i as integer for i = 1 to 5 If Not IsMissing("Object" & i) then MyCollection.Add "Object" & i Next i ``` ... buy obviously it does not work. How can I do it in an elegant and simple way?

Original source

Related problems