title: How to get a key for a single item in an Excel VBA collection
body: <p>I'm trying to write a generalized sort that will sort a VBA collection on the key.  I'm using the sort <a href="https://stackoverflow.com/questions/3587662/how-do-i-sort-a-collection">here</a> as a starting point.  </p>
  
  <p>The sort doesn't work if the collection contains objects (as shown below). How do I modify the code to get the key to use for the sort?</p>
  
  <p>The information I was able to find gets the collection of keys rather than getting the key for a single item (for example here: <a href="https://stackoverflow.com/questions/5702362/vba-collection-list-of-keys">VBA collection: list of keys</a>).</p>
  
  <p>The current code shown below gives "Object doesn't support this property or method" on the Debug.print line.  </p>
  
  <pre><code>Public Function sort(ByRef col As VBA.collection)
      For i = 1 To col.Count - 1
          For j = i + 1 To col.Count
              Debug.Print ("Key: " &amp; col(i))
              If col(i) &gt; col(j) Then
                  'store the lesser item
                  temp = col(j)
                  'remove the lesser item
                  col.Remove j
                  're-add the lesser item before the greater item
                  col.Add temp, temp, i
              End If
          Next j
      Next i
  End Function
  
  Sub test()
      Dim col As collection
      Set col = New collection
      Dim pi As ProgramIncrement
      ' val
      Set pi = New ProgramIncrement
      pi.name = "foo3"
      col.Add pi, pi.name
      ' val
      Set pi = New ProgramIncrement
      pi.name = "foo0"
      col.Add pi, pi.name
      ' val
      Set pi = New ProgramIncrement
      pi.name = "foo2"
      col.Add pi, pi.name
      ' val
      Set pi = New ProgramIncrement
      pi.name = "foo1"
      col.Add pi, pi.name
  
      ' sort
      sort col
      Debug.Print ("Done.")
  
  End Sub
  
  ' ---------------------------------------------------------------
  '
  ' Class to represent Program Increment
  '
  ' ---------------------------------------------------------------
  
  Public name As String
  
  Public sprints As New collection
  </code></pre>