Sorting a collection

[tiredofit]

This code sorts a collection:

Public Function SortCollection(ByRef coll As Collection) As Collection

    Dim i As Long, j As Long
    
    Dim k As Variant
    
    For i = 1 To coll.Count - 1
        
        For j = i + 1 To coll.Count
            
            If UCase(String:=coll.Item(i)) > UCase(String:=coll.Item(j)) Then
               
               k = coll.Item(j)
               
               With coll
               
                    .Remove j
               
                    .Add Item:=k, _
                         Key:=k, _
                         Before:=i
            
                End With
                
            End If
        
        Next j
    
    Next i
    
    Set SortCollection = coll

End Function

It works if I test it as follows:

Sub Test()

    Dim coll As Collection
    Set coll = New Collection
    
    coll.Add "a"
    coll.Add "1"
    
    Set coll = SortCollection(coll)
    
End Sub

but fails if the "1" is not a string, ie this fails:

Sub Test()

    Dim coll As Collection
    Set coll = New Collection
    
    coll.Add "a"
    coll.Add 1
    
    Set coll = SortCollection(coll)
    
End Sub

Can someone please tell me why is that?

Thanks

 

[RoryA]

You can refer to items in a collection either by their key or by their index position. If you use a numeric value for key, it is assumed to be the index so in this case you are trying to add key 1 when there is already an item at position 1.

 

[tiredofit]

You can refer to items in a collection either by their key or by their index position. If you use a numeric value for key, it is assumed to be the index so in this case you are trying to add key 1 when there is already an item at position 1.

Thanks.

But why does the following work, taken from here:

https://excelmacromastery.com/excel-vba-collections/

(slightly adpated, in that I changed the Sub to a function):

Option Explicit

Sub TestSort()

    Dim coll As New Collection
    coll.Add "a"
    coll.Add 1
   
    Set coll = QuickSort(coll, 1, coll.Count)
   
End Sub

Function QuickSort(coll As Collection, first As Long, last As Long) As Collection
 
  Dim vCentreVal As Variant, vTemp As Variant
 
  Dim lTempLow As Long
  Dim lTempHi As Long
  lTempLow = first
  lTempHi = last
 
  vCentreVal = coll((first + last) \ 2)
  Do While lTempLow <= lTempHi
 
    Do While coll(lTempLow) < vCentreVal And lTempLow < last
      lTempLow = lTempLow + 1
    Loop
   
    Do While vCentreVal < coll(lTempHi) And lTempHi > first
      lTempHi = lTempHi - 1
    Loop
   
    If lTempLow <= lTempHi Then
   
      ' Swap values
      vTemp = coll(lTempLow)
     
      coll.Add coll(lTempHi), After:=lTempLow
      coll.Remove lTempLow
     
      coll.Add vTemp, Before:=lTempHi
      coll.Remove lTempHi + 1
     
      ' Move to next positions
      lTempLow = lTempLow + 1
      lTempHi = lTempHi - 1
     
    End If
   
  Loop
 
  If first < lTempHi Then QuickSort coll, first, lTempHi
  If lTempLow < last Then QuickSort coll, lTempLow, last
 
  Set QuickSort = coll
 
End Function

 

[RoryA]

Because it doesn't attempt to assign any keys.

 

[tiredofit]

Because it doesn't attempt to assign any keys.

Great, thanks for clarifying things.

So to make the first one "work", I could've just removed the Key:=k line and not have to convert the 1 to a string?

Between the two functions, is there a preference?

 

[RoryA]

Yep.

Between the two functions, is there a preference?

Not for me. They're basically the same.

 

[tiredofit]

Yep.



Not for me. They're basically the same.

Thanks

 

[RoryA]

But I do recommend you get out of the habit of doing this:

Dim coll As New Collection

even in a test proc.

 

[tiredofit]

But I do recommend you get out of the habit of doing this:

Dim coll As New Collection

even in a test proc.

Actually I NEVER do that in my "real code",

I use the two line method:

Dim Coll As Colllection
Set Coll = New Collection

Dim coll As New Collection was copied from the webpage.