By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,400 Members | 1,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,400 IT Pros & Developers. It's quick & easy.

Error Trap in VBA

100+
P: 171
Hi All
I have a click event in an Access form which basically does three subs.
They are like:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Process_Click()
  2.  
  3. If MsgBox("Did you copy the data ?", vbCritical + vbYesNo, "Data") = vbNo Then
  4. Exit Sub
  5. Else
  6.  
  7. Process1
  8.  
  9. Process2
  10.  
  11. Process3
  12.  
  13.  
  14.  
  15. Me.lbl.Visible = False
  16. DoCmd.Hourglass False
  17.  
  18. If IsNull(DLookup("NotinMaster", "lcmstr", "NotinMaster")) Then
  19. DoCmd.OpenForm "FinalProcess"
  20. Else
  21. MsgBox "New codes found in data. Please update 'Codes' Table and process again", vbInformation + vbOKOnly, "New Codes"
  22. DoCmd.OpenForm "lcadd"
  23. Exit Sub
  24.  
  25. End If
  26.  
  27. End If
  28.  
  29. Me.lbl.Visible = False
  30. DoCmd.Hourglass False
  31.  
  32.  
  33. End Sub
I like to stop the code execution if there is an error in any of the subs and pop up a message box.
The process1 sub opens an excel template to write some data. So if error occurred then kill excel application as well.
Hope I explained well.
Jan 28 '15 #1

✓ answered by twinnyfo

You could have a class-specific global Boolean variable that is set to true on the event of any error. Then, if Process1 generates an error, it returns to the main sub. As soon as you return to the main sub, check the status of the variable. If it is true, then exit the sub.

Example:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private fErr as Boolean
  5.  
  6. Private Sub MainSub()
  7. On Error GoTo EH
  8.     Process1
  9.     If fErr Then GoTo ExitSub
  10.     Process2
  11.     If fErr Then GoTo ExitSub
  12.     Process2
  13.     If fErr Then GoTo ExitSub
  14.  
  15. ExitSub:
  16.     Exit Sub
  17.  
  18. EH:
  19.     MsgBox "You had an error!" & vbcrlf & vbcrlf & _
  20.         Err.Number & ": " & Err.Description
  21.     Exit Sub
  22. End Sub
  23.  
  24. Private Sub Process1
  25. On Error GoTo EH:
  26.     'Your Code
  27.     Exit Sub
  28. EH:
  29.     MsgBox "You had an error!" & vbcrlf & vbcrlf & _
  30.         Err.Number & ": " & Err.Description
  31.     Exit Sub
  32. End Sub
Hope this hepps!

Share this Question
Share on Google+
11 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,482
rajeevs,

I always recommend adding error trapping/handling on all procedures within a project. The basics are as follows:

Expand|Select|Wrap|Line Numbers
  1. Private Sub ThisIsMySub()
  2. On Error GoTo EH
  3.  
  4.     'Add all your Code here......
  5.  
  6.     Exit Sub
  7. EH:
  8.     MsgBox "There was an error ....!  " & _
  9.         "Please contact your Database Administrator.", vbOKOnly, "WARNING!"
  10.     Exit Sub
  11. End Sub
With this basic template, you can do all sorts of things. For example, if your Process1 is to open Excel and performs some functions there, in the error handling portion, just add code to close your Excel Application there. You could also have another call, which some prefer to have, which closes everything out. I.e.:

Expand|Select|Wrap|Line Numbers
  1. ExitMySub:
  2.     xlSheet.Application.ActiveWorkbook.Close
  3.     Set xlSheet = Nothing
  4.     Set xlApp = Nothing
  5.     Exit Sub
Then, at any point in your sub, if you want to exit your Sub and close everything (without an error), then you just add:

Expand|Select|Wrap|Line Numbers
  1. GoTo ExitMySub
Very simple, really. But it does take some skill to identify errors that will kill your project and just errors that will interrupt a Sub, but still allow you to continue.

Hope this hepps!
Jan 28 '15 #2

