Good Patterns For VBA Error Handling

Question

What are some good patterns for error handling in VBA   In particular  what should I do in this situation       some code         some code where an error might occur         some code         some other code where a different error might occur         some other code         some code that must always be run  like a finally block        I want to handle both errors  and resume execution after the code where the error may occur  Also  the finally code at the end must ALWAYS run - no matter what exceptions are thrown earlier  How can I achieve this outcome

User · Answer

Here s my standard implementation  I like the labels to be self-descriptive   Public Sub DoSomething        On Error GoTo Catch   Try       normal code here      Exit Sub Catch        error code  you can get the specific error by checking Err Number  End Sub   Or  with a Finally block   Public Sub DoSomething        On Error GoTo Catch   Try        normal code here      GoTo Finally Catch        error code  Finally        cleanup code  End Sub

User · Answer

Professional Excel Development has a pretty good error handling scheme   If you re going to spend any time in VBA  it s probably worth getting the book   There are a number of areas where VBA is lacking and this book has good suggestions for managing those areas   PED describes two error handling methods  The main one is a system where all entry point procedures are subprocedures and all other procedures are functions that return Booleans   The entry point procedure use On Error statements to capture errors pretty much as designed  The non-entry point procedures return True if there were no errors and False if there were errors  Non-entry point procedures also use On Error   Both types of procedures use a central error handling procedure to keep the error in state and to log the error

User · Answer

Beware the elephant trap   I saw no mention of this in this discussion   Access 2010   How ACCESS VBA handles errors in CLASS objects is determined by a configurable option   VBA Code Editor   Tools   Options   General   Error Trapping

User · Answer

