How to check if certain worksheets exist in Excel-VBA?

Question

Does anyone know how to check if certain sheets exist in an Excel document using Excel VBA?

Answer 1

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

We hope that the code below meets your needs.

Edit1: Added 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 

A solution for which I would like ...

 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 using code that actively raises errors (which is not recommended using common coding rules), you can 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 

Answer 2

Something like this will let you get 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 . Find the DoSheetExist () submenu.

Hope this helps!

Answer 3

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 

Answer 4

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

I did it like this :)