How to check whether certain sheets exist or not in Excel-VBA? [duplicate]

This question already has answers here: Test or check if sheet exists (23 answers) Closed 4 years ago.

Does anyone know how to check whether开发者_StackOverflow中文版 certain sheets exist or not in an Excel document using Excel VBA?

---

Although (unfortunately) such method is not available, we can create our own function to check this..

Hope the code below fits your needs.

Edit1: Added also delete statement...

Sub test()

    If CheckSheet(Sheets(3).Name) then

        Application.DisplayAlerts = False
        Sheets(Sheets(3).Name).Delete
        Application.DisplayAlerts = True

    End If

End Sub

The solution I'd go for...

Function CheckSheet(ByVal sSheetName As String) As Boolean

    Dim oSheet As Excel.Worksheet
    Dim bReturn As Boolean

    For Each oSheet In ActiveWorkbook.Sheets

        If oSheet.Name = sSheetName Then

            bReturn = True
            Exit For

        End If

    Next oSheet

    CheckSheet = bReturn

End Function

Alternatively, if you don't mind to use code that actively raise errors (which is not recommended by common coding best practices) you could use this 'Spartan Programming wannabe' code below...

Function CheckSheet(ByVal sSheetName As String) As Boolean

    Dim oSheet As Excel.Worksheet
    Dim bReturn As Boolean

    For Each oSheet In ActiveWorkbook.Sheets

        If oSheet.Name = sSheetName Then

            bReturn = True
            Exit For

        End If

    Next oSheet

    CheckSheet = bReturn

End Function


Function CheckSheet(ByVal sSheetName As String) As Boolean

    On Error Resume Next
    Dim oSheet As Excel.Worksheet

    Set oSheet = ActiveWorkbook.Sheets(sSheetName)
    CheckSheet = IIf(oSheet Is Nothing, False, True)

End Function

---

Something like this will get you started:

On Error Resume Next

Dim wSheet as Worksheet
Set wSheet = Sheets(1) ' can also be a string, such as Sheets("Sheet1")

If wSheet Is Nothing Then
    MsgBox "Worksheet not found!"
    Set wSheet = Nothing ' make the worksheet point to nothing. 
    On Error GoTo 0 
Else 
    MsgBox "Worksheet found!"
    Set wSheet = Nothing ' set the found Worksheet object to nothing.  You can use the found wSheet for your purposes, though.  
End If

This code was based on http://www.ozgrid.com/VBA/IsWorkbookOpen.htm. Look for the DoesSheetExist() sub.

Hope this helps!

---

I adapted this code for use in LotusScript, one of the languages used by IBM Notes (formerly Lotus Notes) as shown below.

Public Function ExcelSheetExists( _
    xlBook As Variant, _ ' Excel workbook object
    ByVal strSheetName As String _
    ) As Boolean

    On Error GoTo errHandler

    ForAll xlSheet In xlBook.Sheets
        If xlSheet.Name = strSheetName Then
            ExcelSheetExists = True
            Exit Forall
        End If
    End ForAll

    GoTo Done

errHandler:
    ' Call MyCustomErrorHandler()
    Resume Done
Done:

End Function

---

On Error GoTo Line1
If Sheets("BOX2").Index > 0 Then
Else
Line1: MsgBox ("BOX2 is missing")
end if 

I did it this way:)