Error Handling

[AlexanderBB]

I must have misunderstood how it works

Sub isntworking()
    Dim Comm As Range
    Dim cl As Range
    Dim x As Integer
    With Worksheets("Sheet1")
           For x = 2 To 10
                Set iRange = .Range("A" & x & ":CV" & x)
                On Error GoTo NoExcelComments ' handle the error that occurs if there is no cell with comments
                Set Comm = iRange.SpecialCells(xlCellTypeComments)
                For Each cl In Comm ' iterate over all cells in rng - each of them contains a comment
                    Debug.Print cl
                Next
NoExcelComments:
        Next
    End With
End Sub

Stepping show the first row with no comments goes to the Error and Next.
But the next instance crashes with error 1004 No Cells Were found.
But hasn't the error handler been reset?

 

[6StringJazzer]

The error handler should be set each time through the loop. It sounds like you are saying that you are raising an error in the next line after On Error.

I'm not sure why you are seeing that behavior, but I am not a fan of using error handling to manage the logic path. If you know that there can be no comments, it's not really an error and should be tested for at the point it occurs, rather than using it as a GoTo. IMHO as a software developer I would treat a raised error as an unexpected and unrecoverable condition.

Unfortunately the Microsoft engineers did not allow SpecialCells to return Nothing. If there are no cells in that category it raises an error. I would suggest capturing the error and handling it immediately when it occurs, rather than using a GoTo. Try the following:

Sub isntworking()
    Dim Comm As Range
    Dim cl As Range
    Dim x As Integer
    With Worksheets("Sheet1")
           For x = 2 To 10
                Set iRange = .Range("A" & x & ":CV" & x)
                On Error Resume Next
                Set Comm = iRange.SpecialCells(xlCellTypeComments)
                If Err.Number = 0 Then
                   On Error GoTo 0
                   For Each cl In Comm ' iterate over all cells in rng - each of them contains a comment
                       Debug.Print cl
                   Next
               Else
                   On Error GoTo 0
               End If
        Next
    End With
End Sub

 

[Micron]

Not real sure what the goal is there but can say that generally speaking you don't attempt to use a property (e.g. .Value) and then trap an error because it isn't available or valid. Instead, you test its value first; e.g. >0, Is Nothing, Is Not Nothing, <>"", etc. and code executes according to the test result. An exception would be to try to use an object or property that may not exist - e.g. try to use Outlook and if it errors, create an instance of it (because you can't rely on it being loaded).

For your case it may be better to test if a comment exists, otherwise you are attempting to set a range object based on it having a comment, and if it doesn't, that will fail. This may help you with your question about error handling

On Error statement (VBA)

Office VBA reference topic

learn.microsoft.com

 

[6StringJazzer]

you test its value first; e.g. >0, Is Nothing, Is Not Nothing, <>"",

This is the textbook approach but does not work with SpecialCells. If there are no cells in the specified category, SpecialCells does not return Nothing. It raises an error. This is terrible language design and requires a clunky workaround. But here we are.

 

[RoryA]

But hasn't the error handler been reset?

Not quite. You have to clear the current exception state - usually by using a Resume statement - otherwise any further errors will be unhandled.

 

[Micron]

SpecialCells does not return Nothing

"Nothing" is for objects. Those were only meant to be examples as they apply. I don't know much about special cells but if it's a property then perhaps you test for its existence also, just like the .Value prpoerty - but test if it exists, not the value.

 

[RoryA]

SpecialCells is a method but it either returns a range or raises a run-time error, rather than gracefully returning Nothing. It could, as Jeff said, have been better implemented. (it's not alone in that respect!)

 

[6StringJazzer]

perhaps you test for its existence also, just like the .Value prpoerty - but test if it exists, not the value.

either returns a range or raises a run-time error

That is, you can't test it. You have to let it raise an error then deal with the error.