Properly Handling Errors in VBA Excel

Question

I ve been working with VBA for quite a while now  but I m still not so sure about Error Handling   A good article is the one of   CPearson com  However I m still wondering if the way I used to do ErrorHandling was is completely wrong  Block 1       On Error Goto ErrCatcher    If UBound  sortedDates   gt  0 Then            Code     Else ErrCatcher            Code     End If   The if clause  because if it is true  will be executed and if it fails the Goto will go into the Else-part  since the Ubound of an Array should never be zero or less  without an Error  this method worked quite well so far   If I understood it right it should be like this   Block 2  On Error Goto ErrCatcher     If Ubound  sortedDates   gt  0 Then            Code     End If      Goto hereX  ErrCatcher           Code     Resume   Resume Next   Resume hereX  hereX    Or even like this  Block 3  On Error Goto ErrCatcher     If Ubound  sortedDates   gt  0 Then            Code     End If  ErrCatcher      If Err Number  lt  gt  0 then          Code     End If   The most common way I see is that one  that the Error  Catcher  is at the end of a sub and the Sub actually ends before with a  Exit Sub   but however isn t it a little confusing if the Sub is quite big if you jump vice versa to read through the code   Block 4     Source of the following Code      CPearson com     On Error Goto ErrHandler     N   1   0      cause an error           more code        Exit Sub    ErrHandler        error handling code      Resume Next  End Sub    Should it be like in Block 3

User · Accepted Answer

I definitely wouldn t use Block1  It doesn t seem right having the Error block in an IF statement unrelated to Errors   Blocks 2 3  amp  4 I guess are variations of a theme  I prefer the use of Blocks 3  amp  4 over 2 only because of a dislike of the GOTO statement  I generally use the Block4 method  This is one example of code I use to check if the Microsoft ActiveX Data Objects 2 8 Library is added and if not add or use an earlier version if 2 8 is not available   Option Explicit Public booRefAdded As Boolean  one time check for references  Public Sub Add References   Dim lngDLLmsadoFIND As Long  If Not booRefAdded Then     lngDLLmsadoFIND   28   load msado28 tlb  if cannot find step down versions until found          On Error GoTo RefErr               Add Microsoft ActiveX Data Objects 2 8             Application VBE ActiveVBProject references AddFromFile               Environ  CommonProgramFiles       System ado msado   amp  lngDLLmsadoFIND  amp    tlb           On Error GoTo 0      Exit Sub  RefErr          Select Case Err Number             Case 0                  no error             Case 1004                   Enable Trust Centre Settings                  MsgBox   Certain VBA References are not available  to allow access follow these steps   amp  Chr 10   amp                      Goto Excel Options Trust Centre Trust Centre Security Macro Settings   amp  Chr 10   amp                      1  Tick -  Disable all macros with notification    amp  Chr 10   amp                      2  Tick -  Trust access to the VBA project objects model                     End             Case 32813                   Err Number 32813 means reference already added             Case 48                   Reference doesn t exist                  If lngDLLmsadoFIND   0 Then                     MsgBox   Cannot Find Required Reference                       End                 Else                     For lngDLLmsadoFIND   lngDLLmsadoFIND - 1 To 0 Step -1                            Resume                     Next lngDLLmsadoFIND                 End If              Case Else                  MsgBox Err Number  amp  vbCrLf  amp  Err Description  vbCritical   Error                   End         End Select          On Error GoTo 0 End If booRefAdded   TRUE End Sub

User · Answer

