CODEWITHSUNDEEP
excelvbaonerror
Vikas
Vikas

Reputation: 805

VBA On error strange Behavior

I am pretty experienced in VBA programming but this problem comes to me once a while and even after researching, I never found the answer of this problem.

Here is my code snippet

    Dim sht As Worksheet

    For Each sht In Worksheets
        On Error GoTo Nextsheet

        col.Add sht.name, sht.name '<-- will be using it as a main Line in my post.
        shtList.AddItem sht.name
        shtList.List(shtList.ListCount - 1, 1) = sht.name
Nextsheet:
        Err.Clear
    Next sht

This is a simple for loop and col is a Collection Object. What I am trying to do is if an item already exists in a collection, it will throw an error and goto NextSheet Label. If not, then it will load the sheet name in a list. So here is what my problem is:

Suppose there are two consecutive sheet names that already exist in a collection, then this code Doesn't run. First time, it finds an error in "main line" for the first time, then it skips to NextSheet Label. Good so far. But when it comes to Col next time, it doesn't consider the On Error Handler and give me an Automation error. If I execute this line at the same time in immediate Window, it gives me an error "key already exists to a different item", which is a correct behavior, but when I run it, it gives me Automation error and kills my code execution completely. I am really trying hard to find the reason of this behavior. Can someone help?

Thanks, V

Upvotes: 0

Views: 147

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

I think the problem is that you are getting an error while your error handler is still active; since there is no enabled but inactive error handler, the error is fatal at the point it occurs (see HELP for On Error statement).

I would try something like:

On Error Resume Next
    For Each sht In Worksheets
        col.Add sht.Name, sht.Name '<-- will be using it as a main Line in my post.
        If Err.Number = 0 Then
            shtList.AddItem sht.Name
            shtList.List(shtList.ListCount - 1, 1) = sht.Name
        Else
            Err.Clear
        End If
    Next sht
On Error Goto 0

Upvotes: 2

Pieter Geerkens
Pieter Geerkens

Reputation: 11883

You need to rewrite your routine like this:

    Dim sht As Worksheet

    For Each sht In Worksheets
        On Error GoTo ErrHandler

        col.Add sht.name, sht.name '<-- will be using it as a main Line in my post.
        shtList.AddItem sht.name
        shtList.List(shtList.ListCount - 1, 1) = sht.name
Nextsheet:
        Err.Clear
    Next sht

    ' other code here

EndSub:
    exit sub

ErrHandler:
    Resume NextSht
End Sub

Now you can probably guess hat it going on - you have pushed a hidden 'stack-popper' onto an internal stack in the VBA Engine and neglected to pop it. The Resume Next Sht will do that.

Upvotes: 2

Related Questions