VBA How to display an error message just like the standard error message which has a Debug button

Question

As usual  I create an error-handler using On Error Goto statement  there I put a few lines of cleaning codes and display the error message  but now I don t want to lose the comfortableness of the default handler which also point me to the exact line where the error has occured  How can I do that   Thanks in advance

User · Answer

This answer does not address the Debug button (you'd have to design a form and use the buttons on that to do something like the method in your next question). But it does address this part:

now I don't want to lose the comfortableness of the default handler which also point me to the exact line where the error has occured.

First, I'll assume you don't want this in production code - you want it either for debugging or for code you personally will be using. I use a compiler flag to indicate debugging; then if I'm troubleshooting a program, I can easily find the line that's causing the problem.

# Const IsDebug = True

Sub ProcA()
On Error Goto ErrorHandler
' Main code of proc

ExitHere:
    On Error Resume Next
    ' Close objects and stuff here
    Exit Sub

ErrorHandler:
    MsgBox Err.Number & ": " & Err.Description, , ThisWorkbook.Name & ": ProcA"
    #If IsDebug Then
        Stop            ' Used for troubleshooting - Then press F8 to step thru code 
        Resume          ' Resume will take you to the line that errored out
    #Else
        Resume ExitHere ' Exit procedure during normal running
    #End If
End Sub

Note: the exception to Resume is if the error occurs in a sub-procedure without an error handling routine, then Resume will take you to the line in this proc that called the sub-procedure with the error. But you can still step into and through the sub-procedure, using F8 until it errors out again. If the sub-procedure's too long to make even that tedious, then your sub-procedure should probably have its own error handling routine.

There are multiple ways to do this. Sometimes for smaller programs where I know I'm gonna be stepping through it anyway when troubleshooting, I just put these lines right after the MsgBox statement:

    Resume ExitHere         ' Normally exits during production
    Resume                  ' Never will get here
Exit Sub

It will never get to the Resume statement, unless you're stepping through and set it as the next line to be executed, either by dragging the next statement pointer to that line, or by pressing CtrlF9 with the cursor on that line.

Here's an article that expands on these concepts: Five tips for handling errors in VBA. Finally, if you're using VBA and haven't discovered Chip Pearson's awesome site yet, he has a page explaining Error Handling In VBA.

User · Answer

First the good news  This code does what you want  please note the  line numbers    Sub a    10     On Error GoTo ErrorHandler  20     DivisionByZero   1   0  30     Exit Sub  ErrorHandler   41  If Err Number  lt  gt  0 Then  42     Msg    Error      amp  Str Err Number   amp    was generated by               amp  Err Source  amp  Chr 13   amp   Error Line     amp  Erl  amp  Chr 13   amp  Err Description  43     MsgBox Msg     Error   Err HelpFile  Err HelpContext  44     End If  50     Resume Next  60  End Sub   When it runs  the expected MsgBox is shown     And now the bad news   Line numbers are a residue of old versions of Basic  The programming environment usually took charge of inserting and updating them  In VBA and other  modern  versions  this functionality is lost     However  Here there are several alternatives for  automatically  add line numbers  saving you the tedious task of typing them     but all of them seem more or less cumbersome     or commercial     HTH

User · Answer

For Me I just wanted to see the error in my VBA application so in the function I created the below code    Function Database FileRpt  ------------------------- On Error GoTo CleanFail  -------------------------     Create DailyReport Action and code   CleanFail                                           MsgBox                            amp  vbCrLf  amp   Err Number     amp  Err Number     amp  vbCrLf  amp   Err Description     amp  Err Description     amp  vbCrLf  amp   Err Source     amp  Err Source     amp  vbCrLf  amp                             amp  vbCrLf  amp      Exiting VBA Function  Database FileRpt      amp  vbCrLf  amp      Excel VBA Program Reset           VBA Error Exception Raised                                              Note that the next line will reset the error object to 0  the variables  above are used to remember the values   so that the same error can be re-raised  Err Clear                                           Resume CleanExit  CleanExit    cleanup code   if any  goes here  runs regardless of error state   Exit Function    SUB  or Function      End Function    end of Database FileRpt    ------------------

User · Answer

There is a simpler way simply disable the error handler in your error handler if it does not match the error types you are doing and resume   The handler below checks agains each error type and if none are a match it returns error resume to normal VBA ie GoTo 0 and resumes the code which then tries to rerun the code and the normal error block pops up   On Error GoTo ErrorHandler  x   1 0  ErrorHandler  if Err Number   13 then   13 is Type mismatch  only used as an example    error handling code for this  end if  If err Number   1004 then   1004 is Too Large  only used as an example    error handling code for this  end if  On Error GoTo 0 Resume

[vba] VBA: How to display an error message just like the standard error message which has a "Debug" button?

Examples related to vba

Examples related to scripting

Examples related to excel

Examples related to ms-office