Two main purposes for error handling    Trap errors you can predict but can t control the user from doing  e g  saving a file to a thumb drive when the thumb drives has been removed  For unexpected errors  present user with a form that informs them what the problem is   That way  they can relay that message to you and you might be able to give them a work-around while you work on a fix    So  how would you do this   First of all  create an error form to display when an unexpected error occurs   It could look something like this  FYI  Mine is called frmErrors      Notice the following labels    lblHeadline lblSource lblProblem lblResponse   Also  the standard command buttons    Ignore Retry Cancel   There s nothing spectacular in the code for this form   Option Explicit  Private Sub cmdCancel Click     Me Tag   CMD CANCEL   Me Hide End Sub  Private Sub cmdIgnore Click     Me Tag   CMD IGNORE   Me Hide End Sub  Private Sub cmdRetry Click     Me Tag   CMD RETRY   Me Hide End Sub  Private Sub UserForm Initialize     Me lblErrorTitle Caption    Custom Error Title Caption String  End Sub  Private Sub UserForm QueryClose Cancel As Integer  CloseMode As Integer     Prevent user from closing with the Close box in the title bar      If CloseMode  lt  gt  1 Then       cmdCancel Click     End If End Sub   Basically  you want to know which button the user pressed when the form closes   Next  create an Error Handler Module that will be used throughout your VBA app                                                                          MODULE  ErrorHandler       PURPOSE  A VBA Error Handling routine to handle               any unexpected errors         Date     Name            Description                                                                     03 22 2010    Ray      Initial Creation                                                                   Option Explicit  Global Const CMD RETRY   0 Global Const CMD IGNORE   1 Global Const CMD CANCEL   2 Global Const CMD CONTINUE   3  Type ErrorType     iErrNum As Long     sHeadline As String     sProblemMsg As String     sResponseMsg As String     sErrorSource As String     sErrorDescription As String     iBtnCap 3  As Integer     iBitmap As Integer End Type  Global gEStruc As ErrorType Sub EmptyErrStruc S utEStruc As ErrorType    Dim i As Integer    utEStruc iErrNum   0   utEStruc sHeadline        utEStruc sProblemMsg        utEStruc sResponseMsg        utEStruc sErrorSource        For i   0 To 2     utEStruc iBtnCap i    -1   Next   utEStruc iBitmap   1  End Sub Function FillErrorStruct F EStruc As ErrorType  As Boolean    Must save error text before starting new error handler    in case we need it later   EStruc sProblemMsg   Error EStruc iErrNum    On Error GoTo vbDefaultFill    EStruc sHeadline    Error    amp  Format  EStruc iErrNum    EStruc sProblemMsg   EStruc sErrorDescription   EStruc sErrorSource   EStruc sErrorSource   EStruc sResponseMsg    Contact the Company and tell them you received Error      amp  Str  EStruc iErrNum   amp     You should write down the program function you were using  the record you were working with  and what you were doing       Select Case EStruc iErrNum         Case Error number here         not sure what numeric errors user will ecounter  but can be implemented here         e g          EStruc sHeadline    Error 3265          EStruc sResponseMsg    Contact tech support  Tell them what you were doing in the program         Case Else         EStruc sHeadline    Error    amp  Format  EStruc iErrNum   amp        amp  EStruc sErrorDescription        EStruc sProblemMsg   EStruc sErrorDescription     End Select     GoTo FillStrucEnd  vbDefaultFill      Error Not on file   EStruc sHeadline    Error    amp  Format  EStruc iErrNum   amp     Contact Tech Support    EStruc sResponseMsg    Contact the Company and tell them you received Error      amp  Str  EStruc iErrNum  FillStrucEnd     Exit Function  End Function Function iErrorHandler F utEStruc As ErrorType  As Integer   Static sCaption 3  As String   Dim i As Integer   Dim iMCursor As Integer    Beep     Setup static array   If Len sCaption 0    lt  1 Then     sCaption CMD IGNORE      amp Ignore      sCaption CMD RETRY      amp Retry      sCaption CMD CANCEL      amp Cancel      sCaption CMD CONTINUE     Continue    End If    Load frmErrors     Did caller pass error info   If not fill struc with the needed info   If Len utEStruc sHeadline   lt  1 Then     i   FillErrorStruct F utEStruc    End If    frmErrors lblHeadline Caption   utEStruc sHeadline   frmErrors lblProblem Caption   utEStruc sProblemMsg   frmErrors lblSource Caption   utEStruc sErrorSource   frmErrors lblResponse Caption   utEStruc sResponseMsg    frmErrors Show   iErrorHandler F   frmErrors Tag     Save user response   Unload frmErrors                    Unload and release form    EmptyErrStruc S utEStruc            Release memory  End Function   You may have errors that will be custom only to your application     This would typically be a short list of errors specifically only to your application  If you don t already have a constants module  create one that will contain an ENUM of your custom errors    NOTE   Office  97 does NOT support ENUMS    The ENUM should look something like this   Public Enum CustomErrorName   MaskedFilterNotSupported   InvalidMonthNumber End Enum   Create a module that will throw your custom errors                                                                                                                                          MODULE  CustomErrorList       PURPOSE  For trapping custom errors applicable to this application    INSTRUCTIONS   To use this module to create your own custom error                  1   Add the Name of the Error to the CustomErrorName Enum                 2   Add a Case Statement to the raiseCustomError Sub                 3   Call the raiseCustomError Sub in the routine you may see the custom error                 4   Make sure the routine you call the raiseCustomError has error handling in it           Date     Name            Description                                                                                                                                     03 26 2010    Ray       Initial Creation                                                                                                                                   Option Explicit Const MICROSOFT OFFSET   512  Microsoft reserves error values between vbObjectError and vbObjectError   512                                                                                                      FUNCTION   raiseCustomError       PURPOSE   Raises a custom error based on the information passed    PARAMETERS   customError - An integer of type CustomErrorName Enum that defines the custom error               errorSource - The place the error came from       Returns   The ASCII vaule that should be used for the Keypress         Date     Name            Description                                                                                                       03 26 2010    Ray       Initial Creation                                                                                                   Public Sub raiseCustomError customError As Integer  Optional errorSource As String         Dim errorLong As Long   Dim errorDescription As String    errorLong   vbObjectError   MICROSOFT OFFSET   customError    Select Case customError      Case CustomErrorName MaskedFilterNotSupported       errorDescription    The mask filter passed is not supported       Case CustomErrorName InvalidMonthNumber       errorDescription    Invalid Month Number Passed       Case Else       errorDescription    The custom error raised is unknown      End Select    Err Raise errorLong  errorSource  errorDescription  End Sub   You are now well equipped to trap errors in your program   You sub  or function   should look something like this       Public Sub MySub monthNumber as Integer    On Error GoTo eh      Dim sheetWorkSheet As Worksheet     Run Some code here                                                             OPTIONAL BLOCK 1   Look for a specific error                                                        Temporarily Turn off Error Handling so that you can check for specific error   On Error Resume Next    Do some code where you might expect an error   Example below    Const ERR SHEET NOT FOUND   9  This error number is actually subscript out of range  but for this example means the worksheet was not found    Set sheetWorkSheet   Sheets  January       Now see if the expected error exists    If Err Number   ERR SHEET NOT FOUND Then     MsgBox  Hey   The January worksheet is missing   You need to recreate it       Exit Sub   ElseIf Err Number  lt  gt  0 Then      Uh oh   there was an error we did not expect so just run basic error handling      GoTo eh   End If     Finished with predictable errors  turn basic error handling back on    On Error GoTo eh                                                                                               End of OPTIONAL BLOCK 1                                                                                                                                                                                     OPTIONAL BLOCK 2   Raise  a k a   Throw   a Custom Error if applicable                                                                                         If not  monthNumber  gt  1 and monthnumber  lt  12  then     raiseCustomError CustomErrorName InvalidMonthNumber   My Sub    end if                                                                                              End of OPTIONAL BLOCK 2                                                                                           Rest of code in your sub    goto sub exit  eh    gEStruc iErrNum   Err Number   gEStruc sErrorDescription   Err Description   gEStruc sErrorSource   Err Source   m rc   iErrorHandler F gEStruc     If m rc   CMD RETRY Then     Resume   End If  sub exit     Any final processing you want to do     Be careful with what you put here because if it errors out  the error rolls up   This can be difficult to debug  especially if calling routine has no error handling     Exit Sub  I was told a long time ago  10  years  that exit sub was better than end sub   I can t tell you why  so you may not want to put in this line of code   It s habit I can t break  P End Sub   A copy paste of the code above may not work right out of the gate  but should definitely give you the gist     BTW  if you ever need me to do your company logo  look me up at http   www MySuperCrappyLogoLabels99 com

User · Answer

I keep things simple  At the module level I define two variables and set one to the name of the module itself       Private Const ThisModuleName            As String    mod Custom Functions      Public sLocalErrorMsg                   As String   Within each Sub Function of the module I define a local variable      Dim ThisRoutineName                     As String   I set ThisRoutineName to the name of the sub or function    Housekeeping     On Error Goto ERR RTN     ThisRoutineName    CopyWorksheet    I then send all errors to an ERR RTN  when they occur  but I first set the sLocalErrorMsg to define what the error actually is and provide some debugging info       If Len Trim FromWorksheetName    lt  1 Then         sLocalErrorMsg    Parameter  FromWorksheetName  Is Missing           GoTo ERR RTN     End If   At the bottom of each sub function  I direct the logic flow as follows              The  normal  logic goes here for what the routine does           GoTo EXIT RTN      ERR RTN           On Error Resume Next        Call error handler if we went this far          ErrorHandler ThisModuleName  ThisRoutineName  sLocalErrorMsg  Err Description  Err Number  False      EXIT RTN           On Error Resume Next               Some closing logic            End If   I then have a seperate module I put in all projects called  mod Error Handler                                                                                                              Subroutine Name      ErrorHandler                                                                                                                                                           Description                                                                                     This module will handle the common error alerts                                                                                                                                           Inputs                                                                                          ModuleName                String     The name of the module error is in                       RoutineName               String     The name of the routine error in in                      LocalErrorMsg             String     A local message to assist with troubleshooting           ERRDescription            String     The Windows Error Description                            ERRCode                   Long       The Windows Error Code                                   Terminate                 Boolean    End program if error encountered                                                                                                                     Revision History                                                                              Date  YYYYMMDD  Author                Change                                                                                                                                                20140529        XXXXX X  XXXXX        Original                                                                                                                                                                                                                                                Public Sub ErrorHandler ModuleName As String  RoutineName As String  LocalErrorMsg As String  ERRDescription As String  ERRCode As Long  Terminate As Boolean          Dim sBuildErrorMsg                 As String        Build Error Message To Display         sBuildErrorMsg    Error Information    amp  vbCrLf  amp  vbCrLf          If Len Trim ModuleName    lt  1 Then             ModuleName    Unknown          End If          If Len Trim RoutineName    lt  1 Then            RoutineName    Unknown          End If          sBuildErrorMsg   sBuildErrorMsg  amp   Module Name            amp  ModuleName  amp  vbCrLf  amp  vbCrLf         sBuildErrorMsg   sBuildErrorMsg  amp   Routine Name           amp  RoutineName  amp  vbCrLf  amp  vbCrLf          If Len Trim LocalErrorMsg    gt  0 Then             sBuildErrorMsg   sBuildErrorMsg  amp   Local Error Msg        amp  LocalErrorMsg  amp  vbCrLf  amp  vbCrLf         End If          If Len Trim ERRDescription    gt  0 Then             sBuildErrorMsg   sBuildErrorMsg  amp   Program Error Msg      amp  ERRDescription  amp  vbCrLf  amp  vbCrLf             If IsNumeric ERRCode  Then                 sBuildErrorMsg   sBuildErrorMsg  amp   Program Error Code     amp  Trim Str ERRCode    amp  vbCrLf  amp  vbCrLf             End If         End If          MsgBox sBuildErrorMsg  vbOKOnly   vbExclamation   Error Detected            If Terminate Then             End         End If      End Sub   The end result is a pop-up error message teling me in what module  what soubroutine  and what the error message specifically was   In addition  it also will insert the Windows error message and code

