Excel VBA: Check Whether a Worksheet Name Exists Safely

To check whether a worksheet exists in Excel VBA, try to assign the named item from the target workbook’s Worksheets collection, then test whether the resulting worksheet object is Nothing. The function below returns True when the worksheet exists and False when it does not.

What the check does

Excel exposes worksheets through a workbook’s Worksheets collection. Looking up a missing name directly, as in wb.Worksheets("Summary"), normally raises a run-time error. A safe existence check anticipates that result instead of letting the error interrupt the rest of a macro.

The function below briefly enables On Error Resume Next for the lookup. If the name is present, VBA assigns the matching worksheet to ws; if not, the assignment fails and ws remains Nothing. The function converts that outcome into a Boolean value that other procedures can use in an If statement.

A reusable worksheet-exists function

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, then put this code in the standard module. On a Mac, use Option+F11 to open the editor (some keyboards require Fn as well); the Visual Basic Editor’s module workflow is similar.

Option Explicit

Public Function WorksheetExists(ByVal sheetName As String, ByVal wb As Workbook) As Boolean
    Dim ws As Worksheet

    ' A missing workbook cannot contain the requested worksheet.
    If wb Is Nothing Then Exit Function

    ' Limit error suppression to the name lookup.
    On Error Resume Next
    Set ws = wb.Worksheets(sheetName)
    On Error GoTo 0

    WorksheetExists = Not ws Is Nothing
End Function

The workbook argument is explicit so the lookup is not accidentally tied to whichever file happens to be active. If wb is Nothing, the function exits with its default Boolean value, False. The short error-handling interval is important: leaving On Error Resume Next active for an entire macro can hide unrelated problems, such as a misspelled variable or a failed operation later in the procedure.

Call the function from a macro

Once the function is in a standard module, another procedure in the VBA project can call it. This example checks for a worksheet named Summary in the workbook that contains the code:

Public Sub CheckForSummary()
    Dim targetWb As Workbook
    Dim requestedName As String

    ' ThisWorkbook is the file containing this VBA code.
    Set targetWb = ThisWorkbook
    requestedName = "Summary"

    If WorksheetExists(requestedName, targetWb) Then
        MsgBox "The worksheet exists."
    Else
        MsgBox "The worksheet does not exist."
    End If
End Sub

ThisWorkbook means the workbook where the macro is stored. It is different from ActiveWorkbook, which means the workbook currently in the foreground. Use ThisWorkbook when the code should always inspect its own file; use ActiveWorkbook only when the user’s currently active file is deliberately the target. This distinction matters when code is stored in the Personal Macro Workbook or an add-in, because ThisWorkbook then refers to that file, not necessarily the workbook being edited.

Choose the workbook you actually mean

A worksheet name has meaning within a workbook, not across all open workbooks as one global list. The same name, such as Summary, can exist in several open files. Passing the intended workbook to the function prevents a false result caused by checking the wrong file.

For a workbook that is already open, you can set the target explicitly, for example with Set targetWb = Workbooks("Quarterly Report.xlsx"). The name must match an open workbook’s name, including its file extension where applicable. If that workbook is not open, the collection lookup raises an error; do not assume this worksheet-existence function also checks whether a workbook is open. Check the workbook separately or open it through a deliberate, error-checked step.

Use Worksheets when the question is specifically whether a worksheet exists. A workbook also has a broader Sheets collection, which includes chart sheets as well as worksheets. That difference is easy to overlook when automating files that contain charts on their own tabs.

Use a loop as an alternative

If you prefer not to use error handling for the lookup, you can examine each worksheet name. This method returns after it finds a match and otherwise reaches the end of the collection:

Public Function WorksheetExistsByLoop(ByVal sheetName As String, ByVal wb As Workbook) As Boolean
    Dim ws As Worksheet

    ' A missing workbook has no worksheets to inspect.
    If wb Is Nothing Then Exit Function

    For Each ws In wb.Worksheets
        If StrComp(ws.Name, sheetName, vbTextCompare) = 0 Then
            WorksheetExistsByLoop = True
            Exit Function
        End If
    Next ws
End Function

StrComp compares the requested name with each worksheet’s name. The vbTextCompare option makes the comparison case-insensitive. Excel does not allow two worksheet names in one workbook to differ only by letter case, so this matches the way worksheet names are normally treated. The loop is useful when you want the comparison to be visible and explicit; the direct lookup is shorter and is commonly used for this task. Both approaches need a valid workbook reference.

Check first before adding a worksheet

