Why do I get run-time error 9 message: Expecting object to be local when I run this code?

Question

I am in need of assistance in resolving an issue I have ‎been experiencing when running certain codes, ‎particularly the one listed below. While I ‎comprehend why this error message may appear, I ‎am unsure as to why it is occurring with this ‎particular code. I have been receiving the 'expecting ‎object to be local' error message and 'subscript out ‎of range error message when no sheet with the ‎name is declared in the, despite the code's purpose ‎being to determine if the sheet is already present or ‎not and create it if it does not exist.‎

I'll be thankful to those who would put a hand into ‎this.‎

By the way, the code stops at the step of setting the WS.

Sub Check_Sheet_Exists()


    Dim WS As Worksheet
    Dim SheetName As String

    SheetName = "ABCD"

   '    On Error Resume Next
    
    Set WS = ThisWorkbook.Sheets(SheetName)

    If WS Is Nothing Then
        Sheets.Add before:=Sheets(Sheets.Count)
        ActiveSheet.Name = SheetName
        MsgBox "The sheet '" & SheetName & "' was created."
    Else
        MsgBox "The sheet '" & SheetName & "' already exists."
    End If
 
End Sub

Code stuck here

Someone help me solve this issue, please.

Answer 1

Added an explicit Workbook reference, and cancelling the OERN as suggested by VBasic2008

Sub Check_Sheet_Exists()
    Dim WS As Worksheet, wb As Workbook
    Dim SheetName As String  'Use Const if the name is fixed...

    SheetName = "ABCD"

    Set wb = ThisWorkbook
    On Error Resume Next 'ignore errors
    Set WS = wb.Sheets(SheetName)
    On Error GoTo 0      'stop ignoring errors

    If WS Is Nothing Then
        Set WS = wb.Worksheets.Add(before:=wb.Sheets(wb.Sheets.Count))
        WS.Name = SheetName
        MsgBox "The sheet '" & SheetName & "' was created."
    Else
        MsgBox "The sheet '" & SheetName & "' already exists."
    End If
 
End Sub

Answer 2

Add Worksheet With Specific Name

Sub AddWorksheet()
    On Error GoTo ClearError
    
    Const PROC_TITLE As String = "Add Worksheet"
    Const SHEET_NAME As String = "A\BCD"
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Check if sheet name is taken.
    
    Dim sh As Object
    
    On Error Resume Next
        Set sh = wb.Sheets(SHEET_NAME)
    On Error GoTo ClearError

    If Not sh Is Nothing Then
        MsgBox "The sheet """ & SHEET_NAME & """ already exists.", _
            vbExclamation, PROC_TITLE
        Exit Sub
    End If

    ' Add the worksheet.
    
    Dim ws As Worksheet
    Set ws = wb.Sheets.Add(Before:=wb.Sheets(wb.Sheets.Count)) ' before last
    'Set ws = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count)) ' last
    'Set ws = wb.Sheets.Add(Before:=wb.Sheets(1)) ' first
        
    ' Rename the worksheet.
        
    Dim ErrNumber As Long, ErrDescription As String
    
    ' Atempt to rename.
    On Error Resume Next
        ws.Name = SHEET_NAME
        ErrNumber = Err.Number
        ErrDescription = Err.Description
    On Error GoTo ClearError
    
    ' Invalid Sheet Name.
    If ErrNumber <> 0 Then
        Application.DisplayAlerts = False ' to delete without confirmation
            ws.Delete
        Application.DisplayAlerts = True
        MsgBox "Run-time error '" & ErrNumber & vbLf & vbLf _
            & ErrDescription & vbLf & vbLf & vbLf & PROC_TITLE & " Info" _
            & vbLf & "The name """ & SHEET_NAME & _
            """ is invalid. Worksheet not added.", vbCritical, PROC_TITLE
        Exit Sub
    End If
        
    ' Valid Sheet Name
    MsgBox "The worksheet """ & SHEET_NAME & """ was added.", _
        vbInformation, PROC_TITLE

ProcExit:
    Exit Sub
ClearError:
    MsgBox "Run-time error '" & Err.Number & vbLf & vbLf _
        & Err.Description & vbLf & vbLf & vbLf & PROC_TITLE & " Info" _
        & vbLf & "An unexpected error occurred.", _
        vbCritical, PROC_TITLE
    Resume ProcExit
End Sub