Consulting

Results 1 to 12 of 12

Thread: Global Error Handling

  1. #1
    VBAX Regular
    Joined
    Jun 2008
    Location
    Buderim, Queensland
    Posts
    54
    Location

    Global Error Handling

    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

  2. #2
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,354
    Location
    Hi Scott,

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

    Mark

  3. #3
    VBAX Regular
    Joined
    Jun 2008
    Location
    Buderim, Queensland
    Posts
    54
    Location
    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 ?

  4. #4
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,354
    Location
    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
    
    
    Formatting tags added by mark007
    ...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
    Last edited by GTO; 05-14-2009 at 06:44 PM.

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    24,836
    Location
    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 
    
    
    Formatting tags added by mark007
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,354
    Location
    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

  7. #7
    VBAX Regular
    Joined
    Jun 2008
    Location
    Buderim, Queensland
    Posts
    54
    Location
    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

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    24,836
    Location
    Different modules are irrelevant, it is entry points that count.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,354
    Location
    @XLD:

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

    Thanks,

    Mark

  10. #10
    VBAX Regular
    Joined
    Jun 2008
    Location
    Buderim, Queensland
    Posts
    54
    Location
    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 
    
    
    Formatting tags added by mark007

  11. #11
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    24,836
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  12. #12
    VBAX Regular
    Joined
    Jun 2008
    Location
    Buderim, Queensland
    Posts
    54
    Location
    OK I will try that approach for each of the Event entries I have and then see if the Error Handling fires

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •