Error Handling in Excel and VBA

As well as doing work in SQL Server and “.net” i sometimes have to write VBA code for clients and error handling in Excel is a common requirement when writing VBA so i decided to write an article on VBA error handling. If you would like to watch a video on this article and visually see me stepping through this code while debugging then please watch below otherwise skip over it for the transcript:

If you do not follow the GetGeek.Net Youtube channel already then please subscribe to it.

This tutorial applies to Microsoft Office 2016 and is also applicable to Office 2013 and Office 2010 for definite. It may apply to earlier versions such as Office 2003 and Office 2007 but i have not tried this personally. If this is possible then please let me know in the comments.

Error Handling in Excel

In your sub or function, add a line under the sub or function declaration stating “On Error GoTo ErrHandler:” as per the below code

Private Sub TestErrorHandling()
On Error GoTo ErrHandler:

'....Continued below

The “On Error GoTo” is code that must always be present and is a pointer saying that when the application errors then go to the the label “ErrHandler:” you can call the label whatever you like but i tend to use “ErrHandler:” as it makes sense when reading it aloud.

We then want to copy the “ErrHandler:” label and paste it before the end of the sub where we can catch errors using the specific “Err.Number” in a case statement or just do a generic statement where we show a message box with the error description. At the end of the day, how you handle this is up to yourselves. After you have finished handling the error you want to call a “Resume” followed by a label which can be given any title, but i tend to give it the name of the function concatenated with an “_Exit“. In our example it would look like the following: “Resume TestErrorHandling_Exit:"  This then causes the code to jump to a label which we will place before the “ErrHandler:“. Underneath this newly pasted label we will just write code to exit the sub or function as per the below code snippet:

'...Code from earlier
TestErrorHandling_Exit:
    Exit Sub
ErrHandler:
    Select Case Err.Number
        Case 11
            MsgBox "There was a division by 0. Please try again"
        Case Else
            MsgBox Err.Description
    End Select
    Resume TestErrorHandling_Exit:
End Sub

If you have a sub or function that does not error then the “ErrHandler:” code will not fire because the code will always exit through the “TestErrorHandling_Exit:" label that preceeds it. For a visual example of this please see the above video where i step through this while debugging. The full code from the example is below:

Private Sub TestErrorHandling()
On Error GoTo ErrHandler:

Dim intResult As Integer
intResult = 1 / 0

TestErrorHandling_Exit:
    Exit Sub

ErrHandler:
    Select Case Err.Number
        Case 11
            MsgBox "There was a division by 0. Please try again"
        Case Else
            MsgBox Err.Description
    End Select
    Resume TestErrorHandling_Exit:
End Sub

Error Handling In Excel

I hope this article has helped you with VBA error handling. If you have any questions relating to error handling in excel then please let me know in the comments and i will get back to you. I’m also debating doing an article on when to use error handling so if you’d like to see this then let me know.

Comments

comments