Making empty a VBA collection

Question

I have a problem with a Collection object in Visual Basic for Aplications (I use it with Excel)

I have this code trying to empty a Collection object that I have to re-use:

Sub limpieza(ByRef listilla As Collection)

    While listilla.Count <> 0
        listilla.Remove (listilla.Count)
    Wend

End Sub

But when I call it with:

Dim listado As New Collection
<I have some code here that add some values to the collection>
limpieza (listado)

VBA says to me that

argument is not optional

and the code doesn't run.

What can I do? I need to use this collection cleaning at the bottom of a loop that reuses the Collection object.

Answer 1

Dim listado As New Collection
<I have some code here that add some values to the collection>
limpieza listado
'this syntax also works
Call limpieza(listado)

Note that I removed the () around the argument.

When passing byref you want to do this as () causes VBA to pass it as byval by default unless you add Call as well.

This can be really frustrating since you can often actually use subName(args) as syntax sometimes but run into these sorts of problems. I generally use Call mySubName(args) to make things more clear.