Test or check if sheet exists Test or check if sheet exists vba vba

Test or check if sheet exists

Some folk dislike this approach because of an "inappropriate" use of error handling, but I think it's considered acceptable in VBA... An alternative approach is to loop though all the sheets until you find a match.

Function WorksheetExists(shtName As String, Optional wb As Workbook) As Boolean    Dim sht As Worksheet    If wb Is Nothing Then Set wb = ThisWorkbook    On Error Resume Next    Set sht = wb.Sheets(shtName)    On Error GoTo 0    WorksheetExists = Not sht Is NothingEnd Function

If you are specifically interested in worksheets only, you can use a simple Evaluate call:

Function WorksheetExists(sName As String) As Boolean    WorksheetExists = Evaluate("ISREF('" & sName & "'!A1)")End Function

You don't need error handling in order to accomplish this. All you have to do is iterate over all of the Worksheets and check if the specified name exists:

Dim exists As BooleanFor i = 1 To Worksheets.Count    If Worksheets(i).Name = "MySheet" Then        exists = True    End IfNext iIf Not exists Then    Worksheets.Add.Name = "MySheet"End If