On Error GoTo Statements to leave for the next person who maintains your code (1 Viewer)

[Rx_]

An example of the basic On Error Goto
Basically, give the error a name (handle) such as Err_Trap
Any name will do... OK Any Name?

In the old days, we had code testers, code reviews and such.
Now in the new Nike Age "just do it" we get an assignment and move on like a Gypsy. We write good code that works.
But, why not leave some Fortune Cookies for the next coder?

First a quick tutor about On Error - followed by the Fortune Cookie version

The On Error GoTo tells VBA to transfer execution to the line following the specified line label. Whenever an error occurs, code execution immediately goes to the line following the line label. None of the code between the error and the label is executed, including any loop control statements.

Private Sub DesignedToFail()
On Error GoTo Err_Trap
X =1
Y = 2
Z = 1+2 / 0
MsgBox "This will never display: " & Z, vbokonly, "My Existence is Futile"
Exit Sub
  Err_Trap:
  MsgBox "Your Code had an error " & Err.Description, vbokonly, "OK, Bonehead?"
End Sub

Fortune Cookie Version:
Private Sub DesignedToFail()
On Error GoTo Bar_Order_One_Bourbon_One_Scotch_and_One_Beer

Private Sub DesignedToFail()
On Error GoTo Wikipedia

Private Sub DesignedToFail()
On Error GoTo statement_is_frowned_upon_by_Ada_programmers

Private Sub DesignedToFail()
On Error GoTo Heaven ' Wait! Heaven is up and you NEVER NEVER use a GOTO to a upper line of code! Is this Proof nobody can goto Heaven?

Private Sub DesignedToFail()
On Error GoTo PointOfNoReturn


With that, the door is open for your submissions

 

[CJ_London]

I prefer to avoid goto's as much as possible so to trap errors I often use

 on error resume next
  
 x=1/0
 if err.number<>0 then
     'error handling here
 end if '-- or else, depending on program flow requirements

but if I was to use the goto's then I might use

goto yet_another_error_I_missed

goto oops

goto time_to_call_it_a_night

 

[smig]

I'll normaly go back from the error trap to the Exit point so I make sure I close any open RS or DB
In the msgBox there is an option for the end user to send me an email with the Routine name and the traped error.

Private Sub DesignedToFail()
On Error GoTo Err_Trap
X =1
Y = 2
Z = 1+2 / 0
MsgBox "This will never display: " & Z, vbokonly, "My Existence is Futile"

[COLOR="DarkRed"]ExitHere:[/COLOR]
' --- Close any open RS or DB before exit
Exit Sub


  Err_Trap:
  MsgBox "Your Code had an error " & Err.Description, vbokonly, "OK, Bonehead?"
   [COLOR="DarkRed"]resume ExitHere[/COLOR]
End Sub

 

[AnthonyGerrard]

I prefer to avoid goto's as much as possible so to trap errors I often use

 on error resume next
  
 x=1/0
 if err.number<>0 then
     'error handling here
 end if '-- or else, depending on program flow requirements

but if I was to use the goto's then I might use

goto yet_another_error_I_missed

goto oops

goto time_to_call_it_a_night

I do it the exact opposite - only if there is an error , I know I can safely ignore do I have resume next. ie something like menubar item not being found.

Otherwise it used go to to do error handling, or exiting of function/sub.

Do you have to explicitly check after a line of code for an error? That doesn't seem advantageous to me. Bit like going back from TRY CATCH in TSQL.

Can you tell me the advantages of doing it your way?

 

[CJ_London]

It's really just personal preference.

I do use a 'catchall' error handler so I can find out what errors have been generated and not trapped - but that goes to an error handler which emails me with the error details . Once I have determined the nature of the error I fix it as part of the program flow (or perhaps modify input routines)- rather than having to drop down to the bottom of the procedure and then resume next and scroll back up again.

For me there are three benefits

1. readability of the code
2. using resume next without monitoring what errors you get can also mask errors you haven't trapped - unless you also use on error goto 0
3. it indicates where an error can occur

take this code

