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


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: Worksheets("My Worksheet").Select

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!

Please share

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.



Download our Excel VBA Run time errors cheat sheet!

Enter your First Name & Email to get our Excel VBA Runtime errors cheat sheet and subscribe to our Newsletter!

Powered by ConvertKit

Get Free Email Updates!

Signup now and receive an email once I publish new content.

I agree to have my personal information transfered to ConvertKit ( more information )

I will never give away, trade or sell your email address. You can unsubscribe at any time.

It's only fair to share...Share on Facebook
0Pin on Pinterest
0Tweet about this on Twitter
Share on LinkedIn