User · Answer

You ve got one truly marvelous answer from ray023  but your comment that it s probably overkill is apt  For a  lighter  version      Block 1 is  IMHO  bad practice  As already pointed out by osknows  mixing error-handling with normal-path code is Not Good  For one thing  if a new error is thrown while there s an Error condition in effect you will not get an opportunity to handle it  unless you re calling from a routine that also has an error handler  where the execution will  bubble up     Block 2 looks like an imitation of a Try Catch block  It should be okay  but it s not The VBA Way  Block 3 is a variation on Block 2   Block 4 is a bare-bones version of The VBA Way  I would strongly advise using it  or something like it  because it s what any other VBA programmer inherting the code will expect  Let me present a small expansion  though   Private Sub DoSomething   On Error GoTo ErrHandler   Dim as required   functional code that might throw errors  ExitSub       any always-execute  cleanup   code goes here -- analagous to a Finally block       don t forget to do this -- you don t want to fall into error handling when there s no error     Exit Sub  ErrHandler       can Select Case on Err Number if there are any you want to handle specially       display to user     MsgBox  Something s wrong     amp  vbCrLf  amp  Err Description       or use a central DisplayErr routine  written Public in a Module     DisplayErr Err Number  Err Description      Resume ExitSub     Resume End Sub   Note that second Resume  This is a trick I learned recently  It will never execute in normal processing  since the Resume  lt label gt  statement will send the execution elsewhere  It can be a godsend for debugging  though  When you get an error notification  choose Debug  or press Ctl-Break  then choose Debug when you get the  Execution was interrupted  message   The next  highlighted  statement will be either the MsgBox or the following statement  Use  Set Next Statement   Ctl-F9  to highlight the bare Resume  then press F8  This will show you exactly where the error was thrown   As to your objection to this format  jumping around   A  it s what VBA programmers expect  as stated previously   amp  B  your routines should be short enough that it s not far to jump