X=0
Y=0
If 1 / X > 1 Then
'do something
else
'do something else
End If

If 1 / Y < 1 Then
'do another thing
else
'do something else
End If

will generate a divide by zero error and none of the if statement is executed - the code moves to End If. So if as in this example you have two potential divide by zero errors - if you have error handling at the bottom - which bit of code generated the error? You might want to handle each differently.

Agreed you can wrap in another if statement

 X=0
 if X=0 then
     msgbox "dividebyzero"
 elseif 1/X>1 then
 ....

but it's just the way I deal with it

 

[AnthonyGerrard]

It's really just personal preference.

I do use a 'catchall' error handler so I can find out what errors have been generated and not trapped - but that goes to an error handler which emails me with the error details . Once I have determined the nature of the error I fix it as part of the program flow (or perhaps modify input routines)- rather than having to drop down to the bottom of the procedure and then resume next and scroll back up again.

For me there are three benefits

1. readability of the code
2. using resume next without monitoring what errors you get can also mask errors you haven't trapped - unless you also use on error goto 0
3. it indicates where an error can occur

take this code


will generate a divide by zero error and none of the if statement is executed - the code moves to End If. So if as in this example you have two potential divide by zero errors - if you have error handling at the bottom - which bit of code generated the error? You might want to handle each differently.

Agreed you can wrap in another if statement

 X=0
 if X=0 then
     msgbox "dividebyzero"
 elseif 1/X>1 then
 ....

but it's just the way I deal with it

Thanks interesting,

How do you use a general catch all and the on error resume next? Doesn't one exclude the other (maybe it doesn't?!), how do you decide to turn one on the other off.

Maybe you could post a real life example of a sub/func ?

 

[CJ_London]

How do you use a general catch all and the on error resume next?

by calling it when required.

here is a snippet of code - quite short but demonstrates the principle

 Dim fso As Object
