[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBA Error handler not trapping error.

Posted on 2011-05-09
24
Medium Priority
?
511 Views
Last Modified: 2012-05-11
When testing this code I always send the email.  The first email is sent perfectly, but after the second is sent an error arises that is not caught by the error handler.  Why is the error trapped in the first Loop, but not the second?
On Error GoTo ErrHandler

Dim appOutlook As Outlook.Application
Dim msg As Outlook.MailItem
Dim i As Long

Set appOutlook = GetObject(, "Outlook.Application")

i = 1

Do Until i = 3

   i = i + 1
    
     Set msg = appOutlook.CreateItem(olMailItem)
         msg.To = "email@nowhere.com"
         msg.Subject = Test
         msg.Display True


'Determine if email sent
    If msg.Sent = False Then
        MsgBox "Msg Not Sent"
       GoTo SkipDidSend
    End If

DidSend:

     MsgBox "Msg Sent"

    
SkipDidSend:


Loop
        
        
ErrHandlerExit:
   Exit Sub
   
ErrHandler:

   If Err.Description = "The item has been moved or deleted." Or Err.Number < 0 Then
      GoTo DidSend
   Else
      MsgBox "Err No. " & Err.Number & vbCrLf & Err.Description
      GoTo ErrHandlerExit
   End If

Open in new window

0
Comment
Question by:T1080
  • 11
  • 9
  • 3
  • +1
24 Comments
 
LVL 75
ID: 35722613
What error?  Where in the loop?

mx
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35722841
You can't handle errors like that. VBA thinks you are still "handling"the error and throws up it's own debugging messages as long as you are in that mode.

Use Resume Next in the error handler to continue.

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35722863
Like this:

On Error GoTo ErrHandler

Dim appOutlook As Outlook.Application
Dim msg As Outlook.MailItem
Dim i As Long

Set appOutlook = GetObject(, "Outlook.Application")

i = 1

Do Until i = 3

   i = i + 1
   
     Set msg = appOutlook.CreateItem(olMailItem)
         msg.To = "email@nowhere.com"
         msg.Subject = Test
         msg.Display True


'Determine if email sent
    If msg.Sent = False Then
        MsgBox "Msg Not Sent"
       GoTo SkipDidSend
    End If

     MsgBox "Msg Sent"

SkipDidSend:

Loop
       
       
ErrHandlerExit:
   Exit Sub
   
ErrHandler:

   If Err.Description = "The item has been moved or deleted." Or Err.Number < 0 Then
      Resume Next
   Else
      MsgBox "Err No. " & Err.Number & vbCrLf & Err.Description
      Resume Next
   End If

Kevin
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 75
ID: 35722882
Warning! Alert! Excel guy has entered the Access Zone !  Pat down required! ha ha

mx
0
 

Author Comment

by:T1080
ID: 35722917
It errors on the line msg.sent = false.  The error is "The item has been moved or deleted."  It only errors on this line if the email is sent.  The first email that is sent in the loop will be trapped by the error handler, but for some reason the second email will not be trapped.  Run the code, send the emails and you will see the error.  I've noticed I have the same issue for any code that errors in a loop statement.  See my attached code, it too errors in the second loop even though I trap for the Error Number.  What's up with this?
On Error GoTo ErrHandler

Dim i As Long
Dim varN As Variant

i = 1

Do Until i = 3


   i = i + 1
    


varN = 1 / 0


MyLocation:


Loop
        
        
ErrHandlerExit:
   Exit Sub
   
ErrHandler:

   If Err.Number = 11 Then
      GoTo MyLocation
      
   Else
      MsgBox "Err No. " & Err.Number & vbCrLf & Err.Description
      GoTo ErrHandlerExit
   End If

Open in new window

0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35722938
Access sucks! Excel can do anything Access can do and a whole lot more! Let's rumble!

What kinda boots you wearing today?
0
 

Author Comment

by:T1080
ID: 35722953
Zorvek:

I don't want to resume next if I hit the error.  I need to go to a certain location.  In this case the location is SkipDidSend.
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 35722960
You can't use the error handler like that. In fact, you really shouldn't be using the error handler (see below). Here is how I would implement your logic:

Public Sub Test()

    Dim appOutlook As Outlook.Application
    Dim msg As Outlook.MailItem
    Dim i As Long
    Dim ErrorNumber As long
    Dim ErrorDescription As String

    Set appOutlook = GetObject(, "Outlook.Application")
    i = 1
    Do Until i = 3
        i = i + 1
        Set msg = appOutlook.CreateItem(olMailItem)
            msg.To = "email@nowhere.com"
            msg.Subject = Test
            On Error Resume Next
            msg.Display True
            ErrorNumber = Err.Number
            ErrorDescription = Err.Description
            On Error GoTo 0
            If ErrorDescription = "The item has been moved or deleted." Or ErrorNumber < 0 Then
            Else
               MsgBox "Err No. " & ErrorNumber & vbCrLf & ErrorDescription
               Exit Sub
            End If
            'Determine if email sent
            If msg.Sent = False Then
                MsgBox "Msg Not Sent"
            Else
                MsgBox "Msg Sent"
            End If
        End If
    Loop

End Sub

Why global Visual Basic error handling is useless:

Visual Basic error handling has three different modes: ignore all errors, jump to a specific location in the code when an error occurs, or leave the error alone and let Visual Basic figure out how to handle it (this means a debug dialog presented to the user). Clearly the "ignore all errors" is extremely dangerous and should be avoided except in very specific and isolated cases. If this mode is set and the program allowed to run unchecked then, while the first few errors remain hidden from the user, the user experience is bound to be disastrous in the end when the application coughs up all manner of meaningless garbage with no explanation as to why it is what it is.

Jumping to a specific location in the code is the most reasonable approach to error handling but can result in code that is more difficult to maintain. This mode of error handling watches for any error and, when an error occurs, stops execution at the point of the error and starts execution at a predefined location presumably designed to handle errors. The basic problem with this approach is that the moment the error is trapped and the execution shifted to the designated error handler, all aspects of the error except for the error code and description are lost. This means that all the error handler can do is display the error (which is exactly what the default Visual Basic error handler does) but without the Debug button. The error handler has two choices at this point: it can execute a Resume statement which sends the execution back to the offending line of code to be retried, or it can abort the current process. Therefore the more global the error handling (the higher in the calling chain the error handler is put into effect) the more difficult it is to determine the cause of the error and address it. Simply put, if the error handler is not placed in rather close proximity to the offending line of code it is impossible to determine the cause of the error without resorting to other methods of discovery. So the only real effective way to use this type of error handling is to use it locally at or near the potential point of failure.

That leaves the third method which is to do nothing and let Visual Basic handle the error by displaying the debug dialog. While this is the most troublesome from the user's perspective in the short term (at the moment the error occurs), it is the best method to use if the end goal is a stable and effective application because the user, if suitably trained, can provide exact details of the error by clicking the Debug button and taking a screen shot of the offending line of code.

Ultimately, the best code will not encounter any errors and will handle any that it does encounter with user friendly results. The fastest path to this end is to pre-flight wherever necessary, handle possible errors locally, and let Visual Basic handle any errors which are not expected but need to be handled in the next release of the application. Trying to mask any unexpected errors to protect the user's experience will only make it that much harder to resolve the cause of the error and delay fixing the problem.

Kevin
0
 
LVL 75
ID: 35722997
boots ... fake alligator from TJ in 2002.  Pointed toes to kick your ....

"Why global Visual Basic error handling is useless:"
Really now!  Right.
Then you haven't seen and/or use this:

http://www.everythingaccess.com/vbwatchdog.htm

mx
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35723031
Ha!

Yep, you gotta pay some schmod to make error handling useful!

Have you tried the catch-throw error handling in C++/C#? Now THAT'S error handling!

VBA error handling blows chunks.

That's Excel-speak for it's useless.

I'm wearing my black Harley boots.

Meet you in the middle? San Luis Obispo?
0
 
LVL 75
ID: 35723090
SLO ... when?
Wayne sort of implemented Catch-Throw of sorts.
Dude ... you only have ONE pair of boots ... those biker boots!
Got one coming down from Boise ID over mem day ...

0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35723132
Ya, ya...but they're bad-ass boots.

When you buy a boot you really ought to get a second for the other foot at the same time. Shipping charges are going through the roof!

Unless you are in to that new trend: wearing different attire on each foot. Kinda stupid if you ask me but then you Access guys aren't known for a lot of common sense.
0
 
LVL 75
ID: 35723294
no no no.  By 'one' I meant feeeMale ....

0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35723324
Oh! I see! Sending you an email ;-)
0
 
