10

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

Lance Roberts
  • 22,383
  • 32
  • 112
  • 130
Vivian
  • 1,071
  • 5
  • 16
  • 29

4 Answers4

13

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
Tiago Cardoso
  • 2,057
  • 1
  • 14
  • 33
  • This is also an excellent solution -- checks without relying on errors. I knew there was a better way, and I had used this way before at work, I just couldn't remember how to do it. @Tiago: How would you use it on Sheets(1)? Just pass in Sheets(1).Name? – Paul McLain Jul 27 '11 at 02:32
  • @PaulR, yeap, exactly... `Sheets(1).name` would do the trick. Notice you may need to actively define the workbooks you're going to use, since a 'sheet' reference implicitly points to the active workbook (which is not the expected, sometimes). – Tiago Cardoso Jul 27 '11 at 02:43
  • Right, I've had that bite me at work sometimes. For larger operations, I tend to use a With block for my Worksheets("SheetName") object, and call it good. This would be, of course, after I made sure Worksheets("SheetName") existed. – Paul McLain Jul 27 '11 at 02:47
  • Hi @Tiago Cardoso thanks for answering. But sorry if i've asking amateur questions here. I'm rather new to excel-vba, but with my context I've checking if these sheets(2) and (3) exist i've wanted to initial a delete function but now with your above codes. How should implement it this function? – Vivian Jul 27 '11 at 03:01
  • Hi @PaulR i have updated my code above as i have encounter some problem while implementing Tiago Cardoso code. Can you take a look and give me some suggestions here as to how to solve it. Thanks – Vivian Jul 27 '11 at 04:04
  • @Tiago Cardoso I have updated my codes above can you take a look and give me some suggestion as to how can i solve it? – Vivian Jul 27 '11 at 04:06
  • @Vivian added now the deletion code, hope it's better now. Going to bed, I'll see if everything is fine tomorrow. ;) – Tiago Cardoso Jul 27 '11 at 04:21
  • @Vivian, your deletion code based on Tiago's looks sound. – Paul McLain Jul 27 '11 at 12:29
  • Actually in VBA it is best practice (and many times the only way) to determine a lot of things by catching errors. – Lance Roberts Jul 28 '11 at 03:34
  • Agree, @Lance. My point is that in this specific case of 'Resume Next', we are actively *creating* an error instead of *checking* if an error would be raised. A simple way to make it break would be to change VBA's Error Trapping from *Break on Unhandled Errors* to *Break on All Errors*. Ideally, IMO, our applications would run smoothly with the Error Trapping set to *Break on All Errors*, but that's not the case for most of VBA developers. – Tiago Cardoso Jul 28 '11 at 11:34
3

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!

Paul McLain
  • 361
  • 3
  • 12
0

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
John D
  • 1
  • 2
0
On Error GoTo Line1
If Sheets("BOX2").Index > 0 Then
Else
Line1: MsgBox ("BOX2 is missing")
end if

I did it this way:)

Rens Slenders
  • 1
  • 1