Set fso = VBA.CreateObject("Scripting.FileSystemObject")
 If MsgBox("Do you want to backup this file?", vbYesNo) = vbYes Then
    On Error Resume Next
    fso.copyfile CurrentDb.Name, "C:\Dev" & Mid(CurrentDb.Name, InStrRev(CurrentDb.Name, "\")), False
    If Err.Number = 58 Then 'file already exists
        If MsgBox("File already exists - do you want to overwrite?", vbYesNo) = vbYes Then
            fso.copyfile CurrentDb.Name, "C:\Dev" & Mid(CurrentDb.Name, InStrRev(CurrentDb.Name, "\")), True
        End If
    Else
        On Error GoTo 0
    End If
End If

although it doesn't need the else bit for this particular case

Another way it could be written is using a case statement

 Dim fso As Object
Set fso = VBA.CreateObject("Scripting.FileSystemObject")
 If MsgBox("Do you want to backup this file?", vbYesNo) = vbYes Then
    On Error Resume Next
    fso.copyfile CurrentDb.Name, "C:\Dev" & Mid(CurrentDb.Name, InStrRev(CurrentDb.Name, "\")), False
    Select Case Err.Number 
         case 0
              on error goto 0
         case 58 Then 'file already exists
              If MsgBox("File already exists - do you want to overwrite?", vbYesNo) = vbYes Then fso.copyfile CurrentDb.Name, "C:\Dev" & Mid(CurrentDb.Name, InStrRev(CurrentDb.Name, "\")), True
         case else 'untrapped error - email to developer
              if SendError("subname", "EL1", err.number, err.description)<>0 then exit sub 'error determined as fatal or indeterminate so exit sub     
     end Select
End If

it also doesn't have any error handling around the second copyfile, which on reflection, it should do - if for example there was a disk failure....

 

[The_Doc_Man]

The syntax I would REALLY want to see in VBA is

On Repeated Error GoTo Nearest_Bar_To_Drown_My_Sorrow

On Repeated Error Then Tear_Out_What_Is_Left_Of_Your_Hair

On Success Then Fall_Over_In_Total_Surprise

And let's not forget direct testing of conditions using non-trap syntax

If This_Code_Works Then GoTo Buy_A_Lottery_Ticket

 

[Rx_]

Public Sub PayRollDayPrint()
' Variables
On Error GoTo PreventDelay
' Code
Exit Sub
PreventDelay:
Call BankToHaveThemSendChecks()
End Sub

 

[Frothingslosh]

I normally just use ProcedureName_Err, since my old boss was renowned for her lack of anything resembling a sense of humor, but lately I've grown fond of naming my error procedures something like Just_Shoot_Me_Now and Why_God_Why.

 

[AnthonyGerrard]

by calling it when required.

here is a snippet of code - quite short but demonstrates the principle

 Dim fso As Object
Set fso = VBA.CreateObject("Scripting.FileSystemObject")
 If MsgBox("Do you want to backup this file?", vbYesNo) = vbYes Then
    On Error Resume Next
    fso.copyfile CurrentDb.Name, "C:\Dev" & Mid(CurrentDb.Name, InStrRev(CurrentDb.Name, "\")), False
    If Err.Number = 58 Then 'file already exists
        If MsgBox("File already exists - do you want to overwrite?", vbYesNo) = vbYes Then
            fso.copyfile CurrentDb.Name, "C:\Dev" & Mid(CurrentDb.Name, InStrRev(CurrentDb.Name, "\")), True
        End If
    Else
        On Error GoTo 0
    End If
End If

although it doesn't need the else bit for this particular case

Another way it could be written is using a case statement

 Dim fso As Object
Set fso = VBA.CreateObject("Scripting.FileSystemObject")
 If MsgBox("Do you want to backup this file?", vbYesNo) = vbYes Then
    On Error Resume Next
    fso.copyfile CurrentDb.Name, "C:\Dev" & Mid(CurrentDb.Name, InStrRev(CurrentDb.Name, "\")), False
    Select Case Err.Number 
         case 0
              on error goto 0
         case 58 Then 'file already exists
              If MsgBox("File already exists - do you want to overwrite?", vbYesNo) = vbYes Then fso.copyfile CurrentDb.Name, "C:\Dev" & Mid(CurrentDb.Name, InStrRev(CurrentDb.Name, "\")), True
         case else 'untrapped error - email to developer
              if SendError("subname", "EL1", err.number, err.description)<>0 then exit sub 'error determined as fatal or indeterminate so exit sub     
     end Select
End If

it also doesn't have any error handling around the second copyfile, which on reflection, it should do - if for example there was a disk failure....

Thanks. Sorry yes on general catch all - I meant a one off statement like a on error go to, but you use similar called maybe multiple times.

For your repeated error being handled differently in different sections of code - I just use a "Flag" string variable - which then can route a error handler for the same err in different direction based on the flag.

I like the readability of your code, but don't like that you have to seemingly write it so many times. What happens for unhandled errors - that's my main fear.

The handling the second copy error you may have, would seem far easier in error handler to me.

 

[CJ_London]

have to seemingly write it so many times

Only seems like that. For me ease of reading is more important than one or two less lines of code and in a more complex example one could have the potential for many errors - so these really just get split up through the code rather than all condensed at the bottom.

But do like your idea of the flag variable

 

[sneuberg]

I'll normaly go back from the error trap to the Exit point so I make sure I close any open RS or DB
In the msgBox there is an option for the end user to send me an email with the Routine name and the traped error.

Private Sub DesignedToFail()
On Error GoTo Err_Trap
X =1
Y = 2
Z = 1+2 / 0
MsgBox "This will never display: " & Z, vbokonly, "My Existence is Futile"

[COLOR="DarkRed"]ExitHere:[/COLOR]
' --- Close any open RS or DB before exit
Exit Sub


  Err_Trap:
  MsgBox "Your Code had an error " & Err.Description, vbokonly, "OK, Bonehead?"
   [COLOR="DarkRed"]resume ExitHere[/COLOR]
End Sub

I found that you have to be careful about closing RS and DB like this. If closing them can cause an error, which happens if they are already closed, you have an infinite loop. I had to test like:

If Not db Is Nothing Then
        db.Close
 End If

to avoid that.