Good error handling prevents end users from seeing VBA runtime errors and helps the developer easily diagnose and correct errors.
There are three main methods of Error Handling in VBA, two of which should be avoided for distributed programs unless specifically required in the code.
On Error GoTo 0 'Avoid using
On Error Resume Next 'Avoid using
On Error GoTo <line> 'Prefer using
If no error handling is set in your code,
On Error GoTo 0 is the default error handler. In this mode, any runtime errors will launch the typical VBA error message, allowing you to either end the code or enter
debug mode, identifying the source. While writing code, this method is the simplest and most useful, but it should always be avoided for code that is distributed to end users, as this method is very unsightly and difficult for end users to understand.
On Error Resume Next will cause VBA to ignore any errors that are thrown at runtime for all lines following the error call until the error handler has been changed. In very specific instances, this line can be useful, but it should be avoided outside of these cases. For example, when launching a separate program from an Excel Macro, the
On Error Resume Next call can be useful if you are unsure whether or not the program is already open:
'In this example, we open an instance of Powerpoint using the On Error Resume Next call Dim PPApp As PowerPoint.Application Dim PPPres As PowerPoint.Presentation Dim PPSlide As PowerPoint.Slide 'Open PPT if not running, otherwise select active instance On Error Resume Next Set PPApp = GetObject(, "PowerPoint.Application") On Error GoTo ErrHandler If PPApp Is Nothing Then 'Open PowerPoint Set PPApp = CreateObject("PowerPoint.Application") PPApp.Visible = True End If
Had we not used the
On Error Resume Next call and the Powerpoint application was not already open, the
GetObject method would throw an error. Thus,
On Error Resume Next was necessary to avoid creating two instances of the application.
Note: It is also a best practice to immediately reset the error handler as soon as you no longer need the
On Error Resume Next call
This method of error handling is recommended for all code that is distributed to other users. This allows the programmer to control exactly how VBA handles an error by sending the code to the specified line. The tag can be filled with any string (including numeric strings), and will send the code to the corresponding string that is followed by a colon. Multiple error handling blocks can be used by making different calls of
On Error GoTo <line>. The subroutine below demonstrates the syntax of an
On Error GoTo <line> call.
Note: It is essential that the
Exit Sub line is placed above the first error handler and before every subsequent error handler to prevent the code from naturally progressing into the block without an error being called. Thus, it is best practice for function and readability to place error handlers at the end of a code block.
Sub YourMethodName() On Error GoTo errorHandler ' Insert code here On Error GoTo secondErrorHandler Exit Sub 'The exit sub line is essential, as the code will otherwise 'continue running into the error handling block, likely causing an error errorHandler: MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _ VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error" Exit Sub secondErrorHandler: If Err.Number = 424 Then 'Object not found error (purely for illustration) Application.ScreenUpdating = True Application.EnableEvents = True Exit Sub Else MsgBox "Error " & Err.Number & ": " & Err.Desctription Application.ScreenUpdating = True Application.EnableEvents = True Exit Sub End If Exit Sub End Sub
If you exit your method with your error handling code, ensure that you clean up: