PDA

View Full Version : Error Handler Routine (excel 97)



phendrena
11-14-2008, 06:13 AM
Hi there,

I was wondering if someone could give me an idea on how to code the following Error Handler routine :-

Code Errors (could be for any error, but in this case it happens when the user saves the workbook)
Message pops up to advise user of error
Error Handler waits for 10 seconds
Code then tries the original request againThanks,

CreganTur
11-14-2008, 06:42 AM
Just below the variable declaration section of your code you would type in:

On Error GoTo ErrorHandler

This tells VBA to go to the label ErrorHandler whenever an error occurs. You're going to want to trap the errors based on the error number. If you don't know what the number is, then you can run the code to produce the error and debug when prompted. In the Immediate Window you would type "?err" (minus the quotes) to get the number. Then you could build some select case statements for the errors:

ErrorHandler:
Select Case err.number
case 9 'subscript out of range error
MsgBox "This error happened!"
Resume Next 'ignores the error and resumes the code
End Select

For creating a delay take a look at this forum from MrExcel (http://www.mrexcel.com/forum/showthread.php?t=8175)

phendrena
11-14-2008, 07:58 AM
Just below the variable declaration section of your code you would type in:

On Error GoTo ErrorHandler

This tells VBA to go to the label ErrorHandler whenever an error occurs. You're going to want to trap the errors based on the error number. If you don't know what the number is, then you can run the code to produce the error and debug when prompted. In the Immediate Window you would type "?err" (minus the quotes) to get the number. Then you could build some select case statements for the errors:

ErrorHandler:
Select Case err.number
case 9 'subscript out of range error
MsgBox "This error happened!"
Resume Next 'ignores the error and resumes the code
End Select

For creating a delay take a look at this forum from MrExcel (http://www.mrexcel.com/forum/showthread.php?t=8175)

Great stuff, now a couple of questions....

1) Do i need to include the ErrorHandler within each sub where i think the error is going to occur or can i create it elsewhere?

2) How can i test to see if this works as i can't generate the error without input from other users (shared workbook fun).

Thanks,

phendrena
11-14-2008, 08:12 AM
Let me show you what i've got :-

I've highlighted the errorhandler.
It's called after activeworkbook.save

Private Sub cmdUpdate_Click()
' Check to see if Who Called has been completed
If Trim(Me.cboWhoCalled.Value) = "" Then
Me.cboWhoCalled.SetFocus
MsgBox "Please complete the Who Called field!"
Exit Sub
End If
' Check to see if Nature Of Call has been completed
If Trim(Me.cboReason.Value) = "" Then
Me.cboReason.SetFocus
MsgBox "Please complete the Nature Of Call field!"
Exit Sub
End If
Call SendGeneralEmail
ActiveWorkbook.Save
On Error GoTo ErrorHandler
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("InboundData")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtDate.Value
ws.Cells(iRow, 2).Value = Me.txtCM.Value
ws.Cells(iRow, 3).Value = Me.cboWhoCalled.Value
ws.Cells(iRow, 4).Value = Me.txtDealerNo.Value
ws.Cells(iRow, 5).Value = Me.txtContact.Value
ws.Cells(iRow, 6).Value = Me.txtDealerName.Value
ws.Cells(iRow, 7).Value = Me.cboScheme.Value
ws.Cells(iRow, 8).Value = Me.cboReason.Value
ws.Cells(iRow, 9).Value = Me.txtComments.Value
ws.Cells(iRow, 10).Value = Me.cboSubject.Value

'clear the data
Me.txtDate.Value = Format(Date, "yyyy/mm/dd")
Me.txtCM.Value = Application.UserName
Me.cboWhoCalled.Value = ""
Me.txtContact.Value = ""
Me.txtDealerNo.Value = ""
Me.txtDealerName.Value = ""
Me.cboScheme.Value = ""
Me.cboReason.Value = ""
Me.txtComments.Value = ""
Me.cboSubject.Value = ""
Me.txtCM.SetFocus

Unload Me
ErrorHandler:
Select Case Err.Number
Case 1004
MsgBox "Another user is trying to save the workbook. Automatically retrying in 5 seconds."
Application.Wait Now + TimeValue("0:00:05")
ActiveWorkbook.Save
Resume Next
End Select
End Sub

CreganTur
11-14-2008, 08:14 AM
1) Do i need to include the ErrorHandler within each sub where i think the error is going to occur or can i create it elsewhere?
It's procedure specific- you need to create an error handler for every Sub/Function where you need one.


2) How can i test to see if this works as i can't generate the error without input from other users (shared workbook fun).

recreate the conditions for an error to occur.

Also, it's important to use a catchall for all other errors:
Case Else MsgBox "Error " & Err.Number & ": " & Err.Description

phendrena
11-14-2008, 08:33 AM
Also, it's important to use a catchall for all other errors:
Case Else MsgBox "Error " & Err.Number & ": " & Err.Description
That code highlights in red.

Popsup :-

Compile Error:
Expected: end of statement

and highlights MsgBox in the VBIDE.

Am i missing something?
Sorry for being thick.

Bob Phillips
11-14-2008, 08:46 AM
It's procedure specific- you need to create an error handler for every Sub/Function where you need one.
Not absolutely correct.

If you have a hierarchy of called procedures, you can either add an error handler to go every procedure, or you can just add one to the initial procedure, and the called procedures will inherit that error handler.

You can even have A calls B calls C, where A defines an error handler, B does not and C does. If an error occurs in C, its error handler will handle it, if it occurs in B, even in code after the call to C, it will be handled by A's error handler.

There are pros and cons to each approach, I tend to have an error handler in each procedure and pass the flow back up and only show the error when I am back at the top level.

Bob Phillips
11-14-2008, 08:48 AM
That code highlights in red.

Popsup :-

Compile Error:
Expected: end of statement

and highlights MsgBox in the VBIDE.

Am i missing something?
Sorry for being thick.

You need to either, add a colon after Case Else; or put the action (MsgBox) on a new line.

GTO
11-14-2008, 04:09 PM
Greetings to all,

I have read thru this a couple of times, so hopefully I'm not just 'glossing over' some portion.

While I realize that the current code below 'ErrorHandler:' only runs upon passing a test, wouldn't it be good practice to include the Exit Sub?.

Like:

'...statements...
Unload Me
Exit Sub
ErrorHandler:
'... error handling statements...
End Sub

Just a thought, and hopefully not too 'far out in left field."

Mark