Hi Graham and Nd,
OK, I'll offer a little more detail about the way that error-handling operates (as I understand it, anyway
).
Custom error-handling
We invoke our own custom error-handling by use of the On Error ... statement (you really should read the VBE Help on this subject as its quite good). We return contol of error-handling to Excel by use of the On Error GoTo 0 statement.
On Error Resume Next
We use this when we want Excel to ignore any errors that arise. That is, On an Error arising code execution will Resume on the Next line of code.
This should be used when you anticipate that an error may arise at a particular point in your code but you do not want the error to be fatal (that is, to halt code execution). A common use of this approach is when setting object variables. We then test whether the object is Nothing to determine the success of the assignment. For example:
Code:
Sub Oops1()
Dim ws As Worksheet
On Error Resume Next
'invoke custom error-handling - continue to line after error-causing line
Set ws = ThisWorkbook.Worksheets("Nosuchsheet")
'cause an error
On Error GoTo 0
'return error-handling to Excel
If ws Is Nothing Then
MsgBox "Worksheet not found"
Else
MsgBox "Found it!"
End If
'test whether or not the ws variable was set
End Sub
Note how error-handling is passed back to Excel immediately after the line that we suspect may cause an error. I would not advocate the approach adopted by those whose idea of error-handling is to simply add an 'On Error Resume Next' at the start of their routine and never return contol to Excel. Sure, this will suppress the error messages but its unlikely that your code will continue to execute as intended.
On Error GoTo ...
The alternative to simply continuing code execution is to jump to some error-handling code.
This code is best placed at the end of your Sub or Function and should be preceded by an 'Exit Sub' instruction to make sure that the code isn't executed automatically whenever the end of the procedure is reached.
Once Excel has entered the Error state, to continue the analogy used above, it must be reset. This is achieved by use of the Resume statement. You can Resume (return to the error line), Resume Next (return the line after the error line), or Resume ... (specify where the code should resume from).
In the example below, the use of Resume ensures that the second error is also processed.
Code:
Sub Oops2()
Dim ws As Worksheet
On Error GoTo errhandler
'invoke custom error-handling - go to our specified line
Set ws = ThisWorkbook.Worksheets("Nosuchsheet")
'cause an error
Set ws = ThisWorkbook.Worksheets("Nosuchsheeteither")
'cause another error
On Error GoTo 0
'return error-handling to Excel
MsgBox "Finished"
Exit Sub
'ensure error-handler isn't processed at the end of the routine
errhandler:
MsgBox "An error has arisen : " & Err.Number & vbNewLine & Err.Description
'advise user of the error
Resume Next
'return code execution to the line after the error-causing line
End Sub
This is in contrast to the situation below where Err.Clear is used and then code execution is passed back to the main routine via a GoTo statement. The Err properties are cleared, as demonstrated by the Messagebox, but the second error is still fatal.
Code:
Sub Oops3()
Dim ws As Worksheet, bSecond As Boolean
On Error GoTo errhandler
'invoke custom error-handling - go to our specified line
Set ws = ThisWorkbook.Worksheets("Nosuchsheet")
'cause an error
seconderror:
bSecond = True
'indicate this is the second part
Set ws = ThisWorkbook.Worksheets("Nosuchsheeteither")
'cause another error
On Error GoTo 0
'return error-handling to Excel
theend:
MsgBox "Finished"
Exit Sub
'ensure error-handler isn't processed at the end of the routine
errhandler:
MsgBox "An error has arisen : " & Err.Number & vbNewLine & Err.Description
'advise user of the error
Err.Clear
'clear the err properties
MsgBox "An error has arisen : " & Err.Number & vbNewLine & Err.Description
'confirmation that the properties are cleared
If bSecond = True Then Resume theend
GoTo seconderror
'the Clear instruction will not have reset the error-checking,
'we have used GoTo rather than Resume,
'the second error will be fatal
End Sub
Another common mistake is to add a further On Error instruction within the error-handler itself without having reset the first error. This is shown in the following example:
Code:
Sub Oops5()
Dim ws As Worksheet
On Error GoTo errhandler
'invoke custom error-handling - go to our specified line
Set ws = ThisWorkbook.Worksheets("Nosuchsheet")
'cause an error
On Error GoTo 0
'return error-handling to Excel
theend:
MsgBox "Finished"
Exit Sub
'ensure error-handler isn't processed at the end of the routine
errhandler:
MsgBox "An error has arisen : " & Err.Number & vbNewLine & Err.Description
'advise user of the error
On Error GoTo errhandler2
'error-handler within the error-handler
Set ws = ThisWorkbook.Worksheets("Nosuchsheeteither")
'cause another error
'the first error has not been reset - the second handler will not be invoked
GoTo theend
errhandler2:
MsgBox "An error has arisen : " & Err.Number & vbNewLine & Err.Description
'advise user of the error
Resume theend
End Sub
We
could achieve this objective by using something like this:
Code:
Sub Oops6()
Dim ws As Worksheet
On Error GoTo errhandler
'invoke custom error-handling - go to our specified line
Set ws = ThisWorkbook.Worksheets("Nosuchsheet")
'cause an error
On Error GoTo 0
'return error-handling to Excel
theend:
MsgBox "Finished"
Exit Sub
'ensure error-handler isn't processed at the end of the routine
errhandler:
MsgBox "An error has arisen : " & Err.Number & vbNewLine & Err.Description
'advise user of the error
Resume reset
reset:
On Error GoTo errhandler2
'error-handler within the error-handler
Set ws = ThisWorkbook.Worksheets("Nosuchsheeteither")
'cause another error
GoTo theend
errhandler2:
MsgBox "An error has arisen : " & Err.Number & vbNewLine & Err.Description
'advise user of the error
Resume theend
End Sub
However, in my opinion, this approach is starting to resemble 'spaghetti' code - the flow of the code is becoming increasingly difficult to follow. It would be preferable to have just the one error-handler that is capable of handling a variety of errors.
Generic error-handler
If you have a larger project then, rather than adding detailed error-handling code to each and every Sub or Function, you may wish to consider using a generic error-handler.
This takes the form of a Sub or Function that is called by the routine that an error has arisen in. The routine passes details of the error and the procedure in which the error has arisen. The details can be written to a text file for later inspection.
The following is an example of how the error-handling is invoked in the Sub and of a typical generic error-handler. This stores the info in a text file, displays a message to the user, and gets a response from the user.
Code:
Sub TestErrorHandler()
On Error GoTo ErrorHandler
'
Kill "Anon-existantfilename"
'
ExitRoutine:
Exit Sub
ErrorHandler:
Select Case ProcessError("modErrorHandler : TestErrorHandler", Err.Number)
Case Is = True: Resume
'try again
Case Is = False: Resume ExitRoutine
'give up
End Select
End Sub
Function ProcessError(strProc As String, iErrNo As Integer) As Boolean
'use info from calling procedure to produce an error log and user message
Dim strMessage As String
Dim intStyle As Integer
Dim iFile As Integer
Const strTitle As String = "Warning : Error"
Const strMsg1 As String = "The following error has occurred:"
Const strMsg2 As String = "Do you wish to retry?"
strMessage = vbNewLine & "Error number : " & iErrNo & _
vbNewLine & "Error description : " & Error(Err.Number) & _
vbNewLine & "Procedure : " & strProc & vbNewLine
'the basic error info
iFile = FreeFile()
Open ThisWorkbook.Path & "\RPS Add-in_Error.Log" For Append As #iFile
Print #iFile, Now, ThisWorkbook.Name, strMessage
Close #iFile
'add the error message to the error log
strMessage = strMsg1 & vbNewLine & strMessage & vbNewLine & strMsg2
intStyle = vbExclamation + vbRetryCancel
'the full error message
ProcessError = _
(MsgBox(prompt:=strMessage, Buttons:=intStyle, Title:=strTitle)) = vbRetry
'display warning to user and get response (Retry or Cancel)
End Function
I have to thank XL-Dennis for first recommending this approach to me. I should also credit Mssrs Getz and Gilbert who authored the book "VBA Developers Handbook" which included details of the approach.
I hope that helps to clarify things a little.
