There may come a time when you need to know if a sheet in a workbook exists either during VBA code execution or as a result within the workbook. You may be creating and deleting sheets with your VBA code and will need to test if a sheet exists before it’s created/deleted to avoid run-time errors. You may also have functions in your workbook that depend on sheets you’re creating or deleting and you need to check if they exist.
A User Defined Function To Check If A Sheet Exists Within The Current Workbook
This is a simple VBA function that will return true if the current workbook contains a sheet with the exact name passed through the function and returns false otherwise. This function is not case sensitive so Sheet1 and SHEET1 are considered to be the same (sheet names in Excel are not case sensitive). Here the VBA is formatted as a user defined function.
Function WorksheetExists(SheetName As String) As Boolean Dim TempSheetName As String TempSheetName = UCase(SheetName) WorksheetExists = False For Each Sheet In Worksheets If TempSheetName = UCase(Sheet.Name) Then WorksheetExists = True Exit Function End If Next Sheet End Function
With this code we can use
=WorksheetExists(B3) to test any text string to see if it exists as a sheet name in the current workbook.
This didn’t work for me but I got it to run by adding;
1. Dim ws = worksheet
2. Change all references from ‘Sheet’ to ws