Colo's Excel Junk Room -vba tips

Checking if the worksheet exists

Here are example functions returns True if the target worksheet exists.

The first one is using a Loop and the second one is using an Error trap.

Example

Place the following in a standard module.

Option Explicit

Sub TestingFunction()
    'DoesWksExist Function
    Debug.Print DoesWksExist("Sheet1")
    Debug.Print DoesWksExist("Sheet100")

    Debug.Print "-----"

    'DoesWksExist2 Function
    Debug.Print DoesWksExist2("Sheet1")
    Debug.Print DoesWksExist2("Sheet100")
End Sub

Function DoesWksExist(sWksName As String) As Boolean
'Using a loop
'returns True if the target wks exists
'returns False if the target wks does NOT exists
    Dim i As Long

    For i = Worksheets.Count To 1 Step -1
        If Sheets(i).Name = sWksName Then
            Exit For
        End If
    Next

    If i = 0 Then    'if the wks did not found
        DoesWksExist = False
    Else
        DoesWksExist = True
    End If
End Function

Function DoesWksExist2(sWksName As String) As Boolean
'Using an error trap
'returns True if the target wks exists
'returns False if the target wks does NOT exists
    Dim wkb As Worksheet

    On Error Resume Next
    Set wkb = Sheets(sWksName)
    On Error GoTo 0

    DoesWksExist2 = IIf(Not wkb Is Nothing, True, False)
End Function