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