1

I have the following code that's not working as expected:

If Sheets("a") <> "" Then MsgBox ("sheet a exists")

How can I tell if sheet a exists in the workbook?

4

I'd make a separate function for it:

Function SheetExists(SheetName As String)
    On Error GoTo no:
    WorksheetName = Worksheets(SheetName).Name
    SheetExists = True
    Exit Function
no:
    SheetExists = False
End Function

Then you can easily call it where needed, even in a formula if you wanted:

Sub ABC()
    If SheetExists("Test") Then
        MsgBox "Yay!"
    Else
        MsgBox "Boo!"
    End If
End Sub

or

=If(SheetExists("Test"),"Yay!","Boo")
2
  • The use of GoTo and labels in structured programming is considered bad form and can usually be avoided with a little extra thought. – Andrew P. Aug 24 '17 at 13:49
  • 1
    @AndrewP. VBA error handling consists of GoTo or Resume, both of these are far from wonderful, but I've always felt I have more control over a GoTo than a Resume as a Resume could easily pass unnoticed without a conditional check on an error result. I'm always willing to learn though, so I'm looking forward to seeing your answer on the correct way to handle this :) – Jonno Aug 24 '17 at 14:01
2

Something like:

Sub DoesSheetExist()
    Dim s As Worksheet

    For Each s In Sheets
        If s.Name = "a" Then
            MsgBox "Sheet a exists"
            Exit Sub
        End If
    Next s
    MsgBox "Sheet a does not exist"
End Sub
2
  • I like the descriptive sub name =) – Jonno Feb 16 '16 at 17:50
  • @Jonno ....................it's fixed................ – Gary's Student Feb 16 '16 at 18:10
1

You can check for the error. eg:

Dim A As String
    On Error Resume Next
        A = Worksheets("a").Name
        Select Case Err.Number
            Case 9
                MsgBox "Sheet ""a"" does not exist"
            Case 0
                MsgBox "Sheet ""a"" exists"
            Case Else
                Stop
        End Select
    On Error GoTo 0
0

In the template that I made for AutoFilter, I used a function and procedure to check whether a worksheet is available. In the codes below, it is checked whether the DATA sheet is in the workbook :

Sub Sht_Cntrl()
    If Not Sheet_Exists_Cntrl("DATA") Then
    ThisWorkbook.Sheets.Add( _
    After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = "DATA"
    End If
 End Sub

Function Sheet_Exists_Cntrl(SheetName As String) As Boolean
    Dim sht As Excel.Worksheet
    On Error GoTo eHandle
    Set sht = ThisWorkbook.Worksheets(SheetName)
    Sheet_Exists_Cntrl = True
    Exit Function
eHandle:
    Sheet_Exists_Cntrl = False
End Function

When the Sht_Cntrl procedure is called (Call Sht_Cntrl) it is created if DATA sheet does not exist.

enter image description here

You can view the codes by downloading the sample template from this page : Vba autofilter with listbox

-1
If (Worksheets("a").Name <> "") Then MsgBox ("sheet A exists")

this works as expected

1
  • 2
    Unless it doesn't exist, then it may throw a subscript out of range error? – Jonno Feb 16 '16 at 17:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.