Bing,

You could write a collection class that has add remove methods etc., but
that seems overkill to me. Here is another way.

Sub test()
Dim myCol As Collection

Set myCol = New Collection
myCol.Add "Bob", "Key1"
MsgBox myCol("Key1")
On Error Resume Next
If KeyExists(myCol, "Key1") Then
RemoveKey myCol, myCol("Key1")
End If
myCol.Add "Lynne", "Key1"
MsgBox myCol("Key1")

End Sub


Private Function KeyExists(col As Collection, ByVal sKey As String)
On Error GoTo NoSuchKey

If VarType(col.Item(sKey)) = vbObject Then
' force an error condition if key does not exist
End If

KeyExists = True
Exit Function

NoSuchKey:
KeyExists = False
End Function

Private Sub RemoveKey(col As Collection, ByVal val As String)
Dim i As Long

For i = 1 To col.Count
If col.Item(i) = val Then
col.Remove i
End If
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bing" wrote in message
...
The Collection has an annoying feature where if you want to replace an
existing key, you will first have to remove it otherwise an error occurs.

So i was going to wrap the Collection and override the Add method to
remove
the key from collection than delegate to the actual collection itself to
perform the add but doesn't seem to be as straight forward as initially
thought without writing lots of code to check for which option was/wasn't
specified in the new Add method before delegating it to the actual
Collection.

Being new to VBA, is their some syntax to handle this optional paraments
passing, or anyone have a better way of going about doing this?