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 · Accepted 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

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  amp        amp  Err Description    ThisWorkbook Name  amp     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

[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