Adding Unique Items to a Collection
 

Hello,

I'm using the technique described in "Microsoft Excel 2000 - Power
Programming with VBA" to create a list of unique items out of a total list
that might contain duplications. The technique uses the same value being
added to the collection as the key for the collection. Thus when a
duplicate value is encountered and error will occur. Using "On Error", the
attempt to add a duplicate record is ignored and the next record is
processed.

-----------------------------
On Error GoTo SkipRecord:

For RC = 1 To myTotalRecordCount

myUniqueRecordsCollection.Add myTotalRecordsCollection(RC),
CStr(myTotalRecordsCollection(RC))
myUniqueRecordsIndices.Add RC, CStr(RC)

SkipRecord:

Next RC
-----------------------------

I happen to know that my first, second, and ninth records are unique and the
rest are duplicates. The procedure executes fine, adding records 1 and 2
and their indices to the respective collections, skipping record 3 and then
failing at RC = 4. Records 3 and 4 are both identical to record 2. The
error is the duplicate key error, the exact error I'm trying to ignore as
far as I can tell:

-----------------------------
Run-time error '457':

This key is already associated with an element of this collection
-----------------------------

It almost acts as if it has forgotten the command, "On Error GoTo
SkipRecord" between RC =3 and RC=4.

Does anyone have any advice?

Thanks,

WJ

Adding Unique Items to a Collection
 

From XL/VBA Help ("On Error Statement"):

an "active" error handler is an enabled handler that is in the process of
handling an error. If an error occurs while an error handler is active
(between the occurrence of the error and a Resume, Exit Sub, Exit Function,
or Exit Property statement), the current procedure's error handler can't
handle the error.

So one way:

On Error GoTo ErrHandler
For RC = 1 To myTotalRecordCount
myUniqueRecordsCollection.Add _
myTotalRecordsCollection(RC), _
CStr(myTotalRecordsCollection(RC))
myUniqueRecordsIndices.Add RC, CStr(RC)
SkipRecord:
Next RC
'remainder of your code
Exit Sub
ErrHandler:
Resume SkipRecord
End Sub


In article , "WJ"
wrote:

Hello,

I'm using the technique described in "Microsoft Excel 2000 - Power
Programming with VBA" to create a list of unique items out of a total list
that might contain duplications. The technique uses the same value being
added to the collection as the key for the collection. Thus when a
duplicate value is encountered and error will occur. Using "On Error", the
attempt to add a duplicate record is ignored and the next record is
processed.

-----------------------------
On Error GoTo SkipRecord:

For RC = 1 To myTotalRecordCount

myUniqueRecordsCollection.Add myTotalRecordsCollection(RC),
CStr(myTotalRecordsCollection(RC))
myUniqueRecordsIndices.Add RC, CStr(RC)

SkipRecord:

Next RC
-----------------------------

I happen to know that my first, second, and ninth records are unique and the
rest are duplicates. The procedure executes fine, adding records 1 and 2
and their indices to the respective collections, skipping record 3 and then
failing at RC = 4. Records 3 and 4 are both identical to record 2. The
error is the duplicate key error, the exact error I'm trying to ignore as
far as I can tell:

-----------------------------
Run-time error '457':

This key is already associated with an element of this collection
-----------------------------

It almost acts as if it has forgotten the command, "On Error GoTo
SkipRecord" between RC =3 and RC=4.

Does anyone have any advice?

Thanks,

WJ

Adding Unique Items to a Collection
 

Yes!!!!!! My problem was that "Next RC" was part of my error handler! The
error handler was already active after RC = 3 and so could not reactivate
when it encountered another error on RC = 4. Thanks so much for your help!

"JE McGimpsey" wrote in message
...
From XL/VBA Help ("On Error Statement"):

an "active" error handler is an enabled handler that is in the process of
handling an error. If an error occurs while an error handler is active
(between the occurrence of the error and a Resume, Exit Sub, Exit
Function,
or Exit Property statement), the current procedure's error handler can't
handle the error.

So one way:

On Error GoTo ErrHandler
For RC = 1 To myTotalRecordCount
myUniqueRecordsCollection.Add _
myTotalRecordsCollection(RC), _
CStr(myTotalRecordsCollection(RC))
myUniqueRecordsIndices.Add RC, CStr(RC)
SkipRecord:
Next RC
'remainder of your code
Exit Sub
ErrHandler:
Resume SkipRecord
End Sub


In article , "WJ"
wrote:

Hello,

I'm using the technique described in "Microsoft Excel 2000 - Power
Programming with VBA" to create a list of unique items out of a total
list
that might contain duplications. The technique uses the same value being
added to the collection as the key for the collection. Thus when a
duplicate value is encountered and error will occur. Using "On Error",
the
attempt to add a duplicate record is ignored and the next record is
processed.

-----------------------------
On Error GoTo SkipRecord:

For RC = 1 To myTotalRecordCount

myUniqueRecordsCollection.Add myTotalRecordsCollection(RC),
CStr(myTotalRecordsCollection(RC))
myUniqueRecordsIndices.Add RC, CStr(RC)

SkipRecord:

Next RC
-----------------------------

I happen to know that my first, second, and ninth records are unique and
the
rest are duplicates. The procedure executes fine, adding records 1 and 2
and their indices to the respective collections, skipping record 3 and
then
failing at RC = 4. Records 3 and 4 are both identical to record 2. The
error is the duplicate key error, the exact error I'm trying to ignore as
far as I can tell:

-----------------------------
Run-time error '457':

This key is already associated with an element of this collection
-----------------------------

It almost acts as if it has forgotten the command, "On Error GoTo
SkipRecord" between RC =3 and RC=4.

Does anyone have any advice?

Thanks,

WJ