excel-vba VBA Best Practices Error Handling

Example

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

or

On Error Resume Next 'Avoid using

Prefer using:

On Error GoTo <line> 'Prefer using

---

On Error GoTo 0

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

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

---

On Error GoTo <line>

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:

• Undo anything that is partially completed
• Close files
• Reset screen updating
• Reset calculation mode
• Reset events
• Reset mouse pointer
• Call unload method on instances of objects, that persist after the End Sub
• Reset status bar