VBA Tips

The focus of this week’s column is error handling. Learning basic error handling is essential knowledge for writing more robust VBA code. I’m going to demonstrate two simple strategies. These strategies assume that you are using the default Break on Unhandled Errors error trapping option. Check the setting for this option by selecting Tools/Options in the Visual Basic Editor. This option is located on the General tab.

Figure 1: The default runtime error experience

Before moving into basic error handling strategies, I’ll demonstrate the default strategy — no error handling. If there is no error handling present, VBA will display a runtime error when an error occurs and present the option to either End the macro or Debug it. Listing 1 shows an example listing that will generate a division by zero error.

Listing 1: A procedure that does not use error handling.

Sub NoErrorHandlingStratgy()
Dim x As Integer

' This line will only be reached if no errors occur.
MsgBox "Finished - without errors"
End Sub

Running Listing 1 produces the dialog box shown in Figure 1.

The error-handling strategy is simply to ignore any errors and continue executing code. As an example, consider Listing 2. This listing attempts to do the same thing as Listing 1 except it includes the statement On Error Resume Next near the beginning of the procedure.

Listing 2: A procedure that ignores all errors

Sub IgnoreEverythingStratgy()
Dim x As Integer

' This line will be reached no matter what.
MsgBox "Finished - with or without errors"
End Sub

This strategy is helpful when you have procedures in which the individual statements don’t rely on previous statements. In other words, if one line doesn’t execute due to an error, there are no adverse affects to the remaining statements.

If there a procedure is performing more critical operations and there is a dependency between the various statements in a procedure, it might be better to gracefully quit or exit the procedure the first time that an error occurs. At first, this might sound exactly like what you get by not using any error handling. However, there are critical differences.

First, you get to control the user experience rather than the VBA execution engine. For example, you might not want to give the user the ability to debug your code. Or you might choose to not even notify the user that an error occurred. Finally, if you have other procedures that need to run after the procedure that caused the error, these other procedures can still run. Without error handling, all execution stops and any following procedures will not be run.

To use this strategy, use the On Error GoTo ErrHandler statement. ErrHandler is actually a line label - you can use a different label if you prefer. Listing 3 shows an example of this strategy.

Listing 3: A procedure that uses an error handler

Sub QuitOnFirstErrorStrategy()
Dim x As Integer

' The following code only runs if an
' error occurs.
ErrHandler:
MsgBox "Did not finish - error: " & _
Err.Description
End Sub

These two basic strategies can significantly improve your ability to write more user friendly and robust code. Next week I’ll demonstrate additional error handling strategies that provide even more flexibility to selectively handle some errors but not others.