I find the following to work best  called the central error handling approach    Benefits  You have 2 modes of running your application  Debug and Production  In the Debug mode  the code will stop at any unexpected error and allow you to debug easily by jumping to the line where it occurred by pressing F8 twice  In the Production mode  a meaningful error message will get displayed to the user   You can throw intentional errors like this  which will stop execution of the code with a message to the user   Err Raise vbObjectError  gsNO DEBUG   Some meaningful error message to the user   Err Raise vbObjectError  gsUSER MESSAGE   Some meaningful non-error message to the user    Or to exit in the middle of a call stack without a message  Err Raise vbObjectError  gsSILENT   Implementation  You need to  wrap  all subroutines and functions with any significant amount of code with the following headers and footers  making sure to specify ehCallTypeEntryPoint in all your entry points  Note the msModule constant as well  which needs to be put in all modules   Option Explicit Const msModule As String     lt Your Module Name gt      This is an entry point  Public Sub AnEntryPoint       Const sSOURCE As String    AnEntryPoint      On Error GoTo ErrorHandler       Your code  ErrorExit      Exit Sub  ErrorHandler      If CentralErrorHandler Err  ThisWorkbook  msModule  sSOURCE  ehCallTypeEntryPoint  Then         Stop         Resume     Else         Resume ErrorExit     End If End Sub    This is any other subroutine or function that isn t an entry point Sub AnyOtherSub       Const sSOURCE As String    AnyOtherSub      On Error GoTo ErrorHandler       Your code  ErrorExit      Exit Sub  ErrorHandler      If CentralErrorHandler Err  ThisWorkbook  msModule  sSOURCE  Then         Stop         Resume     Else         Resume ErrorExit     End If End Sub   The contents of the central error handler module is the following                                                                 Comments  Error handler code                Run SetDebugMode True to use debug mode  Dev mode              It will be False by default  Production mode      Author    Igor Popov   Date      13 Feb 2014   Licence   MIT                                                               Option Explicit Option Private Module  Private Const msModule As String    MErrorHandler   Public Const gsAPP NAME As String     lt You Application Name gt    Public Const gsSILENT As String    UserCancel    A silent error is when the user aborts an action  no message should be displayed Public Const gsNO DEBUG As String    NoDebug     This type of error will display a specific message to the user in situation of an expected  provided-for  error  Public Const gsUSER MESSAGE As String    UserMessage   Use this type of error to display an information message to the user  Private Const msDEBUG MODE COMPANY     lt Your Company gt   Private Const msDEBUG MODE SECTION     lt Your Team gt   Private Const msDEBUG MODE VALUE    DEBUG MODE   Public Enum ECallType     ehCallTypeRegular   0     ehCallTypeEntryPoint End Enum  Public Function DebugMode   As Boolean     DebugMode   CBool GetSetting msDEBUG MODE COMPANY  msDEBUG MODE SECTION  msDEBUG MODE VALUE  0   End Function  Public Sub SetDebugMode Optional bMode As Boolean   True      SaveSetting msDEBUG MODE COMPANY  msDEBUG MODE SECTION  msDEBUG MODE VALUE  IIf bMode  1  0  End Sub                                                                Comments  The central error handler for all functions             Displays errors to the user at the entry point level  or  if we re below the entry point  rethrows it upwards until the entry point is reached               Returns True to stop and debug unexpected errors in debug mode                The function can be enhanced to log errors      Date          Developer           TDID    Comment                                                               13 Feb 2014   Igor Popov                  Created  Public Function CentralErrorHandler ErrObj As ErrObject  Wbk As Workbook  ByVal sModule As String  ByVal sSOURCE As String                                        Optional enCallType As ECallType   ehCallTypeRegular  Optional ByVal bRethrowError As Boolean   True  As Boolean      Static ssModule As String  ssSource As String     If Len ssModule    0 And Len ssSource    0 Then          Remember the module and the source of the first call to CentralErrorHandler         ssModule   sModule         ssSource   sSOURCE     End If     CentralErrorHandler   DebugMode And ErrObj Source  lt  gt  gsNO DEBUG And ErrObj Source  lt  gt  gsUSER MESSAGE And ErrObj Source  lt  gt  gsSILENT     If CentralErrorHandler Then          If it s an unexpected error and we re going to stop in the debug mode  just write the error message to the immediate window for debugging         Debug Print   Err     amp  Err Description     ElseIf enCallType   ehCallTypeEntryPoint Then          If we have reached the entry point and it s not a silent error  display the message to the user in an error box         If ErrObj Source  lt  gt  gsSILENT Then             Dim sMsg As String  sMsg   ErrObj Description             If ErrObj Source  lt  gt  gsNO DEBUG And ErrObj Source  lt  gt  gsUSER MESSAGE Then sMsg    Unexpected VBA error in workbook     amp  Wbk Name  amp      module     amp  ssModule  amp      call     amp  ssSource  amp        amp  vbCrLf  amp  vbCrLf  amp  sMsg             MsgBox sMsg  vbOKOnly   IIf ErrObj Source   gsUSER MESSAGE  vbInformation  vbCritical   gsAPP NAME         End If     ElseIf bRethrowError Then          Rethrow the error to the next level up if bRethrowError is True  by Default            Otherwise  do nothing as the calling function must be having special logic for handling errors          Err Raise ErrObj Number  ErrObj Source  ErrObj Description     End If End Function   To set yourself in the Debug mode  run the following in the Immediate window   SetDebugMode True

User · Answer

My personal view on a statement made earlier in this thread   And just for fun  On Error Resume Next is the devil incarnate and to be avoided  as it silently hides errors   I m using the On Error Resume Next on procedures where I don t want an error to stop my work and where any statement does not depend on the result of the previous statements  When I m doing this I add a global variable debugModeOn and I set it to True  Then I use it this way  If not debugModeOn Then On Error Resume Next  When I deliver my work  I set the variable to false  thus hiding the errors only to the user and showing them during testing  Also using it when doing something that may fail like calling the DataBodyRange of a ListObject that may be empty  On Error Resume Next Sheet1 ListObjects 1  DataBodyRange Delete On Error Goto 0  Instead of  If Sheet1 ListObjects 1  ListRows Count  gt  0 Then      Sheet1 ListObjects 1  DataBodyRange Delete End If  Or checking existence of an item in a collection  On Error Resume Next Err Clear Set auxiliarVar   collection key     Check existence  if you try to retrieve a nonexistant key you get error number 5  exists    Err Number  lt  gt  5

User · Answer

