Error handling and err object

Question

While coding in vba, I noticed that when we are expecting an error and then write "on error resume next". And when an error really occurs, then will the err value be increased to 1. A simple sample code below.

Dim ws as worksheets
    On error resume next 
     Set ws = sheets("hellosheet")  ' hellosheet doesn't exist 
      If err <> 0 then
        Msgbox "the worksheet hellosheet doesn't exist"
      End if

As you can see hellosheet doesn't exist making the code to be error. Will the err value in this part be increased to 1. Please help me understand the logic if i am missing something. Thank you

If you step through the macro with `F8`, when (or right before) the error line, you can hover over `err` and it should show you the error. Or, add `Debug.print "Error is: " & err` and it'll print the err number to the Immediate Window. If you want error handling, perhaps [this thread](http://stackoverflow.com/q/6688131/4650297) may help. [Or this one](https://stackoverflow.com/questions/6040164/excel-vba-if-worksheetwsname-exists) which doesn't use Error Handling. — BruceWayne, Mar 17 '17 at 13:56
There's a 'Documentation' link at the top of this page, with a topic dedicated to error handling in VBA. Did you try researching a bit? — Mathieu Guindon, Mar 17 '17 at 14:11

score 1 · Answer 1 · answered Mar 17 '17 at 14:08

Excel has hundreds, if not thousands, of different errors. MS has the all numbered and catalogued and each has a number and description.

Err.Number
Err.Description

You can specify what the code should do when an error occurs. The idea is that you direct the code to error handling code which examines what kind of error it is and what to do about it. For example, if the sheet you tried to open doesn't exist you might want the code to create it and continue to work with that sheet as if no error ever happened.

Another way to deal with an error is to use

On Error Resume Next

On the next line you might have a test, like

If Err Then
    ' what to do
End if

This would be perfect for creating a sheet which was found missing, but you might want to test that it was really that error which occurred.

After an error occurred you might want to use the command Err.Clear before you make the next test for whether an error occurred because Excel will remember the first one, where you told it to continue doing the next command.

Important caveat with `On Error Resume Next` - this answer should mention `On Error GoTo 0` to reinstate runtime errors *as soon as reasonably possible*, otherwise OERN shuts off error handling, and then more subtle/annoying bugs invariably happen. — Mathieu Guindon, Mar 17 '17 at 14:10

score 0 · Answer 2 · answered Mar 17 '17 at 14:06

The error number will actually be 9 in this case; however, you won't get a messagebox like that on Resume Next unless you hard code it as the next line. While there are instances where Resume Next is what you would want to do, in your case you will need to actually trap the error. This will require it to be set it up differently.

Private Sub()
Dim ws as Worksheet
On Error GoTo There_is_an_Error
Set ws = Sheets("HelloSheet")
<other code>

Exit_There_is_an_Error:
Exit Sub

There_is_an_Error:
MsgBox "The Worksheet doesn't exist!"
Resume Exit_There_is_an_Error
End Sub

Error handling and err object

2 Answers2