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
   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.

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