Saturday, November 7, 2015

Excel VBA: How to check whether a sheet exist or not

Sometimes we need to check whether a sheet exist or not to avoid run time error. Below is a simple function to do that task:

Function isSheetExist(ByVal workbookName As String, ByVal sheetName As String) As Boolean
Dim ws As Worksheet, ss
Dim vbR As Boolean
    vbR = False
    Set ss = Workbooks(workbookName).Worksheets
    For Each ws In ss
        If (ws.Name = sheetName) Then
            vbR = True
            Exit For
        End If
    Next ws
    isSheetExist = vbR
End Function

And this is an example to use that function: 

Sub FunctionTest()
 If isSheetExist(ThisWorkbook.Name, "Sheet1") = False Then
     MsgBox "Sheet is not exist."
     MsgBox "Sheet is exist."
 End If
End Sub
I hope this example help you.

No comments:

Post a Comment