A frequent reason to test a name is to avoid creating a duplicate. The following procedure adds a worksheet only when Summary is not already present:

Public Sub AddSummaryIfMissing()
    Dim targetWb As Workbook
    Dim newSheet As Worksheet
    Dim requestedName As String

    ' Work in the workbook that stores this code.
    Set targetWb = ThisWorkbook
    requestedName = "Summary"

    If WorksheetExists(requestedName, targetWb) Then Exit Sub

    ' Add a worksheet, then assign its requested name.
    Set newSheet = targetWb.Worksheets.Add
    newSheet.Name = requestedName
End Sub

This example assumes no chart sheet already uses the name Summary. Excel requires sheet-tab names to be unique across the workbook, so a chart sheet named Summary can prevent the new worksheet from taking that name even though WorksheetExists returns False. If your task is to add any kind of sheet tab without a name conflict, check the broader Sheets collection instead of only Worksheets.

Also, checking and then adding are two separate operations. In ordinary single-user VBA automation, another procedure is unlikely to change the workbook between those lines, but the assignment to newSheet.Name can still fail if the requested name is invalid or conflicts with another tab. Excel worksheet names have restrictions: they cannot exceed 31 characters or contain characters such as a colon, backslash, slash, question mark, asterisk, or square brackets. A check for an existing worksheet does not validate all those naming rules.

Common problems and misunderstandings

Using the wrong collection: Worksheets excludes chart sheets; Sheets includes them. Choose according to whether you mean a worksheet specifically or any sheet tab.
Checking the wrong workbook: A successful lookup in ActiveWorkbook does not prove the sheet exists in ThisWorkbook, or vice versa. Pass the intended workbook explicitly.
Assuming case changes create separate names: Worksheet names are not a reliable way to distinguish names only by capitalization. A case-insensitive check is appropriate for normal worksheet lookup.
Confusing a worksheet name with its VBA code name: The tab name is the visible name users can rename. The code name appears in the Project Explorer and can be referenced directly in VBA, but it is a different property. This function checks the visible tab name.
Suppressing too many errors: Keep On Error Resume Next around only the lookup, then restore normal error handling with On Error GoTo 0. Otherwise, a genuine error later can be silently ignored.
Passing an empty or misspelled name: A name that does not match a worksheet returns False; the function does not try to correct spelling or remove extra spaces. Trim or validate input separately if it comes from a cell or user form.

Excel’s lookup accepts the worksheet name as a string. It does not search cell contents, worksheet code names, or names that merely resemble the requested text. If the desired name is stored in a cell, read that cell into a string variable first, then pass the variable to the function. A leading or trailing space in that value can make it different from the actual tab name.

Save and run VBA safely

Save a copy of the workbook before running a macro that adds, renames, deletes, or otherwise changes sheets. VBA macros cannot be undone with Ctrl+Z, so a backup is the straightforward way to recover the original workbook if a later change has an unwanted result. Save a macro-enabled workbook as .xlsm if it needs to retain VBA code; a standard .xlsx file does not store VBA macros.

On Windows, run a procedure from the Visual Basic Editor with F5 while the cursor is inside it, or use Developer > Macros to select and run a macro. If the Developer tab is not visible, it can be enabled through File > Options > Customize Ribbon. On Mac, use the Visual Basic Editor or the Macros command available in Excel’s ribbon; keyboard shortcuts can vary with the keyboard and macOS settings. Excel for the web can open and edit many macro-enabled workbooks, but it cannot create, run, or edit VBA macros. Use the desktop Excel application for these steps.

Frequently Asked Questions

Does the check distinguish between uppercase and lowercase?
Not in a meaningful way for ordinary worksheet names. The loop example explicitly uses a case-insensitive comparison, and Excel does not permit worksheet tabs whose names differ only by case.

Can I use this function to check whether a chart sheet exists?
No. The supplied function searches wb.Worksheets, which contains worksheets. To search for chart sheets too, use the workbook’s Sheets collection and an object variable for the result.

Why does the function return False when I can see the tab?
The code may be checking another workbook, or the requested string may contain a typo or extra spaces. Confirm the workbook argument and compare the exact visible tab name with the string being passed.

Can I use the worksheet’s code name instead?
This function checks the visible worksheet tab name. A code name is a separate VBA property and is useful for referring to a particular worksheet in project code, but it does not answer whether a specified tab name exists.

Will the macro work in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in the desktop version of Excel for Windows or Mac to use the Visual Basic Editor and run the code.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.