Check if a sheet exists

Tested in Excel 365 (16.8730.2046) 64-bit

 
 
  1. Public Function SheetExists(SheetToFind As String) As Boolean
  2.     On Error Resume Next
  3.     Sheets(SheetToFind).Activate
  5.     If Err.Number = 0 Then
  6.         SheetExists = True
  7.     Else
  8.         Err.Number = 0
  9.     End If
  11.     Sheets(sheet).Activate
  12.     On Error GoTo 0
  13. End Function

Or checking each sheet name. I personally prefer this method because I like to avoid as much as possible the number of On Error Resume Next and On Error GoTo 0. Also, using Like allows to search using an approximate name.

 
 
  1. Public Function SheetExists(SheetName) As Boolean
  2.     Dim i, test
  3.    
  4.     test = False
  5.     For i = 1 To Sheets.Count
  6.         If UCase(Sheets(i).Name) Like UCase(SheetName) Then
  7.             test = True
  8.             Exit For
  9.         End If
  10.     Next i
  11.     SheetExists = test
  12. End Function

Or return the exact name using Like to search an approximate name.

 
 
  1. Public Function GetSheetExactName(SheetName) As String
  2.     Dim i, test
  3.    
  4.     test = ""
  5.     For i = 1 To Sheets.Count
  6.         If UCase(Sheets(i).Name) Like UCase(SheetName) Then
  7.             test = Sheets(i).Name
  8.             Exit For
  9.         End If
  10.     Next i
  11.     GetSheetExactName = test
  12. End Function

Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.