Error Handling construct

[Geoff]

Hi
From tests and 'Help' it seems I need to restate On Error GoTo
myErrorHandler after returning from each sub proc and after the On Error
Resume Next.
Is that correct or is there a better way to provide error handling
throughout Main?

T.I.A.

Geoff

Sub Main()

On Error GoTo myErrorHandler

blah

SubProc1 ''no error handler in this proc

On Error Resume Next
foundIt = Sheets(1).Find("xxx", , , xlWhole)
On Error GoTo 0

SubProc2 ''no error handler in this proc

blah

Exit Sub

myErrorHandler:

If Err.Number <> 0 Then
'''do something
Err.Clear
End If

End Sub

 

[James Snell]

On Error is procedural, so changing it in a child routine does not affect the
parent.

Proof of the pudding is in the eating though... So here's a sample.

Sub TestErr()
On Error GoTo errHandle
Call NaughtyChild
Err.Raise 2
MsgBox "error handler not called - oops"
End

errHandle:
MsgBox "error handler called - you do not need to restate the on error"
End Sub

Sub NaughtyChild()
On Error Resume Next
Err.Raise 1
Err.Clear
End Sub

 

[Geoff]

Ok so it must be the On Error Resume Next before the Find statement in Main
that seems to disable myErrorHandler. So I'm thinking I should restate the
handler at that point instead of the following On Error GoTo 0?

Geoff

 

[Bob Phillips]

There is no need to reset the error handlers on return from a sub-procedure.
If the sub-procedure has its own error handler, it will deal with its own
errors and the error handler is cleared on exit. If the sub-procedure does
not have its own error handler, the error will be handled by the currently
active error handler, i.e the last invoked error handler in a procedure in
the parent hierarchy.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Geoff]

I am clear about sub procs. But my post includes an On Error Resume Next and
On Error GoTo 0 around the Find statement. Resume Next permits a 'no find'
but also disables myErrorHandler? Therefore instead of Return to 0 I should
reinstate myErrorHandler?

Geoff

 

[James Snell]

Yes, that's correct.

On Error GoTo 0 means for vba to handle errors (usually report them to the
user) which means in subproc2 (as there is no error handling there) and once
subproc2 returns vba will be handling errors and not your error handler.

 

[Geoff]

Thank you - the example made it clear and allowed me to experiment.

Geoff

 

[James Snell]

Thinking again...

Probably the best way of handling it is instead of using the additional on
error statements is to look for the specific error codes that might come from
the find.

myErrorHandler:

Select Case Err.Number

Case Is = 0
' Err code is 0 so no error - this in itself is an error
Case Is = -1 ' change this for the error code you get from an error in the
find.
Err.Clear
Resume Next
Case Else
''' do what you were going to do with your error handler anyway
End Select


That's probably a better solution than using multiple on error statements.

 

[Bob Phillips]

The Resume Next and Goto 0 seem superfluous to me, but they have the effect
of killing the proper error handler.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

If your real question is about error handling, ignore this response.

But if you're real question is about using .find:

Option Explicit
Sub Main()
Dim FoundIt as Range

'I'd specify all the parms--I wouldn't want to use the
'parms from VBA's last find or the user's last find.
'and don't forget the Set statement
set foundit = Sheets(1).Find("xxx", , , xlWhole)

if foundit is nothing then
'not found
else
'was found
end if

End Sub

 

[Geoff]

Yes, see what you mean because 'no find' is specific whereas the what I'm
trying to protect against is a crash caused by unknown elements.

That's neat. Thanks again.

Geoff

 

[Geoff]

The real question was about error handling and I just put Find in to
illustrate.

However this is what I am doing with the real find. At this stage in the
project the code is simply establishing whether any of the possible
descriptions for salutation is in data pulled from an unopened workbook and
added to the xla wsheet for scrutiny. If found, a value is given for
assessment. There are other groups of alternatives for other headers in the
table. If all tests are passed then that particular workbook is passed for
further processing.

Geoff

Dim rnum as Long
Dim headertbl As Range
Dim foundSalutation As String
Dim realLastColumn As Long

With Sheets(1)
Set headertbl = .Range(.Cells(rnum + 1, 2), .Cells(rnum + 1, .Range("IV" &
rnum + 1).End(xlToLeft).Column))

foundSalutation = headertbl.Find("Title", , , xlWhole)
foundSalutation = headertbl.Find("Salutation", , , xlPart)
foundSalutation = headertbl.Find("Honorific", , , xlPart)
foundSalutation = headertbl.Find("Prefix", , , xlPart)
foundSalutation = headertbl.Find("Name", , , xlWhole)

If Not foundSalutation = "" Then .Cells(rnum + 1, realLastColumn).Offset(,
2) = 16

End With