Error handling best practices

[Barb Reinhardt]

I'd like to get some ideas on error handling best practices. I prefer to
handle the errors where they occur using something like this

Set myWS = Nothing
on error resume next
Set myWS = ActiveWorkbook.Worksheets("Sheetabc")
On Error goto 0
if not myWS is nothing then
'Do Stuff
end if

I've also seen On Error GoTo ErrHandler with the error handler at the end.
I've been trying to update a workbook that has the latter and I'm finding
it's a pain to find the errors without making other code changes. Ideas
folks?

Thanks,
Barb Reinhardt

 

[Jacob Skaria]

A useful post
http://www.fmsinc.com/TPapers/vbacode/Debug.asp

If this post helps click Yes

 

[OssieMac]

Hi Barb,

I like to write error information to a text file because users can rarely be
relied upon to accurately translate the info to the Administrator. Also it is
not uncommon for users to not tell you about them at all. By appending the
info to a txt file you can interrogate the file occasionally and see how
things are going. The following is a simplified demo. You can probably gather
other info like username etc and maybe others in this forum would like to
contribute suggestions on lots more good info that can be gathered and
written to the file.

Following sample code should give you the idea.

Option Explicit

'Dimensioning variables in the declarations area saves having to
'repeat this for every sub.
Dim strModuleName As String
Dim strSubname As String

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo errorCall
'Need to assign module name and sub name to variables
'for each sub.
strModuleName = "Sheet1"
strSubname = "Private Sub Worksheet_Change"

'Required code here

'Following line included to produce an error
'for testing purposes. (Sheet4 non-existant)
Sheets("Sheet4").Select

Exit Sub

errorCall:
Call Error_Routine(strModuleName, strSubname, Err.Number, Err.Description)

End Sub


Place the following code in a standard module:

Sub Error_Routine(ParamModule, _
ParamSubName, ParamErrNo, ParamErrDescript)
'Called from On Error and writes
'error info to "Error Log.txt"

Dim strNow As String
Dim strPath As String
Dim strFilename As String
Dim strFileToOpen As String

strNow = Format(Now(), "dd mmm yyyy hh:mm AMPM")

MsgBox "Date and Time: " & strNow & vbCrLf & _
"Module: " & ParamModule & vbCrLf & _
"Procedure: " & ParamSubName & vbCrLf & _
"Error Number: " & ParamErrNo & vbCrLf & _
"Error Description: " & ParamErrDescript & vbCrLf & _
vbCrLf & _
"Error information written to file Error Log.txt"

strPath = ThisWorkbook.Path

strFilename = "Error Log.txt"

strFileToOpen = strPath & "\" & strFilename

'If file exists open for append otherwise
'this command creates text file.
Open strFileToOpen For Append As #1

Print #1, " Date and Time: " & strNow
Print #1, " Module: " & ParamModule
Print #1, " Procedure: " & ParamSubName
Print #1, " Error Number: " & ParamErrNo
Print #1, "Error Description: " & ParamErrDescript
Print #1, " "
Print #1, "End of error message"
Print #1, "**********************************************"
Print #1, " "
Close #1

End Sub

 

[JP]

Keeping your error handling code separate from the logic of your
procedure is good programming practice.

You don't want "On Error" statements sprinkled throughout your code,
or multiple IF statements trying to test if errors occur. You don't
always know where errors can occur, so trying to hard code them leads
to hard-to-read code.

What I like to do is put "On Error GoTo ErrorHandler" at the top of a
procedure, then in the ErrorHandler section, you can test for what
type of error occurred.

i.e.
Select Case Err.Number
Case 7 ' Out of memory
Msgbox "Out of memory"
Case 11 ' Division by zero
Msgbox "Don't enter zero"
Case Else ' all other errors
Msgbox "An unspecified error occurred."
End Select

And so on. Of course, this approach requires that you are familiar
with what types of errors can occur, and what the error numbers are.

Having said that, I use your process sometimes. It depends on the
particulars of the procedure. In your particular case, you can
minimize the chance of an error occurring if you refer to the code
name of the sheet, not the sheet name. Sheet names are very easy to
change, but the code name stays the same.

HTH,
JP

 

[Patrick Molloy]

i addition, where you can, make assignments in functions that handle the
errors outside of your main code
, which IMHO gives better error control.
Every developer has their pet loves & hates, this is one of mu favourites.

so instead of this

Set myWS = ActiveWorkbook.Worksheets("Sheetabc")

I'd have

Set myWS = GetSheet("Sheetabc")
if myWS Is Nothing then
'handle no sheet problem here
end if


Function GetSheet(sheetname as string, optional bAdd as Boolean=False) As
Worksheet
on error resume next
SET GetSheet.Worksheets(sheetname )
if err.Number<>0 then 'sheet doesn't exist
err.clear
if bAdd then
SET GetSheet.Worksheets.Add
GetSheet.Name = "Sheetabc"
end if
End If
On Error goto 0
End Function


This means that the ANTICIPATED error is handled correctly by your code and
the main routine's error handler can be left to look after unanticipated
issues.