Excel VBA Exception Handling -How to turn errors into friends

When a VBA code is running it can ( and often it will )  encounter some errors. The all topic of errors is defined Excel VBA Exception Handling

Errors can be of 2 types:

1. Syntax errors that need to be corrected before you can run the code

2. Runtime errors that occur when the code is running  

This post deals with Runtime errors explaining not only how you can identify errors and avoid them blocking the code,  but also how you can obtain valuable information and use it to better manage your code .

 On Error statements tell VBA to deal with errors. 

There are two key statements that can be used to deal with errors.

1. On Error Resume Next: It tells VBA to ignore the error and continue with the execution of the rest of the code. The error can be examined and specific actions can be foreseen. A use of this statement in action can be seen at the following article on Pivot Tables 

2. On Error Go To: Tells the code to jump to a dedicated error handling section of the code 

Before starting to use Error Handing procedures the VBA Module has to be set up correctly 

In the tools --> Option menu of the VBE Menu Bar the "Break on Unhandled Error" option has to be selected

Once the VBE has been set up correctly we can now start investigating the power of Excel VBA Error Handling 

A case in which Excel VBA will return a runtime error is when it is asked to refer to objects that do not exist

The below code shows the previous routine with the insertion of the On Error Resume Next statement. The statement is placed before the lines of code that can generate an error

If the error occurs, the Number property of the Err object gets populated with a number different from zero 

If the Err.Name property is different from zero, an error has been encountered (i.e. no sheet called "John" exists) and therefore  a new sheet is created and given the name of "Frank". 

In case it is equal to zero, the workbook does contain a sheet called "John". Therefore is renamed to "Frank" and no additional operation is executed by the code 

 The final statement On Error Go To 0 resets VBA back to the standard error handling method

Sub errorhandling()

On Error Resume Next

Worksheets("John").Name = "Frank"

If Err.Number <> 0 Then

Sheets.Add

ActiveSheet.Name = "Frank"

Else: End If

 On Error GoTo 0

End Sub

The second method foreseen by Excel VBA Exception Handling is On Error GoTo

This method foresees that when the error is identified the code jumps to a different part of the code to execute lines aimed at handling the error

In the below routine the On Error GoTo statement tells the code to jump to the myError section if an error is identified. The myError section displays a message that no sheet called "John "exists

If an error is not identified the code renames the sheet called John to Frank and it exits the sub before the part dedicated to the error handling. If this was not the case the myError part of the code would run despite no error was identified. 

Sub errorhandling()

On Error GoTo myError

Worksheets("John").Name = "Frank"

Exit Sub

myError:

MsgBox " A sheet called John does not exist"

End Sub