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

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

If this post helps click Yes
---------------
Jacob Skaria


"Barb Reinhardt" wrote:

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

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

On Jul 3, 6:46*am, Barb Reinhardt
wrote:
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

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.

"Barb Reinhardt" wrote in message
...
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