check if sheet exists vba code example

Example 1: excel vba test or check if sheet exists

'VBA function to test if a sheet exists in the active workbook:

Function IsSheet(n$) As Boolean
    IsSheet = Not IsError(Evaluate(n & "!a1"))
End Function

'-----------------------------------------------------------------------

'Same thing, different technique (much faster):

Function IsSheet(n$) As Boolean
    On Error Resume Next
    IsSheet = Sheets(n).Index
End Function

Example 2: excel vba test if worksheet exists

Function WorksheetExists(pShtName As String, Optional pWb As Workbook) As Boolean
    Dim sht As Worksheet

  If pWb Is Nothing Then Set pWb = ThisWorkbook
    On Error Resume Next
    Set sht = pWb.Sheets(pShtName)
    On Error GoTo 0
    WorksheetExists = Not sht Is Nothing
End Function

Example 3: excel vba existence of a file

' Existence of a file 
If Dir("C:\myDirectory\myFile", vbDirectory) = vbNullString Then
    MsgBox "Doesn't exists"
Else
    MsgBox "Exists"
End If