Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling construct
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling construct
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling construct
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 "James Snell" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling construct
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling construct
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 "Bob Phillips" wrote: 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling construct
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" wrote: 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 "James Snell" wrote: 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" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling construct
Thank you - the example made it clear and allowed me to experiment.
Geoff "James Snell" wrote: 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" wrote: 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 "James Snell" wrote: 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" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling construct
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. "Geoff" wrote: 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 "James Snell" wrote: 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" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling construct
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) "Geoff" wrote in message ... 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 "Bob Phillips" wrote: 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" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling construct
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 wrote: 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 -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling construct
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 "James Snell" wrote: 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. "Geoff" wrote: 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 "James Snell" wrote: 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" wrote: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling construct
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 "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Handling in Each Sub | Excel Programming | |||
Handling #NUM! error | Excel Worksheet Functions | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
Error handling with a handling routine | Excel Programming | |||
Error Handling | Excel Programming |