What is the ERR object in Excel VBA?
The ERR object in Excel VBA is an object that contains information about different Run-time errors.
How can I use the ERR object in Excel VBA?
The Err object in Excel VBA can be used to differentiate between different Run-time errors that you encounter on your VBA code so that you can take different actions based on which error you encounter.
Check out the below VBA code:
Sub ErrObject() On Error GoTo errorhandling Worksheets("My Worksheet").Select 'error number 9 Range("a1").Value = 1 / 0 'error number 11 errorhandling: If Err.Number = 9 Then MsgBox "The Worksheet called ""My Worksheet"" doesn't exist. Please create it" ElseIf Err.Number = 11 Then MsgBox "You can't divide by zero!" End If End Sub
On the above code, we’ve got two lines of code that can cause errors.
1-The first one is:
And this will generate an error if the worksheet can’t be selected (because it doesn’t exist for example). The error you get has error number 9 as indicated in the below screenshot:
2- The second one is:
Range("a1").Value = 1 / 0
And this will generate an error because we can’t divide by zero. The error you get has error number 11 as indicated in the below screenshot:
On the above block of code, we can test for a specific error using the .number property for the Err object in Excel VBA to check what error we’re getting and then display a relevant message box.
In other words, using the .number property for the Err object, we are able to be selective of which action(s) to take based on what error we encounter.
You can also check out the below video on my Youtube channel explaining the ERR object – Don’t forget to subscribe to my Youtube channel to get notified about all my new videos!
Did you find this useful? Why not share the love and tell your friends on the different social media platforms.
Also, you can download our Excel VBA runtime errors cheat sheet which lists the error numbers for all runtime errors in Excel VBA and their error messages using the form below.
Want to learn the basics of Excel VBA? You can join my course on Excel VBA fundamentals course for a special price of only $15 using this link.
If you have any questions/comments, please leave them in the comments section below.
- Learn all about the new XLOOKUP Function in Excel! - September 27, 2019
- FOR EACH Loop in Excel VBA - December 15, 2018