HELP: error handling in VBA

[sam]

Hi All,

Why do we use "On error resume next" shouldnt we be resolving those errors.

For eg: if we are sending important email notifications through excel VBA
which affect management decisions, shouldnt we be avoiding to user "On error
resume next"? IF for some reason there is an error and an email is not sent
it might affect a lot of things.

My concern is: how do we handle error is this situations? is there a better
way to handle errors, rather than using "On error resume next" statement?

Thanks in advance.

 

[Paul Robinson]

Hi
Use "on error resume next" when you are not bothered what the error is
or want to use the error. Call a sub if you want to handle the error
or use goto.
As an example of using "on error resume next", if you want to add a
comment to a cell and put in some text you may not be bothered if the
comment and text exists already. In that case, the error is thrown
(but ignored) if the comment already exists but is not thrown if there
is no comment there, and you can add it. Your code is simpler - you
don't need to check the comment is there. This principle can be used
for lots of things - do variables exist, do range names exist yet, do
sheets exist yet etc.
regards
paul

 

[Bob Phillips]

On Error Resume Next can be very useful if we want test a certain condition
that may or may not be met, and then test the results afterwards. That is
testing for predictable errors that we don't want to crash and burn on.

For instance, suppose we intend to add a worksheet, but it may already have
been added. We can use code like this

On Error Resume Next
Set ws = Worksheets("mySheet")
On Error Goto 0
If ws Is Nothing Then

Set ws = Worksheets.Add
ws.Name = "mySheet"
End If

'then do stuff with ws

So as you can see, it is a useful technique to help us test things. The
important thing is to make sure that the scope of an On Error Resume Next is
very limited, as I did above by restricting it to just one line of code, so
that real errors don't just get ignored.

The better way is proper error handling, like this

On Error GoTo errHandler

'do stuff like sending mail

Exit Sub 'so we don't drop into the error handler

errHandler:
MsgBox "Unexpected error" & vbNewline & _
"Error: " & Err.Number & ", " & Err.Description, _
vbOkOnly & vbCritical, "My App - Error"

 

[J_Knowles]

Here's how I trap errors.

Sub ErrorTesing()

On Error GoTo ErrStop

' enter vba code here like - email code

'Note: you do not have to put Exit Sub
'the error free code passes through the ErrStop:
'& ends on End Sub

ErrStop:
If Err Then MsgBox ("Error Occurred " & Err)
End Sub

If an error is detected in your code it will be displayed in a MsgBox
"Error Occurred xxx"

where xxx is the error number

HTH,