If WorkSheet wsName Exists

Question

I m wondering if there is clean cut functionality that returns True or False if a worksheet inside a workbook exists    It would be good  but not essential  if it s possible to do it without skipping error handling    The only thing I ve found doesn t really work   On Error Resume Next If  Worksheets  wsName   Name  lt  gt      Then     Debug Print  Worksheet exists   Else     Debug Print  Worksheet doesn t exist   End If On Error GoTo ErrHandler

User · Answer

also a slightly different version  i just did a appllication sheets count to know how many worksheets i have additionallyl  well and put a little rename in aswell  Sub insertworksheet       Dim worksh As Integer     Dim worksheetexists As Boolean     worksh   Application Sheets Count     worksheetexists   False     For x   1 To worksh         If Worksheets x  Name    ENTERWROKSHEETNAME  Then             worksheetexists   True              Debug Print worksheetexists             Exit For         End If     Next x     If worksheetexists   False Then         Debug Print  transformed exists          Worksheets Add after  Worksheets Worksheets Count          ActiveSheet Name    ENTERNAMEUWANTTHENEWONE      End If End Sub

User · Answer

There s no built-in function for this     Function SheetExists SheetName As String  Optional wb As Excel Workbook     Dim s As Excel Worksheet    If wb Is Nothing Then Set wb   ThisWorkbook    On Error Resume Next    Set s   wb Sheets SheetName     On Error GoTo 0    SheetExists   Not s Is Nothing End Function

User · Answer

Another version of the function without error handling  This time it is not case sensitive and a little bit more efficient   Function WorksheetExists wsName As String  As Boolean     Dim ws As Worksheet     Dim ret As Boolean             wsName   UCase wsName      For Each ws In ThisWorkbook Sheets         If UCase ws Name    wsName Then             ret   True             Exit For         End If     Next     WorksheetExists   ret End Function

User · Answer

Slightly changed to David Murdoch s code for generic library  Function HasByName cSheetName As String                        Optional oWorkBook As Excel Workbook  As Boolean      HasByName   False     Dim wb      If oWorkBook Is Nothing Then         Set oWorkBook   ThisWorkbook     End If      For Each wb In oWorkBook Worksheets         If wb Name   cSheetName Then             HasByName   True             Exit Function         End If     Next wb End Function

User · Answer

A version without error-handling   Function sheetExists sheetToFind As String  As Boolean     sheetExists   False     For Each sheet In Worksheets         If sheetToFind   sheet name Then             sheetExists   True             Exit Function         End If     Next sheet End Function

[excel] If WorkSheet("wsName") Exists

Examples related to excel

Examples related to vba