The code below shows an alternative that ensures there is only one exit point for the sub function   sub something       on error goto errHandler        start of code                        end of code        1  not needed but signals to any other developer that looks at this       code that you are skipping over the error handler          see point 1        err clear  errHandler      if err number  lt  gt  0 then           error handling code     end if end sub

User · Answer

I use a piece of code that i developed myself and it is pretty good for my codes   In the beginning of the function or sub  I define   On error Goto ErrorCatcher    and then  I handle the possible errors  ErrorCatcher  Select Case Err Number  Case 0  exit the code when no error was raised     On Error GoTo 0     Exit Function Case 1  Error on definition of object      do stuff Case     little description here      do stuff Case Else     Debug Print     ERROR      Debug Print         Number      Err Number     Debug Print         Descrip     Err Description     Debug Print         Source      Err Source     Debug Print         HelpCont    Err HelpContext     Debug Print         LastDLL     Err LastDllError     Stop     Err Clear     Resume End Select

User · Answer

Here s a pretty decent pattern   For debugging  When an error is raised  hit Ctrl-Break  or Ctrl-Pause   drag the break marker  or whatever it s called  down to the Resume line  hit F8 and you ll step to the line that  threw  the error   The ExitHandler is your  Finally    Hourglass will be killed every time  Status bar text will be cleared every time   Public Sub ErrorHandlerExample       Dim dbs As DAO Database     Dim rst As DAO Recordset      On Error GoTo ErrHandler     Dim varRetVal As Variant      Set dbs   CurrentDb     Set rst   dbs OpenRecordset  SomeTable   dbOpenDynaset  dbSeeChanges   dbFailOnError       Call DoCmd Hourglass True        Do something with the RecordSet and close it       Call DoCmd Hourglass False   ExitHandler      Set rst   Nothing     Set dbs   Nothing     Exit Sub  ErrHandler      Call DoCmd Hourglass False      Call DoCmd SetWarnings True      varRetVal   SysCmd acSysCmdClearStatus       Dim errX As DAO Error     If Errors Count  gt  1 Then        For Each errX In DAO Errors           MsgBox  ODBC Error    amp  errX Number  amp  vbCrLf  amp  errX Description        Next errX     Else         MsgBox  VBA Error    amp  Err Number  amp        amp  vbCrLf  amp  Err Description  amp  vbCrLf  amp   In  Form MainForm   vbCritical     End If      Resume ExitHandler     Resume  End Sub        Select Case Err Number         Case 3326  This Recordset is not updateable              Do something about it  Or not            Case Else             MsgBox  VBA Error    amp  Err Number  amp        amp  vbCrLf  amp  Err Description  amp  vbCrLf  amp   In  Form MainForm   vbCritical     End Select   It also traps for both DAO and VBA errors  You can put a Select Case in the VBA error section if you want to trap for specific Err numbers   Select Case Err Number     Case 3326  This Recordset is not updateable          Do something about it  Or not        Case Else         MsgBox  VBA Error    amp  Err Number  amp        amp  vbCrLf  amp  Err Description  amp  vbCrLf  amp   In  Form MainForm   vbCritical End Select

User · Answer

So you could do something like this   Function Errorthingy pParam  On Error GoTo HandleErr     your code here      ExitHere        your finally code     Exit Function      HandleErr          Select Case Err Number           different error handling here          Case Else             MsgBox  Error    amp  Err Number  amp        amp  Err Description  vbCritical   ErrorThingy          End Select      Resume ExitHere  End Function     If you want to bake in custom exceptions   e g  ones that violate business rules  use the example above but use the goto to alter the flow of the method as necessary

User · Answer

I would also add    The global Err object is the closest you have to an exception object You can effectively  throw an exception  with Err Raise   And just for fun    On Error Resume Next is the devil incarnate and to be avoided  as it silently hides errors

User · Answer

