Excel VBA: On Error Goto statement not working inside For-Loop
Solution 1
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.
Solution 2
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)
Solution 3
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!
Swiftslide
Updated on January 04, 2020Comments
-
Swiftslide over 4 years
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?
-
enderland over 11 years"A better architecture" would be... a better error handling technique than skipping over errors :)
-
paxdiablo over 11 years@enderland: well, yes, that may be preferable, but "Needs must when the devil vomits into your kettle" :-)
-
paxdiablo over 11 years@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 11 years@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
-
Mahhdy over 6 yearsThanks. Was so helpful. Actually, on the loops, you need to reset the error handling...