ByVal or ByRef - Collections

The Collection data type is a Reference data type.
When you declare a variable with a Collection data type it can be passed in using either ByRef or ByVal.
In VBA all reference data types are passed by reference although there is a subtle difference.
Although it is a Reference data type there is a difference between passing ByRef vs ByVal

ByRef

the address of the object is passed.
You must use the Set statement because VBA.Collection is a reference data type.
The second Debug.Print displays the number 0.

Public Sub Passing_Collections1() 
    Dim MyCol As Collection 

    Set MyCol = New Collection 
    Call MyCol.Add(item:="1", key:="one") 
    Call MyCol.Add(item:="2", key:="two") 
    Call MyCol.Add(item:="3", key:="three") 
    Debug.Print MyCol.Count   '3  
    
    Call ByReference(MyCol) 
    Debug.Print MyCol.Count   '0  
End Sub 

Public Sub ByReference(ByRef MyCol2 As Collection) 
    'this is the original
    'the object has been passed by reference so this new item is added to the original collection.
    Call MyCol2.Add(item:="5", key:="five") 
    
    'the object has been passed by reference so this changes the original
    'the new object reference will be passed back
    'we can see this because the third Debug.Print displays the number 0
    Set MyCol2 = New Collection 
End Sub 

ByVal

a copy of the address to the object is passed.
You must use the Set statement because VBA.Collection is a reference data type.
The second Debug.Print displays the number 4.

Public Sub Passing_Collections2() 
    Dim MyCol As Collection 

    Set MyCol = New Collection 
    Call MyCol.Add(item:="1", key:="one") 
    Call MyCol.Add(item:="2", key:="two") 
    Call MyCol.Add(item:="3", key:="three") 
    Debug.Print MyCol.Count   '3  
    
    Call ByValue(MyCol) 
    Debug.Print MyCol.Count   '4  
End Sub 

Public Sub ByValue(ByVal MyCol2 As Collection) 
    'the object has been passed by value so this new item is added to the original collection
    'we can see this because the second Debug.Print displays the number 4
    Call MyCol2.Add(item:="4", key:="four") 
    
    'the object has been passed by value so this changes just the local copy
    'any changes made to the objects reference (ie new assignment) is contained within the subroutine
    'we can see this because the second Debug.Print displays the number 4
    Set MyCol2 = New Collection 
End Sub