Also relevant to the discussion is the relatively unknown Erl function  If you have numeric labels within your code procedure  e g    Sub AAA   On Error Goto ErrorHandler  1000    code 1100    more code 1200    even more code that causes an error 1300    yet more code 9999    end of main part of procedure ErrorHandler  If Err Number  lt  gt  0 Then    Debug Print  Error      CStr Err Number   Err Descrption           Last Successful Line      CStr Erl  End If    End Sub    The Erl function returns the most recently encountered numberic line label   In the example above  if a run-time error occurs after label 1200  but before 1300   the Erl function will return 1200  since that is most recenlty sucessfully encountered line label   I find it to be a good practice to put a line label immediately above your error handling block  I typcially use 9999 to indicate that the main part of the procuedure ran to its expected conculsion   NOTES    Line labels MUST be positive integers -- a label like MadeItHere  isn t recogonized by Erl   Line labels are completely unrelated to the actual line numbers of a VBIDE CodeModule  You can use any positive numbers you want  in any order you want  In the example above  there are only 25 or so lines of code  but the line label numbers begin at 1000  There is no relationship between editor line numbers and line label numbers used with Erl  Line label numbers need not be in any particular order  although if they are not in ascending  top-down order  the efficacy and benefit of Erl is greatly diminished  but Erl will still report the correct number   Line labels are specific to the procedure in which they appear  If procedure ProcA calls procedure ProcB and an error occurs in ProcB that passes control back to ProcA  Erl  in ProcA  will return the most recently encounterd line label number in ProcA before it calls ProcB  From within ProcA  you cannot get the line label numbers that might appear in ProcB     Use care when putting line number labels within a loop  For example   For X   1 To 100 500    some code that causes an error 600  Next X   If the code following line label 500 but before 600 causes an error  and that error arises on the 20th iteration of the loop  Erl will return 500  even though 600 has been encounterd successfully in the previous 19 interations of the loop   Proper placement of line labels within the procedure is critical to using the Erl function to get truly meaningful information   There are any number of free utilies on the net that will insert numeric line label in a procedure automatically  so you have fine-grained error information while developing and debugging  and then remove those labels once code goes live   If your code displays error information to the end user if an unexpected error occurs  providing the value from Erl in that information can make finding and fixing the problem VASTLY simpler than if value of Erl is not reported

User · Answer

Error Handling in VBA     On Error Goto ErrorHandlerLabel  Resume  Next   ErrorHandlerLabel  On Error Goto 0  disables current error handler  Err object   The Err object s properties are normally reset to zero or a zero-length string in the error handling routine  but it can also be done explicitly with Err Clear   Errors in the error handling routine are terminating   The range 513-65535 is available for user errors  For custom class errors  you add vbObjectError to the error number  See MS documentation about Err Raise and the list of error numbers   For not implemented interface members in a derived class  you should use the constant E NOTIMPL    amp H80004001     Option Explicit  Sub HandleError     Dim a As Integer   On Error GoTo errMyErrorHandler     a   7   0   On Error GoTo 0    Debug Print  This line won t be executed    DoCleanUp    a   0 Exit Sub errMyErrorHandler    MsgBox Err Description        vbExclamation   vbOKCancel         Error     amp  CStr Err Number  Resume DoCleanUp End Sub  Sub RaiseAndHandleError     On Error GoTo errMyErrorHandler       The range 513-65535 is available for user errors        For class errors  you add vbObjectError to the error number      Err Raise vbObjectError   513   Module1  Test      My custom error     On Error GoTo 0    Debug Print  This line will be executed    Exit Sub errMyErrorHandler    MsgBox Err Description        vbExclamation   vbOKCancel         Error     amp  CStr Err Number    Err Clear Resume Next End Sub  Sub FailInErrorHandler     Dim a As Integer   On Error GoTo errMyErrorHandler     a   7   0   On Error GoTo 0    Debug Print  This line won t be executed    DoCleanUp    a   0 Exit Sub errMyErrorHandler    a   7   0    lt    Terminating error    MsgBox Err Description        vbExclamation   vbOKCancel         Error     amp  CStr Err Number  Resume DoCleanUp End Sub  Sub DontDoThis        Any error will go unnoticed    On Error Resume Next     Some complex code that fails here  End Sub  Sub DoThisIfYouMust      On Error Resume Next     Some code that can fail but you don t care    On Error GoTo 0      More code here End Sub

[exception] Good Patterns For VBA Error Handling

Examples related to exception

Examples related to vba

Examples related to exception-handling