Bing,

It is easy to reduce the bloat.with a bit of judicial re-writing

Sub test()
Dim myCol As Collection

Set myCol = New Collection
AddToCollection myCol, "Bob", "Key1"
MsgBox myCol("Key1")
On Error Resume Next
AddToCollection myCol, "Lynne", "Key1"
MsgBox myCol("Key1")

End Sub

Private Sub AddToCollection(ByRef col As Collection, _
ByVal val As String, ByVal sKey As String)
Dim i As Long
Dim oldVal
If KeyExists(col, oldVal, "Key1") Then
For i = 1 To col.Count
If col.Item(i) = oldVal Then
col.Remove i
End If
Next i
End If
col.Add val, sKey
End Sub

Private Function KeyExists(col As Collection, ByRef val, 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
val = col.Item(sKey)
Exit Function

NoSuchKey:
KeyExists = False
End Function

--

HTH

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


"Bing" wrote in message
...
Hi Bob,

Thanks for responding!

Currently i had also done it more or less the same way as you have
mentioned
but then i noticed that i do a lot of adding elements into collections and
a
lot of bloat code develops because i have to keep checking whether that
key
exists or not before adding. So i thought of wrapping the default
implementation of the Collection and just override the Add method to check
for existence of key, delete it if it exists, and then delegate to
original
Collection to re-insert. That way all the bloat code only occurs once,
inside the wrapper, instead of everywhere else in the project where i have
to
add to any Collections.

Tricky part here is (and i can't find an elegant solution) trying to pass
arguments fromt the Wrapper to the Collection when soem of the arguments
can
be optionally specified without doing bunch of
if-elseif-elseif-elseif-elsif.... etc. Ideally, i'd like to 'implement'
fully the 'interface' of the Collection (for lack of better words).

"Bob Phillips" wrote:

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?