VBA Code to Check if Sheet with Name exists?
This Excel vba code will check if sheet with a specific name exists in the workbook.
As per Microsoft specifications, You can add as many number of sheets depending on the limits of Your computer’s memory.
So, this loop will check if any of those sheets has the name passed as parameter to this function.
- There are 2 methods presented in this page.
- Use option 1 first & Test the performance.
- If the loop is taking more time, then use 2nd option.
VBA function to Check if Sheet Exists
Here is the vba code the loop thru all the sheets & verifies the name passed as parameter.
Public Function SheetExists(SheetName As String) As Boolean 'Declare variables - Officetricks.com Dim wSh As Worksheet Dim bReturnValue As Boolean 'Loop Thru Each Sheet in workbook bReturnValue = False For Each wSh In ThisWorkbook.Sheets 'Check whether there is a name match If VBA.UCase(wSh.Name) = VBA.UCase(SheetName) Then bReturnValue = True Exit For End If Next wSh 'Return Match Result SheetExists = bReturnValue End Function
The above function will return ‘True’ if the sheet with exact name exists in the workbook. Otherwise it will return false.
Lets see another version that is more faster than the above one.
VBA Worksheet Exists with specific Name in Workbook
This function does not loop thru all existing sheet. It directly checks for the Sheet name.
If you face any issue in using this function, then use the first one. Option 1 gives good results consistently.
Public Function fSheetExists(SheetName As String) As Boolean 'Declare variables - Officetricks.com Dim wSh As Worksheet Dim bReturnValue As Boolean Set wSh = Nothing 'Assign Sheet to Object On Error Resume Next Set wSh = ThisWorkbook.Sheets(SheetName) On Error GoTo 0 'Check if Sheet with name exists If wSh Is Nothing Then bReturnValue = False Else bReturnValue = True End If 'Return Match Result fSheetExists = bReturnValue End Function
Using this function You can confirm is a sheet exists of not.
Then by using the function in here, you can create or add new worksheet to workbook & rename it. Click here to get the code to add new sheet & rename it.