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 : http://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.
I quite like this, but it’s a lot of code to do not much. I normally handle errors as below:
Sub GetOrderNumber()
Dim variables etc
exitSub:
On Error GoTo 0
Exit Sub
errorSub:
MsgBox “GetOrderNumber had error:” & Err.Description
Resume exitSub
Resume
End Sub
You may ask why I have ‘Resume exitSub’ followed by another ‘Resume’, as, theoretically, you never will get to the Resume. Well this is for debugging. If you get an error, then you can break into Debug Mode (Ctrl&Break), then drag the little yellow ‘current line’ arrow down from ‘Resume exitSub’ to ‘Resume’, so that you can tell which line of your code actually caused the error. As far as I’m aware, there’s no other way of detecting this.
Happy coding!
Hi Andy. Nice Framework actually, but I am not a big fan of it for a lot of reasons.
I think a good programmer should expect when an error might occur, and deal with it immediately. And that is how I code my macros, I expect an error to happen, If It does, I display the error immediately. And my ShowError sub makes it a breeze. I feel that this philosophy is far better than just telling the end user “Hey..Something is wrong…And you can’t do anything about it…And I don’t know what went wrong either”
The essence of this post is to give my readers a reusable sub that they can call whenever. Your framework requires adding all those lines for every sub and If you think about it, essentially you are adding a lot more lines to your project than I would.
The reason I have a lot of lines in there is for it to be as versatile as possible:
1. I can call it without any arguments to just show an error description
2. I give the user an option to display a custom message and title
3. I also give the user an option to try continuing with the execution despite the error
4. I can call it even if an error did not occur, just to give the user a warning, and let the user decide if they’d like to proceed with the execution. For instance, if I call the sub without realizing any errors, it skips printing the error description part.
I have, reduced the number of lines now, by shifting around the code a little, and using the IIF function. If you still think the code doesn’t do much, let me know how I can make it better.
In conclusion, My Error Handling Philosophy is :
And the ShowError Sub helps you to display a message effectively: call it by typing in one line.
Nice Code. Could you please post workbook for download?
Hi Bhavik! I am glad you found this useful. Since it was just one sub, I did not upload a workbook. Did you have troubles copy pasting the code into your project?
I shall upload a workbook now.
I have added the link towards the end of the post.
Here is the link for your Convenience:
https://www.dropbox.com/s/j9z15gew5dxz92y/Error%20Handling.xlsm
I can certainly understand your point about displaying meaningful errors to the user. However, your implementation seems flawed for the following reasons:
1) Your “On Error Resume Next” forces the programmer to handle EVERY error, not just the expected ones. For example, buffer overflows, out of range errors, divide by zero, etc. That seems to defeat the purpose of error routines and being able to display Err.Description.
2) Your “If Not Err.Number = 0” assumes that whenever an error happens, only one type of error will happen, and the programmer knows exactly what message needs to be displayed to the user.
3) If your argument against #2 is that your method displays the correct error via Err.Description, what if your passed-in message differs from the displayed Err.Description? Now you have 2 conflicting error messages in your pop-up.
4) Do you really expect a user to know if they can/should ignore the error and continue?
No, thanks, I’ll continue to use “On Error Goto” and display Err.Description.
Hi Tom,
If err.number = 0 is just an example. You’d know what error to expect depending on your code. For instance, if i am trying to initialize a worksheet object with the sheet name, and if i get an error, i know that sheet does not exist. These are the types of errors I suggest you handle with this methodology.
Also, I am just appealing to my readers to explicitly handle exactly which error you expect. The reason I gave the user an option to display a custom message in addition to the error description is for them to use it with a on-error-goto set-up also. They can just call this sub with a “An Error as Occurred” message, instead of having to construct the message on each sub they write.
Let me give you a scenario where the continue option is useful. Say you are processing a list of files in a loop, and they are independent of each other. You may want to give the user the option to process the rest of the files, even if one iteration failed. Again, these are all just ideas. I think i will add a boolean option to let the developers decode if they’d like the user to get the choice to continue.
Thanks for the feedback Tom. You gave me a great idea.
hi,
do you know HOW TO PASS AN OBJECT THAT CAUSED THE ERROR TO A GLOBAL ERROR HANDLER so that the errorhandler can tackle that error and return control to the calling proc with a RESUME (back to error line)?
Why would you want to pass the object to the Error Handler? That is not required for the program control to return to the place where the error happened.
May be I did not get your question right. Can you give me an example?