On Error GoTo 0

[tiredofit]

In the sub below, should the On Error GoTo 0 be placed immediately after the line that might cause an error or should it be placed at the end, or indeed in both places?

    On Error GoTo Errhandler

        Worksheets("Sheet10").Activate

    'On Error GoTo 0 'SHOULD THIS BE PLACED HERE???????
    
    Range("A1").Value = 123
    
    GoTo Anothersub
    
Errhandler:
    
    Worksheets.Add.Name = "Sheet10"
    
Anothersub:
    
    On Error GoTo 0
    
    Call SomeModule.SomeSub

 

[RoryA]

Neither, really. You should use On Error Resume Next to assign the sheet to a variable, test if it's Nothing and add the sheet if so (you could put that into a separate function). Then On Error Goto your error handler, if you have one. You should not be jumping about all over the place with Goto statements.

 

[tiredofit]

I was following this:

https://www.wiseowl.co.uk/videos/excelvba/error-handling.htm

So is the video displaying bad practice?

 

[RoryA]

If that's what it shows, then yes, in my opinion.

 

[tiredofit]

So you suggest the following?

    If Not (FnCheckWorksheetName.CheckWorksheetName(wksname:="Sheet10")) Then Worksheets.Add.Name = "Sheet10"
        
    Range("A1").Value = 123<strike></strike>
  
    Call SomeModule.SomeSub

The function:

Public Function CheckWorksheetName(ByRef wksname As String) As Boolean
    
    Dim wks As Worksheet
 
    On Error Resume Next
    
        Set wks = ThisWorkbook.Worksheets(wksname)
    
    On Error GoTo 0
    
    CheckWorksheetName = Not wks Is Nothing
    
    Set wks = Nothing
    
End Function

 

[RoryA]

Something along those lines, yes.

 

[tiredofit]

Thanks