100+
P: 171
Thank you twinnyfo
You are so kind as always.
I will try and let you know the progress
Jan 29 '15 #3

100+
P: 171
Hi twinnyfo
Actually the error occurs always in process1. So I would like to stop the Process_Click() sub to stop and show a message to the user without proceeding to process2 and further. So could you tell me how can I put the error trap in Process_Click().
Jan 29 '15 #4

twinnyfo
Expert Mod 2.5K+
P: 3,482
Use the templates in Post #2 and incorporate those into your code.

Keep in mind that I use most of my error trapping in the development of my code so that I can avoid any errors ever popping up. There are some errors that one cannot avoid, such as trying to find a file that does not exist, but that can be prevented as well.

The whole point is to determine what the Error that is occurring, then, build code into your project to prevent any errors (if at all possible).

So, in your instance, find out the error that is being tripped in Process1. Then you must figure out why it is causing an error, then programmatically prevent the error from occurring.
Jan 29 '15 #5

100+
P: 171
Dear twinnyfo

I was trying the error trap in the process1 sub as well as in the button click event. Still nothing happens. How can we stop the code running further from Process1 in the click event Process_Click() if there is an error in the process1 sub. I need to stop the Process_Click() and get the warning message.
Feb 12 '15 #6

twinnyfo
Expert Mod 2.5K+
P: 3,482
You could have a class-specific global Boolean variable that is set to true on the event of any error. Then, if Process1 generates an error, it returns to the main sub. As soon as you return to the main sub, check the status of the variable. If it is true, then exit the sub.

Example:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private fErr as Boolean
  5.  
  6. Private Sub MainSub()
  7. On Error GoTo EH
  8.     Process1
  9.     If fErr Then GoTo ExitSub
  10.     Process2
  11.     If fErr Then GoTo ExitSub
  12.     Process2
  13.     If fErr Then GoTo ExitSub
  14.  
  15. ExitSub:
  16.     Exit Sub
  17.  
  18. EH:
  19.     MsgBox "You had an error!" & vbcrlf & vbcrlf & _
  20.         Err.Number & ": " & Err.Description
  21.     Exit Sub
  22. End Sub
  23.  
  24. Private Sub Process1
  25. On Error GoTo EH:
  26.     'Your Code
  27.     Exit Sub
  28. EH:
  29.     MsgBox "You had an error!" & vbcrlf & vbcrlf & _
  30.         Err.Number & ": " & Err.Description
  31.     Exit Sub
  32. End Sub
Hope this hepps!
Feb 19 '15 #7

100+
P: 171
Thank you twinnyfo
I have tried it and it is working fine now. Sorry for the delayed reply because I am still stuck with the other issue posted in a different thread. I will post the progress on the other issue in that thread
Mar 4 '15 #8

twinnyfo
Expert Mod 2.5K+
P: 3,482
I just noticed something in my code. A minor tweak to the Process Subs:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Process1
  2. On Error GoTo EH:
  3.     'Your Code
  4.     Exit Sub
  5. EH:
  6.     MsgBox "You had an error!" & vbcrlf & vbcrlf & _
  7.         Err.Number & ": " & Err.Description
  8.     fErr = True
  9.     Exit Sub
  10. End Sub
This sets the Boolean value of the Flag we created whenever there is an Error.

Also, add:

Expand|Select|Wrap|Line Numbers
  1. fErr = False
To the MainSub
Mar 4 '15 #9

NeoPa
Expert Mod 15k+
P: 31,768
An alternative way to detect whether or not a procedure has fallen over an error is to use a Function procedure. That way you can pass back the information however you like. A Boolean can say whether or not, while a Long can return an error number or a String can return a message.
Mar 4 '15 #10

twinnyfo
Expert Mod 2.5K+
P: 3,482
Ahh, nice touch, NeoPa. Again, I am reminded of another useful trick!
Mar 4 '15 #11

100+
P: 171
Thank you twinnyfo for the quick response. I will add the lines as mentioned. But the code is worked ok while tested.
Mar 4 '15 #12

Post your reply

Sign in to post your reply or Sign up for a free account.