How to check if Worksheet Exists using VBA Macro

excel vba

← PrevNext →

Tools you can use

While working on worksheets using a macro, you may sometimes need to know if a particular worksheet exists in a workbook or not. Especially, when the worksheets you are working have random names. You either have deleted it or renamed it. I’ll show you how using a macro, you can easily check if a worksheet actually exists or not in your current or another workbook.

Here’s an example.

Option Explicit

Sub executeMacro()
    On Error GoTo ErrHandler
    
    Dim sWS As String
    sWS = "Sheet2"         ' Worksheet to be checked.
    
    If Not chkWorkSheetExists(sWS) Then
        ' This is optional. Create and add the worksheet if not found.
        ThisWorkbook.Sheets.Add _
            (After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)).Name = sWS
    End If
   
ErrHandler:
    '
End Sub

' Check if worksheets exists.
Function chkWorkSheetExists(sSheetName As String) As Boolean
    On Error Resume Next
    
    Dim sSht As Worksheet
    Set sSht = ThisWorkbook.Sheets(sSheetName)

    chkWorkSheetExists = Not sSht Is Nothing    ' Return true if worksheets exists.
End Function

In the above example, I have hardcoded the worksheet name (Sheet2) that I want to check in my current workbook. The property ThisWorkBook returns the current workbook object.

User Defined Function chkWorkSheetExists()

The UDF (or the user defined function) chkWorkSheetExists() returns a Boolean true or false based on the result. The function takes a parameter that is the name of the worksheet. And it checks if the given worksheet exists in the current workbook.

ThisWorkbook.Sheets(sSheetName)

There may be other ways to do this in VBA. However, this method also works and its simple.

Programmatically Create and Add a Worksheet if it Does not Exist

It is optional. In-addition, I have added a code to create the sheet if it does not exists. Even though, it is optional, I thought I’ll share this little piece of macro here as would be useful for some.

' Add a sheet "after" the last available sheet.
ThisWorkbook.Sheets.Add _
    (After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)).Name = sWS

You can further simplify the above one-liner using the with clause like this.

With ThisWorkbook
    .Sheets.Add(After:=.Worksheets(.Worksheets.Count)).Name = sWS
End With

Check if Worksheet Exists in another Workbook

In the first example above, I was checking if a particular worksheet exists in the current workbook. Now, using the same method, I’ll check if a particular worksheet exists in another workbook.

The method is simple, except that I have to provide the workbook (or file) path and the name of the worksheet.

Option Explicit

Dim objSrc As Workbook

Sub executeMacro()
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
        
        Dim sWS As String
        sWS = "Year - 2021"     ' Worksheet to be checked.
        
        If Not chkWorkSheetExists(sWS, "d:\\Books.xlsx") Then
            Debug.Print "Worksheet does not exist"
        Else
            Debug.Print "Worksheet exist"
        End If
    
ErrHandler:
    Application.ScreenUpdating = True
End Sub

' Check if worksheets exists.
Function chkWorkSheetExists(sSheetName As String, sFilePath As String) As Boolean
    On Error Resume Next
   
    Set objSrc = Workbooks.Open(sFilePath, True, True)      ' Open the file.

    Dim sSht As Worksheet
    Set sSht = objSrc.Worksheets(sSheetName) 	' Check if the worksheet exists.

    ' Close the source file.
    objSrc.Close saveChanges:=False   ' Close the workbook without saving changes.
    Set objSrc = Nothing
    
    chkWorkSheetExists = Not sSht Is Nothing
End Function

In this example, the user defined function chkWorkSheetExists() takes two parameters, the name of the worksheet and workbook path. It will return a Boolean value (true or false). The source workbook is defined in the beginning of the macro (Dim objSrc As Workbook).

Thanks for reading. ☺

← PreviousNext →