When working with collections, use an error handler to easily determine if a given key exists in the collection. If you try to access an item from a collection where the key doesn’t exist, you’ll get an error. Likewise, if you try to add an item that exists, you’ll also get an error. This example shows an error handler for adding an item to a collection. To trap for errors where an item exists, trap error code 457:
Private Function BuildCustCol(CustList As ListBox) As _ Collection On Error GoTo ProcError Dim colCust As Collection Dim lngCustCnt As Long Dim J As Long Set colCust = New Collection For J = 0 To CustList.ListCount - 1 lngCustCnt = colCust(CStr(CustList.List(J))) + 1 colCust.Remove (CStr(CustList.List(J))) colCust.Add Item:=lngCustCnt, _ Key:=CStr(CustList.List(J)) Next J Set BuildCustCol = colCust Set colCust = Nothing Exit FunctionProcError: Select Case Err Case 5 'collection item doesn't exist, so add it colCust.Add Item:=0, _ Key:=CStr(CustList.List(J)) Resume Case Else 'untrapped error End SelectEnd Function