CODEWITHSUNDEEP
excelvbaerror-handling
iDevlop
iDevlop

Reputation: 25252

Why VBA error handling seems to be ignored here?

The following statements (in a loop) end in an error when row 1 is empty.
I would have expected the error to be silently ignored and the next sheet to be processed.
How come the code stops when the on error is active ? I would expect it to ignore the error and continue processing after the skip label.

Sub listSheets()
    Dim sh As Worksheet, ar
    Dim a
    Set a = Application

    For Each sh In ThisWorkbook.Sheets
        Sheet1.Cells(3 + sh.Index, 1) = sh.Name
        On Error GoTo skip
        ar = sh.Range("1:1").SpecialCells(xlCellTypeConstants)  'code stops here if row 1 empty
        ar = a.Transpose(a.Transpose(ar))
        Sheet1.Cells(3 + sh.Index, 2) = Join(ar, "//")
        ar = Null
skip:
    Next sh
End Sub

Note: Error trapping is set to "Break on unhandled errors"

Upvotes: 1

Views: 1000

Answers (2)

Domenic
Domenic

Reputation: 8104

A Resume statement is needed after an error occurs in which an error handler is enabled by the On Error statement. As per VBA reference...

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.

For additional information have a look at the following link...

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/on-error-statement

Upvotes: 1

JvdV
JvdV

Reputation: 75840

I believe the reason lays in the fact you allready encountered an error. You however never cleared your error from the handler. A second error won't skip over that line again.

So to replicate this I had three worksheets, a blank row in the second and third. Your On Error Goto went through the second sheet but would return Error 1004 on the third.

You might want to include an Err.Clear

On Error Resume Next
    ar = sh.Range("1:1").SpecialCells(xlCellTypeConstants)  'error 1004 on certain sheets
skip:
    Err.Clear
Next sh

EDIT: Just found an alternative solution here

And even then, maybe even drop the error handling completely and use something like:

If WorksheetFunction.CountA(sh.Rows(1)) > 0 Then
    ar = sh.Range("1:1").SpecialCells(xlCellTypeConstants)
End If

Upvotes: 3

Related Questions