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.
For example, if we checked if the sheet name “teams” exists then the function would also return TRUE:
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:
VBA: How to Count Number of Sheets in Workbook
VBA: How to Extract Data from Another Workbook
VBA: How to Insert Multiple Rows
