Sheet Exists Function for Excel VBA

A function macro to help another macro

Often when you are working with Excel VBA you need to confirm if a sheet exists based on the tab name. This Function can be used to do that. It works with the active file (workbook).

The function below is meant to be used within another macro, an example of that follows.

Private Function SheetExists(sName As String) As Boolean
 
'returns TRUE if the sheet exists
 
Dim x As Worksheet
 
On Error Resume Next
 
Set x = ActiveWorkbook.Sheets(sName)
 
SheetExists = (Err.Number = 0)
 
Set x = Nothing
 
End Function

 

To use this function in a macro, the code would look something like. This is looking for a sheet tab named test.

Sub TestSheet()
 
If SheetExists("test") Then
 
   MsgBox "sheet exists"
 
   'do stuff
 
Else
 
   MsgBox "sheet doesn't exist"
 
   'tell user to fix something
 
End If
 
End Sub

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

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.