VBA UDF (User Defined Function) to determine if a sheet or range exists in currently active workbook, e.g.

If IsWorksheet("SheetName") Then
     Msgbox "Sheet exists"
Else
     Msgbox "Sheet does NOT exist."
End If

VBA Code

Public Function IsWorksheet(ByVal WorksheetName As String) As Boolean

    On Error Resume Next
    
    'Return true if a worksheet using a sheet name string exists
    IsWorksheet = (Sheets(WorksheetName).Name <> "")
    
End Function


Public Function IsRange(CheckRange As Range) As Boolean

    On Error Resume Next
    
    Set rng = Workbook.Range(CheckRange)
    
    'Return true if range object is valid
    If Not rng Is Nothing Then IsRange = True
    
End Function


Public Function IsRangeAddress(RangeAddress As String) As Boolean

    Dim rng As Range
    
    On Error Resume Next
    
    Set rng = Worksheets(1).Range(RangeAddress)
    
    'Return true if string is a valid range address
    If Not rng Is Nothing Then IsRangeAddress = True
    
End Function