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?

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?

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?

Bing wrote:
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.

One way would be to make the Key argument mandatory or use a default,
making the Before and After arguments irrelevant. My collection classes
are always for object's, rather than values, so this is easy for me: I
either default to the object's Name property or use its ObjPtr() value
as appropriate. This makes the Remove method very easy i.e. use the Key
to remove the object. That said, I rarely use the remove method: easier
to start over using a new collection class instance.

Jamie.

--

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?

Hi Bing,

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.

A potential alternative is to use the Collection class from the Microsoft
Scripting Runtime (use Tools References to add a reference to it), which
has an alternative syntax for adding items to the Dictionary that does not
generate errors with duplicate keys:

Sub test()

Dim x As New Dictionary

x("Hello") = "Hello"
Debug.Print x("Hello")

x("Hello") = "Goodbye"
Debug.Print x("Hello")


End Sub


Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk