Excel – How to tell if sheet a exists in a workbook

microsoft excelvbaworksheet-function

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?

Best Answer

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")
Related Question