Global Error Handling

[scott56]

Hi,

I am trying to implement some Global Error Handling to a Revenue Forecast model workbook I have developed.

In the Open_Workbook routine I set

On Error Goto ErrorHandler:

Then within that same routine I create a basic ErrorHandler to display a message box and then close the workbook.

The problem I have is that the ErrorHandler doesn't seem to fire for any of the errors that get reported by Excel....I still get the default Error Message box giving the user the option to Debug, End or Continue..

I had thought from my reading that once you set an On Error statement that it stays inforce until you set an On Error Goto 0....but it doesn't seem to working like that for me...

Any help appreciated..

Scott

[GTO]

Hi Scott,

Error handling is set for each procedure (Sub or Function).

Mark

[scott56]

So do I need to specify an On Error statement for Procedures and Functions in the Workbook ?

I have probably over 100 Procedures in this workbook....is there a way to have a Global Error Handler setup ?

[GTO]

Yes; any procedure that you want error handling in must have at least the

On Error GoTo MyErrHandler

'...code

MyErrHandler:
'...something here
End Sub 'End Function


...in it

You could read this KB entry by DRJ for a general error handler:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=189

Also, read thru the help topic for the On Error Statement.

Hope this helps,

Mark

[Bob Phillips]

No, that is not correct. You only need to set an error handler at the top level of procedures, any called procedure will inherit that error handler unless it resets it or sets its own.



Public Function TopLevel()

On Error GoTo ErrorHandler

Call Level1Proc1

Call Level1Proc2

Exit Function
ErrorHandler:
MsgBox Err.Number & ": " & Err.Description & " in top level"

End Function

Public Function Level1Proc1()

'handle our own errors
On Error GoTo l11_ErrHandler

'force an error
Debug.Print 1 / 0

Exit Function

l11_ErrHandler:
'ignore this error
Debug.Print Err.Number & ": " & Err.Description; " & in level 1"
End Function

Public Function Level1Proc2()

'call another level

Call Level2Proc1

End Function

Public Function Level2Proc1()

'force an error
Debug.Print 1 / 0

End Function

[GTO]

Bob,

I realize that you've heard it a ton o' times, but you certainly are a blessing. I wish I could articulate it better, but thank-you ever so much.

@Scott:

My utter appologies, my misleading was most unintentional. Please post a response so that I'll know my bad info is corrected.

Thank you,

Mark

[scott56]

I thought that was the case....that you only needed an error handler at the top level....I think my structure is still having a problem though...

Rather than Top Level Procedure within Level 1 Procedures within Level 2 Procedure....

I have the procedures I want to react to the Error Handler in different Modules within a VBA project, some may be inside a Form and others with Modules themselves....

I have a Errorhandler set with the first Application Startup procedure but it it not recognised or set when it gets to a routine in another module....rather than try and explain it further I will develop a example of what I am talking about and then post it back here for further comment

Thanks
Scott

[Bob Phillips]

Different modules are irrelevant, it is entry points that count.

[GTO]

@XLD:

Does it matter that the Top Level is a Class (ThisWorkbook)?

Thanks,

Mark

[scott56]

Apologies for taking so long to post back the sample of what I am trying to do and how I thought the
error handling should work.

Find attached a sample workbook....

It has the following code....when you open the workbook a msg is displayed and then using the Error or
Error Free buttons the problem can be seen. I had thought when initiating an error the error handler
would be called into action...but it is not.....what am I doing wrong ??


Sub Workbook_Open()
Call ApplicationStartup

End Sub

Sub ApplicationStartup()
'This routines is called after the Licence verification process is completed successfully

'If for any reason an error occurs that has not been handled properly by this code then lets error
' gracefully and stop the application
On Error GoTo ErrorHandler:
MsgBox "The workbook has now started and the Error Handler has been setup"
Exit Sub

ErrorHandler:
Call GlobalErrorRoutine

End Sub
Sub GlobalErrorRoutine()
'This routine will display and error message and exit the routine
MsgBox "An error has occurred that cannot be recovered, please record the following details and _
contact Technical Support" & vbNewLine & _"The workbook will be saved before being closed" & _
vbNewLine & vbNewLine & "Error Number : " & Err.Number & vbNewLine & "Error Description : _
" & Err.Description, vbCritical, "Critical Error that cannot be recovered"

Workbooks(ActiveWorkbook.Name).Save
Workbooks(ActiveWorkbook.Name).Close

End Sub

Private Sub ErrorButton_Click()
Call ErrorCause

MsgBox "This will not show this error message "

End Sub
Private Sub ErrorFreeButton_Click()
MsgBox "The Error Free Button has been selected"

End Sub

Sub ErrorCause()
Debug.Print (1 / 0)

End Sub

[Bob Phillips]

The procedure ErrorButton_Click is an entry point, that is it is called from an event not from another procedure, so you need to have a separate error handler fire from there. The error handeler you triggered with some procedure that you ran earlier end when that procedure terminates.

[scott56]

OK I will try that approach for each of the Event entries I have and then see if the Error Handling fires