Error handling is an important aspect of programming in VBA, especially if you are writing macros for other users. Unfortunately, many users ignore it completely. Visual Basics is an amazing programming language, but it lags far behind in the error handling department. All we have is the “On Error”, “Goto” and the “Resume” statements. These statements allow only a few error handling structures, and each of the structures has its own set of expert proponents. In this post, I am going to share with you, a little block of code that I use to handle errors in all my spreadsheet applications; and hopefully offer a fresh perspective.

Let us try to understand how Visual Basics deals with Errors first. Visual Basics uses the inbuilt Err Object, to help users handle errors. It fills the object with useful information every time an error occurs. Each error has a Number associated with it, and has a corresponding short Description. The Err Object has another property called Source, that lets the user know what generated the error.

An Error might be generated by:

Visual Basics, when it encounters a runtime error. The source, in this case is the project’s name. An object, when the user assigns an invalid value to its property, or when a method does not run successfully. In this case, the source would be the object’s class name. The User, by using the Raise Method. The user may choose to explicitly define the source.

Note that each time the ‘Exit Sub’, ‘Exit Function’, ‘Resume’ or ‘On Error’ statements executes, the Err object is reset automatically. You may also explicitly reset it by using the Clear Method. You may use the ErrorToString Function to find the Description corresponding to an Error Number

Now that we have covered the basics, we can concentrate on the two important things we need to consider when an error occurs:

First, we need to let the user know the specifics of the error: Number, Description, Source, an optional custom message, and perhaps, a title. The other consideration is, what happens after displaying the error message. The user may choose to rectify the situation by ending the execution, or allow the execution to continue.

This is the Sub that I was talking about:

'##################################################################### 'ShowError : Macro to Display Error Messages '##################################################################### 'Author : Ejaz Ahmed 'Email : StrugglingToExcel@outlook.com 'Date : 15 April 2013 'Website : https://strugglingtoexcel.wordpress.com/ '##################################################################### Sub ShowError(Optional ByVal ErrorMessage As String = vbNullString, _ Optional ByVal MsgBoxTitle As String = vbNullString) 'Declare Sub level variables Dim strMessage As String Dim lngAns As Long Dim ContinueExecution As Boolean Dim MsgBoxStyle As Long 'Set a Default title for the Error Message box MsgBoxTitle = IIf(MsgBoxTitle = vbNullString, "Struggling To Excel", MsgBoxTitle) 'Set a Default Message for the Error Message box strMessage = IIf(ErrorMessage = vbNullString, "An Error has occurred.", ErrorMessage) 'Add the details of the error, if an error actually occurred If Not Err.Number = 0 Then strMessage = strMessage & vbNewLine & vbNewLine & _ &"Error Number" & vbTab & ": " & Err.Number _ & vbNewLine & "Error Source" & vbTab & ": " & Err.Source _ & vbNewLine & "Description" & vbTab & ": " & _ Err.Description strMessage = strMessage & vbNewLine & vbNewLine & _ "Would you like to Continue Execution?" End If MsgBoxStyle = IIf(Err.Number = 0, vbCritical, vbYesNo + vbCritical) 'Display the Error Message Box lngAns = MsgBox(strMessage, MsgBoxStyle, MsgBoxTitle) ContinueExecution = IIf(lngAns = vbYes, True, False) 'If the user chooses to continue, reset the Err Object If ContinueExecution Then Err.Clear On Error GoTo 0 Else Application.StatusBar = False Application.ScreenUpdating = True Application.EnableEvents = True Application.DisplayAlerts = True End End If End Sub

You can set the title of the message box; display a custom message; display error details; and choose to end or continue with the execution. I usually set the title to ‘ModuleName.SubName’, so I know which Sub generated the error precisely. And write a brief note suggesting what the user should try doing. I am not a big fan of the “On Error Goto Label” statements; I prefer using the “Resume Next” construction.

Here is an example of how I call the above sub:

'Example Error Handling Structure Sub ErrorHandling01() 'Declare Your Variables Dim strSheetName As String Dim strMessage As String Dim strTitle As String Dim wksSheet As Worksheet 'Initialize Variables strSheetName = "Hypothetical Sheet" 'Start with the On Error Resume Next Statements On Error Resume Next 'Code that may produce an error Set wksSheet = ActiveWorkbook.Worksheets(strSheetName) 'Check if an Error Occured. Err.Number = 0 implies No Errors 'occured If Not Err.Number = 0 Then 'Display the Message strMessage = "The Sheet, " & Chr(34) _ & strSheetName & Chr(34) & ", does not exist." strTitle = "Error Message Box Title" Call ShowError(strMessage, _ strTitle) End If 'This part of the code is only reached if the user chose to 'continue execution after displaying the error message. MsgBox "The User Chose to Continue Execution" End Sub

Download ‘Error Handling.xlsm‘ from Dropbox and check it out.