On Error Resume Next

N

Nigel

What causes On Error Resume Next to be ignored ??

I have a piece of code that exploits the Collection limit of unique key
values only, however it still throws an error when trying to add a duplicate
key to the Collection - as if the On Error Resume Next is being ignored

Dim NoDupes As New Collection
Dim AllCells As Range, Cell As Range
Set AllCells = Range("A1:A100")

' The next statement ignores the error caused by attempting to add a
duplicate key to the collection.
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
' Resume normal error handling
On Error GoTo 0
 
N

Norman Jones

Hi Nigel,

You need to restore default error handling *within* the For Each ... Next
loop, i.e.:

On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
' Resume normal error handling
On Error GoTo 0
Next Cell

With your code error hanling is only restored one the collection loading is
complete, hence the error.
 
D

Dave Peterson

Your code worked for me.

In the VBE, try:
Tools|Options|General tab
Change the error trapping setting to "break on unhandled errors"

If I had "break on all errors" set, then I got an error.
 
N

Norman Jones

Hi Dave,

Thank you, you are, of course correct.

What moving the On Error Goto 0 into the loop does do is to clear the error
at each pass and, thus, enable code to be run in response to any error.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top