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.

CodePudding user response：

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

CodePudding user response：

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