CODEWITHSUNDEEP
vbaexcel
Kai
Kai

Reputation: 2168

Excel VBA still breaks after On Error handler

I have a very simple bit of code that loops through a column of data and adds unique values to a Collection.

It's VBA, so of course Collection lacks an Exists function (who'd ever want that?), and I'd rather avoid iterating over the entire collection for every cell in the column, I decided to go for the error-handling approach - attempt to retrieve the item from the collection, catch the error that occurs if it doesn't exist and add it:

'Trucated the code slightly, I know I should be checking the actual error code, but omitted that for brevity

Dim r As Range
Set r = MySheet.Range("B2") 'First cell in column

Dim uniqueValues As New Collection
Do While r.Value <> ""
    On Error GoTo ItemExists
    'If r.Value doesn't exist in the collection, throws an error
    uniqueValues.Add(Item:=r.Value, Key:=r.Value) 
ItemExists:
    r.Offset(1)
Loop

The problem? Excel seems to be completely ignoring the On Error line, breaking the code and throws up the Continue/End/Debug dialog regardless.

I've checked the options in VBA, it's correctly set to Break on Unhandled Errors.

Any idea why this is happening?

Upvotes: 0

Views: 1082

Answers (2)

SierraOscar
SierraOscar

Reputation: 17637

use an ArrayList instead, which has an .Contains method amongst other handy things like a .Sort method

With CreateObject("System.Collections.ArrayList")

    .Add "Item 1"
    .Add "Item 2"

If .Contains "Item 1" Then Msgbox "Found Item 1"
If .Contains "Item 3" Then Msgbox "Found Item 3"


End With

some more examples here

Upvotes: 1

to StackOverflow
to StackOverflow

Reputation: 124706

You could use On Error Resume Next, but you're better off encapsulating the error handling in its own Sub or Function. E.g. something like:

Private Sub AddIfNotPresent(Coll As Collection, Value As Variant, Key As Variant)
    On Error Resume Next
    Coll.Add Item:=Value, Key:=Key
End Sub

which you could use as follows:

Do While r.Value <> ""
    AddIfNotPresent uniqueValues, r.Value, r.Value
    r = r.Offset(1)
Loop

The reason for your problem is described in the VBA documentation for On Error:

An "enabled" error handler is one that is turned on by an 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

You have not called Resume or exited the procedure after the first error, so the error handler can't handle subsequent errors.

UPDATE

From comments:

I hate the idea of promoting the use of On Error Resume Next ...

I can sympathise with this POV, but there are some things in VBA (e.g. checking if a key is present in a Collection) that you can only do by handling an error. If you do this in a dedicated helper method (Sub/Function), it's a reasonable approach. Of course, you can use On Error Goto instead, e.g. something like the following (a variation on the above which tests to see if a collection contains a given key):

Public Function ContainsKey(Coll As Collection, Key As Variant) As Boolean
    On Error GoTo ErrHandler
    Dim v As Variant
    v = Coll(Key)
    ContainsKey = True
    Exit Function
ErrHandler:
    ContainsKey = False
    Exit Function
End Function

Upvotes: 5

Related Questions