User · Answer

Block 2 doesn t work because it doesn t reset the Error Handler potentially causing an endless loop  For Error Handling to work properly in VBA  you need a Resume statement to clear the Error Handler  The Resume also reactivates the previous Error Handler  Block 2 fails because a new error would go back to the previous Error Handler causing an infinite loop   Block 3 fails because there is no Resume statement so any attempt at error handling after that will fail   Every error handler must be ended by exiting the procedure or a Resume statement  Routing normal execution around an error handler is confusing  This is why error handlers are usually at the bottom    But here is another way to handle an error in VBA  It handles the error inline like Try Catch in VB net There are a few pitfalls  but properly managed it works quite nicely   Sub InLineErrorHandling         code without error handling  BeginTry1        activate inline error handler     On Error GoTo ErrHandler1           code block that may result in an error         Dim a As String  a    Abc          Dim c As Integer  c   a  type mismatch  ErrHandler1        handle the error     If Err Number  lt  gt  0 Then           the error handler has deactivated the previous error handler          MsgBox  Err Description            Resume  or exit procedure  is the only way to get out of an error handling block          otherwise the following On Error statements will have no effect          CAUTION  it also reactivates the previous error handler         Resume EndTry1     End If  EndTry1       CAUTION  since the Resume statement reactivates the previous error handler      you must ALWAYS use an On Error GoTo statement here      because another error here would cause an endless loop      use On Error GoTo 0 or On Error GoTo  lt Label gt      On Error GoTo 0       more code with or without error handling  End Sub   Sources    http   www cpearson com excel errorhandling htm  http   msdn microsoft com en-us library bb258159 aspx    The key to making this work is to use a Resume statement immediately followed by another On Error statement  The Resume is within the error handler and diverts code to the EndTry1 label  You must immediately set another On Error statement to avoid problems as the previous error handler will  resume   That is  it will be active and ready to handle another error  That could cause the error to repeat and enter an infinite loop    To avoid using the previous error handler again you need to set On Error to a new error handler or simply use On Error Goto 0 to cancel all error handling

User · Answer

This is what I m teaching my students tomorrow   After years of looking at this stuff    ie all of the documentation above http   www cpearson com excel errorhandling htm comes to mind as an excellent one     I hope this summarizes it for others   There is an Err object and an active  or inactive  ErrorHandler   Both need to be handled and reset for new errors     Paste this into a workbook and step through it with F8   Sub ErrorHandlingDemonstration        On Error GoTo ErrorHandler       this will error     Debug Print  1   0        this will also error     dummy   Application WorksheetFunction VLookup  not gonna find me   Range  A1 B2    2  True        silly error     Dummy2    string    50      Exit Sub  zeroDivisionErrorBlock      maybeWe    did some cleanup on variables that shouldnt have been divided         moves the code execution to the line AFTER the one that errored     Resume Next  vlookupFailedErrorBlock      maybeThisTime    we made sure the value we were looking for was in the range         moves the code execution to the line AFTER the one that errored     Resume Next  catchAllUnhandledErrors      MsgBox thisErrorsDescription      Exit Sub  ErrorHandler      thisErrorsNumberBeforeReset   Err Number     thisErrorsDescription   Err Description      this will reset the error object and error handling     On Error GoTo 0      this will tell vba where to go for new errors  ie the new ErrorHandler that was previous just reset      On Error GoTo ErrorHandler        11 is the err number for division by 0     If thisErrorsNumberBeforeReset   11 Then         GoTo zeroDivisionErrorBlock       1004 is the err number for vlookup failing     ElseIf thisErrorsNumberBeforeReset   1004 Then         GoTo vlookupFailedErrorBlock     Else         GoTo catchAllUnhandledErrors     End If  End Sub

[excel] Properly Handling Errors in VBA (Excel)

Examples related to excel

Examples related to vba