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 StringAs 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 StringAs 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 NothingTrueFalse)
End Function

| HOME |
Copyright © cellmasters.net - colo's junk room All Right Reserved
ABOUT
WORKS
THE CELL MASTERS
CONTACT
LINKS
Tips and Information about Microsoft Excel|Masaru Kaji aka Colo