Error handling not working as expected

[vbaanalyst]

I have a simple bit of code for removing blank rows from a data set - it totals each row and tags it with “remove me” if the total is zero– but despite setting up error handling procedures I can’t stop it giving me an error when there are strings in the data and I want to know why!

Sub blank_tagger()

Set my_range = Range("A1:C10")
count_rows = my_range.Rows.Count
count_columns = my_range.Columns.Count

For i = 1 To count_rows
Total = 0
On Error GoTo String_error
For j = 1 To count_columns
Total = Total + Cells(i, j)
Next j
If Total = 0 Then
my_range.Cells(i, 1) = "remove me"
End If
String_error:
Err.Clear
Next i

End Sub

When one of the cells in my range is a text string then I would expect and error since you can’t add a string to a number and sure enough without error handling I receive is “error 13: type mismatch”. The problem is even ‘with’ error handling I receive this message!

I’m not looking for a workaround (using worksheetfunction.sum for example) as the code is fairly trivial – I just want to work out why the error handling doesn’t work!

Sample Data:
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD="align: right"]8 [/TD]
[TD] string[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] string[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[/TR]
</tbody>[/TABLE]

 

[NdNoviceHlp]

Eric is this supposed to error (note change to Err.Clear/Resume Next)?

Sub test()
On Error GoTo ErrorHandler
    For i = 1 To 2
    Err.Raise 13
ErrorHandler:
        If Err Then
        MsgBox Err.Number
        Err.Clear
        'Resume Next
        End If
    
    Next i
    
End Sub

Gives me a type mismatch error on loop 2. Craps out on..

Err.Raise 13

Well I'm at it, HalfAce I don't get the point of having this part in your code if you can contribute a bit more of your time. Thanks. Dave

String_error:

ps. Haven't seen the -1 thing before ZVI. Not sure how it's different yet but Thanks

 

[ZVI]

ps. Haven't seen the -1 thing before ZVI. Not sure how it's different yet but Thanks

It was mentioned here more for fun.
But that undocumented feature works and sometimes can simplify the code.
For example, in error handler after On Error GoTo -1 you can use On Error Resume Next and then - some code with possible errors have to be ignored.

Usually I don't like suggesting undocumented stuff.
But not more than breaking up the loop by error label which is not good style

 

[erik.van.geit]

Eric is this supposed to error?

Of course: the error is "raised" on purpose to test the "on error" lines.
Programmers can use that line instead of things like
i = 1/0

 

[NdNoviceHlp]

Eric... I mean the error is not handled on the 2nd loop as it seems it should be. Thanks ZVI. Dave

 

[erik.van.geit]

Eric... I mean the error is not handled on the 2nd loop as it seems it should be.

Indeed, as we saw in previous examples; I tried to show the problem with a minimum of lines.
When you activate the line with "resume" it will work.

 

[shg]

Here are my notes about error handling:

There are three ways that the code in an error handler could come to execute: To have execution transferred as the result of an error, to have execution transferred as the result of an Application.Goto statement, and to fall into it in straight-line execution.

• If you arrive via an error, VBA considers the code to be in an error handler, and
  • You cannot change the error handler in the error handler, and On Error Resume Next has no effect
  • It cannot handle its own errors; run-time errors cause the code to break
  • Resume statements behave as documented
• If you arrive via straight-line execution or a GoTo statement, VBA considers the code to be about halfway into an error handler:
  • You can change the active error handler
  • An error will cause the code to reenter the top of the error handler (the first time; see above)
  • The behavior of Resume statements, which cause run-time errors outside of an error handler, behave in a different way:
    • All executions of a Resume statement will reenter the error handler, perhaps resulting in an endless loop, so none should appear
    • The first execution of a Resume Next statement will reenter the error handler, and the second execution will fall through. I can’t think of a case where this is desirable behavior.

Here’s a typical example where an error handler gets fallen into:

On Error GoTo Oops
Application.EnableEvents = False
Range("A1").Value = "Bob"
Oops:
Application.EnableEvents = True

In general, for end-of-procedure error handling,

• The first error handler should be preceded with an Exit Sub (or Function)
• Each error handler should end with a Resume statement (Resume, Resume Next, or Resume statement#)

• The error handler should never be the target of Application.Goto

You cannot change the active error handler in an error handler, and an error in an error handler will not cause execution to transfer back to the beginning.

 

[HalfAce]

Actually, there is no point to that String_error: line. I started out with the code in the original post and when the tweaking was done that line got overlooked for deletion.

 

[NdNoviceHlp]

A very informative thread. Thanks for fixing up that cognitive dissonance thing HalfAce. Dave