How to check if a sheet exists in an Excel workbook using VBA?

To check if a sheet exists in an Excel workbook using VBA, you can create a function that attempts to access the sheet by name and handles any potential errors. Below is a sample function and a subroutine to demonstrate how to use this function:

Function SheetExists(sheetName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(sheetName)
    On Error GoTo 0
    SheetExists = Not ws Is Nothing
End Function

Sub CheckSheet()
    Dim sheetName As String
    sheetName = "Sheet1" ' Replace with the name of the sheet you want to check

    If SheetExists(sheetName) Then
        MsgBox "The sheet '" & sheetName & "' exists in the workbook."
    Else
        MsgBox "The sheet '" & sheetName & "' does not exist in the workbook."
    End If
End Sub

Explanation:

SheetExists Function:

The function takes a single argument `sheetName`, which is the name of the sheet you want to check.
It uses error handling with `On Error Resume Next` to ignore any errors that might occur when attempting to set the `ws` variable to the sheet.
If the sheet exists, `ws` will be set to that worksheet, otherwise it will remain `Nothing`.
The function returns `True` if `ws` is not `Nothing`, indicating the sheet exists, otherwise `False`.

CheckSheet Subroutine:

This subroutine demonstrates how to call the `SheetExists` function.
It assigns a sheet name to `sheetName` and calls `SheetExists`.
A message box is displayed informing the user whether the sheet exists.

You can input the name of the sheet you’d like to check into the `sheetName` variable and run the `CheckSheet` subroutine to verify the existence of the sheet in the current workbook.

facebooklinkedin