Table of Contents
To check if a sheet exists in VBA, one can use the “Worksheets” collection object and the “Exists” method. This method allows the user to specify the name of the sheet they want to check and returns a Boolean value indicating whether the sheet exists or not. By utilizing this method, the user can efficiently determine the existence of a sheet in their VBA program.
VBA: Check if Sheet Exists (With Example)
You can create the following function in VBA to check if a particular sheet exists in the currently active Excel workbook:
Function sheetExists(some_sheet As String) As Boolean
On Error Resume Next
sheetExists = (ActiveWorkbook.Sheets(some_sheet).Index > 0)
End Function
This function will return either TRUE or FALSE to indicate whether or not a particular sheet name exists in the currently active Excel workbook.
Note that this function simply checks if the index number of a sheet is greater than 0.
If the sheet exists, the index number of the sheet will have a value of 1 at the minimum, which will cause the function to return a value of TRUE.
The following example shows how to use this function in practice.
Example: Use VBA to Check if Sheet Exists
Suppose we have the following Excel workbook with three sheets:
We can create the following function in VBA to check if a particular sheet name exists in this workbook:
Function sheetExists(some_sheet As String) As Boolean
On Error Resume Next
sheetExists = (ActiveWorkbook.Sheets(some_sheet).Index > 0)
End Function
We can then type the following formula into cell A1 of the currently active sheet to check if the sheet name “Teams” exists in this workbook:
=sheetExists("Teams")
The following screenshot shows how to use this formula in practice:
The function returns TRUE since this sheet name does exist in the workbook.
Also note that this function is not case-sensitive.
However, suppose we check if the sheet name “coaches” exists:
The function returns FALSE since this sheet name does not exist in the workbook.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA: