Excel VBA: On Error Goto statement not working inside For-Loop

Question

I'm trying to cycle through a table in excel. The first three columns of this table have text headings, the rest of them have dates as headings. I want to assign those dates, sequentially, to a Date-type variable, and then perform some operations based on the date

To do this I am using a foreach loop on myTable.ListColumns. Since the first three columns do not have date headers, I have tried to set the loop up so that, if there is an error assigning the header string to the date-type variable, the loop goes straight to the next column

This seems to work for the first column. However, when the second column's header is 'assigned' to the date-type variable, the macro encounters an error even though it is within an error-handling block

Dim myCol As ListColumn
For Each myCol In myTable.ListColumns
    On Error GoTo NextCol

    Dim myDate As Date
    myDate = CDate(myCol.Name)

    On Error GoTo 0

    'MORE CODE HERE

NextCol:
    On Error GoTo 0
Next myCol

To reiterate, the error is thrown on the second round of the loop, at the statement

myDate = CDate(myCol.Name)

Can anyone explain why the On Error statement stops working?

Rather than using an error as your control structure, maybe an IF with an IsDate function would be more suitable in this scenario? — ray
– ray, Commented Aug 17, 2012 at 2:29
If you are "blindly" handling the error - rather than taking specific action on an error type - then you should just use an On Error Resume Next outside your loop. At the moment you are using error handling afresh on each column. — brettdj
– brettdj, Commented Aug 17, 2012 at 2:40
@brettdj, I don't think you can just resume next. The whole idea is to skip over the "more code here" code if the date conversion fails. So you need to go into a handler so that you can resume to a specific line. In addition, you only want the handler enabled for the date conversion, not the entire loop body. — paxdiablo
– paxdiablo, Commented Aug 17, 2012 at 3:01
@paxdiablo On reflection I agree. I would like to see 'more code here'. — brettdj
– brettdj, Commented Aug 17, 2012 at 3:26

Community · Accepted Answer · 2018-05-08 21:48:18Z

50

With the code as shown, you're actually still considered to be within the error handling routine when you strike the next statement.

That means that subsequent error handlers are not allowed until you resume from the current one.

A better architecture would be:

    Dim myCol As ListColumn
    For Each myCol In myTable.ListColumns
        On Error GoTo ErrCol
        Dim myDate As Date
        myDate = CDate(myCol.Name)
        On Error GoTo 0
        ' MORE CODE HERE '
NextCol:
    Next myCol
    Exit Sub ' or something '

ErrCol:
    Resume NextCol

This clearly delineates error handling from regular code and ensures that the currently executing error handler finishes before you try to set up another handler.

This site has a good description of the problem:

Error Handling Blocks And On Error Goto

An error handling block, also called an error handler, is a section of code to which execution is tranferred via a On Error Goto <label>: statement. This code should be designed either to fix the problem and resume execution in the main code block or to terminate execution of the procedure. You can't use the On Error Goto <label>: statement merely skip over lines. For example, the following code will not work properly:

    On Error GoTo Err1:
    Debug.Print 1 / 0
    ' more code
Err1:
    On Error GoTo Err2:
    Debug.Print 1 / 0
    ' more code
Err2:

When the first error is raised, execution transfers to the line following Err1:. The error hander is still active when the second error occurs, and therefore the second error is not trapped by the On Error statement.

edited May 8, 2018 at 21:48

CommunityBot

11 silver badge

answered Aug 17, 2012 at 2:19

paxdiablo

888k243 gold badges1.6k silver badges2k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

enderland Over a year ago

"A better architecture" would be... a better error handling technique than skipping over errors :)

paxdiablo Over a year ago

@enderland: well, yes, that may be preferable, but "Needs must when the devil vomits into your kettle" :-)

paxdiablo Over a year ago

@brettdj, I'm not so sure of that. You only want the error handler enabled during the data conversion, not in the bit that says "more code here".

brettdj Over a year ago

@paxdiablo You make a fair point. Although I think it would make more sense to have error handling on through-out, and then test for the error condition triggered to judge how to handle it. +1 btw

Community · Accepted Answer · 2018-05-08 21:48:18Z

8

You need to add resume of some sorts in your error handling code to indicate the error handling is over. Otherwise, the first error handler is still active and you are never "resolved."

See http://www.cpearson.com/excel/errorhandling.htm (specifically the heading "Error Handling Blocks And On Error Goto" and following section)

edited May 8, 2018 at 21:48

CommunityBot

11 silver badge

answered Aug 17, 2012 at 2:18

enderland

14.2k18 gold badges105 silver badges157 bronze badges

Comments

AjV Jsy · Accepted Answer · 2015-07-21 16:15:58Z

8

Follow-up to paxdiablo's accepted answer. This is possible, allowing two error traps in the same sub, one after the other :

Public Sub test()
    On Error GoTo Err1:
    Debug.Print 1 / 0
    ' more code
Err1:
    On Error GoTo -1     ' clears the active error handler
    On Error GoTo Err2:  ' .. so we can set up another
    Debug.Print 1 / 0
    ' more code
Err2:
    MsgBox "Got here safely"
End Sub

Using On Error GoTo -1 cancels the active error handler and allows another to be set up (and err.clear doesn't do this!). Whether this is a good idea or not is left as an exercise for the reader, but it works!

edited Jul 21, 2015 at 16:15

answered Jul 21, 2015 at 15:48

AjV Jsy

6,1054 gold badges39 silver badges30 bronze badges

1 Comment

Mahhdy Over a year ago

Thanks. Was so helpful. Actually, on the loops, you need to reset the error handling...

LletDeCabra · Accepted Answer · 2023-11-16 19:11:32Z

2

change it to On error goto -1, instead

Dim myCol As ListColumn
For Each myCol In myTable.ListColumns
    On Error GoTo NextCol

    Dim myDate As Date
    myDate = CDate(myCol.Name)

    On Error GoTo 0

    'MORE CODE HERE

NextCol:
    On Error GoTo -1
Next myCol

answered Nov 16, 2023 at 19:11

LletDeCabra

1113 bronze badges

Comments

Profex · Accepted Answer · 2015-04-20 18:47:31Z

0

Clearing all property settings of the Err object is not the same as resetting the error handler.

Try this:

Sub TestErr()
Dim i As Integer
Dim x As Double
    On Error GoTo NextLoop
    For i = 1 To 2
10:     x = i / 0
NextLoop:
        If Err <> 0 Then
            Err.Clear
            Debug.Print "Cleared i=" & i
        End If
    Next
End Sub

You'll notice the just like the OP, it will catch the error properly when i =1 but it will fail on line 10 when i = 2, even though we used Err.Clear

answered Apr 20, 2015 at 18:47

Profex

1,3908 silver badges20 bronze badges

Comments

Arun Mani · Accepted Answer · 2022-10-28 07:21:06Z

0

Dim ws As worksheets

For Each myCol In myTable.ListColumns

On Error GoTo endbit

Dim myDate As Date

myDate = CDate(myCol.Name)

On Error GoTo 0
'MORE CODE HERE

endbit: Resume NextCol

NextCol: ' Next myCol Exit Sub ' or something '

edited Oct 28, 2022 at 7:21

answered Oct 28, 2022 at 7:19

Arun Mani

11 bronze badge

Collectives™ on Stack Overflow

Excel VBA: On Error Goto statement not working inside For-Loop

6 Answers 6

4 Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related