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

Question

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?

[**Optional Parameters and ParamArray in VBA**](http://vba4all.wordpress.com/2014/04/25/optional-parameters-and-paramarray-in-vba/) — , Jun 04 '14 at 15:24

score 4 · Accepted Answer · edited May 23 '17 at 12:02

4

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

edited May 23 '17 at 12:02

Community

1
1

answered Nov 20 '13 at 12:09

Thank you, It works, but what I want is to detect how many args were passed, like: `Public Sub Add( ParamArray args() as Variant)` `for each args in ParamArray` `mycollection.add ...` But whithout using variant type or ParamArray... – Lando Calc_rissian Nov 20 '13 at 12:12
what about using a `ParamArray`? – Nov 20 '13 at 12:13
The reason for refusing ParamArray is to force the function to only accept custom object type as argument and not other types. And also to preserve resources and speed of the whole code – Lando Calc_rissian Nov 20 '13 at 12:16
Then just make it check the type of the ParamArray elements, and `Err.Raise` an error if one of them isn't the right type. – Jean-François Corbett Nov 20 '13 at 12:18
@LandoCalc_rissian see `AddExtended()` – Nov 20 '13 at 12:19
Ok, if there is no other way I will use your recommendations, but, you know, every vba texts say "avoid variant types as long as u can" :P – Lando Calc_rissian Nov 20 '13 at 12:20
there is a way but it's not very efficient => checking all parameters that a function takes for being empty. So if you have 15 parameters than it's mad coding with some select case statements or if logic ... grr – Nov 20 '13 at 12:22
@mehow could you provide any link about AddExtended? I can't find about it in google :S Thanks ;) – Lando Calc_rissian Nov 20 '13 at 12:26
@LandoCalc_rissian `AddExtended` is a sub I've created and name. This is not a built in function so I doubt you'll find anything about it :P – Nov 20 '13 at 12:27
1

Oh, it is my first question in SO and i forgot to refresh to see your edited answer, yeah, it's fine for me, i will do that way, maybe with 2 different `Add()` methods: one for single items and the extended one for several items. Thank you @mehow and @jean-francois – Lando Calc_rissian Nov 20 '13 at 12:35

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

1 Answers1