LVL 16

Expert Comment

by:HooKooDooKu
ID: 35724124
@T1080:

To repeat what zorvek was trying to say... once an error handler has been invoked, the current error handler (the On Error statement) is nullified until the error is cleared.  To clear the error, you must use the Resume command.

Now you can use 'Resume'by itself, in which case the logic jumps to the line of code that caused the error and tries it again.  

But you can also pair 'Resume' with a line label.  The will clear the error and jump to the specified line of code (at least that's how VB6 works).

So you need to replace 'Goto DidSend' with 'Resume DidSend'.  That will reactivate your 'On Error' statement AND jump to the desired line of code.
0
 
LVL 75
ID: 35724171
"To clear the error, you must use the Resume command."

Err.Clear

mx
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35724195
Joe,

That doesn't work.

It has to be a Resume ... statement.

BUT, using any kind of "goto" logic is so passe and considered very bad form since the 70's. It's that kind of coding that resulted in the term "spaghetti code" (http://www.answers.com/spaghetti+code?gwp=11&ver=2.4.0.651&method=3).

Just saying!

Kevin
0
 
LVL 75
ID: 35724216
Of course Err.Clear works ... note the word CLEAR.

I think France is affecting your mind, lol.

mx
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35724251
You know, I really don't have time for this wankiness. But, if I must educate the confused...

Run the following and watch VB take over error reporting:

Public Sub Test()

    On Error GoTo ErrorHandler
    Do
        Debug.Print 1 / 0
Continue:
    Loop

ErrorHandler:
    Err.Clear
    GoTo Continue

End Sub

Kevin

Excel Rocks!
0
 
LVL 75
ID: 35724579
Well ... Guess we're gonna  have to get serious here:

Public Function mExcelBlowsAndAintNoDB() As Boolean
   
    On Error GoTo ErrHandler
    Dim appOutlook As Outlook.Application
    Dim msg As Outlook.MailItem
    Dim i As Long
    Set appOutlook = GetObject(, "Outlook.Application")
    i = 1
    Do Until i = 3
        i = i + 1
        Set msg = appOutlook.CreateItem(olMailItem)
        msg.To = "kj@francehereicome.com"
        msg.Subject = "TEST"
        msg.Display True

       'Determine if email sent
        On Error Resume Next
        If msg.Sent = False Then
            Select Case Err.Number
                Case 0          'no error occurred (happens if user closes email window instead of sending
                    On Error GoTo ErrHandler    'reset general error handler
                Case Is < 0     'ignore this error
                    Debug.Print Err.Number & "  " & Err.Description
                    Err.Clear
                    On Error GoTo ErrHandler    'reset general error handler
                Case Else
                    MsgBox "An unexpected error has occurred - Message NOT Sent" & vbCrLf & "Err No. " & Err.Number & vbCrLf & Err.Description
                    GoTo mExcelBlowsAndAintNoDB_Exit
            End Select
        End If
    Loop
mExcelBlowsAndAintNoDB_Exit:
    Exit Function
ErrHandler:
    MsgBox "An unexpected error has occurred - Err No. " & Err.Number & vbCrLf & Err.Description
    GoTo mExcelBlowsAndAintNoDB_Exit
End Function
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35725565
That doesn't prove anything dude. You execute an On Error Resume Next which turns off error handling then you execute the Err.Clear method when you get an error. Most actions clear the error handler including On Error GoTo 0 which is why I save off the values before doing anything else. All it does is set the error handler properties to zero and empty.

When you kick in the error handler by branching you have to execute a Resume ... statement or exit the routine. There is no other way to keep VB from capturing and handling new errors on it's own.

Excel and SQL Server. Who needs Access?
0
 
LVL 75
ID: 35725589
Dude ... it works ... try it.

"You execute an On Error Resume Next which turns off error handling "
Exactly.  Then you test for what error occurred and act accordingly ... the Select Case.  Plus, I eliminated all those unnecessary Line labels and consequent jumping around.  It's all handled *inside* the Loop ... only jumps out if an *unexpected* error occurs ... Case Else.

mx
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35725611
I know it works. You basically proposed the same thing I did. It's a very simplistic usage of the error handler to handle errors as they occur without the excessive jumping around you get with the On Error GoTo ... statements.

I am contesting your statement above:

    "To clear the error, you must use the Resume command." Err.Clear

Err.Clear does not negate or otherwise turn off the On Error GoTo ... mode. The only way to do so is to use Resume ... or exit the scope. In fact, you don't even need the Err.Clear statement in your code. It does nothing to control execution flow.
0
 

Author Closing Comment

by:T1080
ID: 35728626
Thank you!!
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question