On Error

[jebenexcel]

Hello,

I have a macro that might refer to nonexisting sheets. I want the macro to, let's say, select the A1 Cell on the sheet a if an error occurs.
How do I implement this with On Error Go To?

 

[Johnny C]

Avoid On Error Goto unless it's to exit the VBA cleanly altogether. You're better having a Sub you can call.

On Error Resume Next
...... refer to non-existing sheet
If Err.Number <> 0 Then
     On Error Goto 0
     Call ErrorCode
End If
On Error Goto 0
... rest of code

Sub ErrorCode()
Sheets("a").Activate
Range("A1").Select
End Sub

 

[Johnny C]

The problem with On Error Goto x is that VBA doesn't know where the Goto was called from, so you would need to use a Goto back to the statement after the error. Which is fine if it's one macro and the error can only occur in one place. But if you subsequently add another bit of code where the error might occur, you need to start using flags to tell the error code where to go back to. It gets messy. it's simpler to just have a Sub which automatically returns to where it was called from.

The issue doesn't arise if you are just cleanly exitting a sub after an error, but it's a good habit not to use On Error Goto with anything other than 0. Even if you want to exit after a fatal error, you can use

On Error resume Next
...
If Err.Number <> 0 Then
     Call ErrorHandler
     Exit Sub
End If
On Error Goto 0

I studied Comp Sci at university, it was a mandatory fail on any assignment in any language if you used GoTo - a lesson I learnt very quickly and I've never used a Goto since (30 years cough cough)

 

[RoryA]

The problem with On Error Goto x is that VBA doesn't know where the Goto was called from, so you would need to use a Goto back to the statement after the error. Which is fine if it's one macro and the error can only occur in one place. But if you subsequently add another bit of code where the error might occur, you need to start using flags to tell the error code where to go back to. It gets messy.

Not really - either Resume or Resume Next should suffice.

 

[jebenexcel]

I just solved my problem using If clauses ( referring to the existing sheet ordered by number only if they exist)

 

[Johnny C]

That's alwways the best solution - avoiding the chance of errors in the first place