How to check whether certain sheets exist or not in Excel-VBA? [duplicate]

by

Although (unfortunately) such method is not available, we can create our own function to check this..

Hope the code below fits your needs.

Edit1: Added also delete statement…

Sub test()

    If CheckSheet(Sheets(3).Name) then

        Application.DisplayAlerts = False
        Sheets(Sheets(3).Name).Delete
        Application.DisplayAlerts = True

    End If

End Sub

The solution I’d go for…

Function CheckSheet(ByVal sSheetName As String) As Boolean

    Dim oSheet As Excel.Worksheet
    Dim bReturn As Boolean

    For Each oSheet In ActiveWorkbook.Sheets

        If oSheet.Name = sSheetName Then

            bReturn = True
            Exit For

        End If

    Next oSheet

    CheckSheet = bReturn

End Function

Alternatively, if you don’t mind to use code that actively raise errors (which is not recommended by common coding best practices) you could use this ‘Spartan Programming wannabe’ code below…

Function CheckSheet(ByVal sSheetName As String) As Boolean

    Dim oSheet As Excel.Worksheet
    Dim bReturn As Boolean

    For Each oSheet In ActiveWorkbook.Sheets

        If oSheet.Name = sSheetName Then

            bReturn = True
            Exit For

        End If

    Next oSheet

    CheckSheet = bReturn

End Function


Function CheckSheet(ByVal sSheetName As String) As Boolean

    On Error Resume Next
    Dim oSheet As Excel.Worksheet

    Set oSheet = ActiveWorkbook.Sheets(sSheetName)
    CheckSheet = IIf(oSheet Is Nothing, False, True)

End Function

More Related Contents:

  1. How to add a custom Ribbon tab using VBA?
  2. filter out multiple criteria using excel vba
  3. Closing Excel Application using VBA
  4. How to add parameters to an external data query in Excel which can’t be displayed graphically?
  5. Cannot VBA write data to cells in Excel 2007/2010 within a function
  6. VBA equivalent to Excel’s mod function
  7. Excel VBA to match and line up rows
  8. Excel: the Incredible Shrinking and Expanding Controls [closed]
  9. Excel Date Conversion from yyyymmdd to mm/dd/yyyy
  10. How to programmatically code an ‘undo’ function in Excel-Vba? [duplicate]
  11. Delete all data rows from an Excel table (apart from the first)
  12. How to do a “Save As” in vba code, saving my current Excel workbook with datestamp?
  13. MS Excel: insert a row automatically if the content in a cell is <> that in cell of the last row?
  14. Round function in Excel, worksheet function vs VBA
  15. Uncheck all checkboxes across entire workbook via CommandButton
  16. VBA workbooks.Close without being prompted to if the user wants to save?
  17. Copy a selected range to another worksheet
  18. Excel macro – paste only non empty cells from one sheet to another
  19. The MacScript function is not working well in Office for Mac 2016! Any ideas?
  20. Create hyperlink to another sheet
  21. Convert Early Binding VBA to Late Binding VBA : Excel to Outlook Contacts
  22. Delay macro to allow events to finish
  23. Export multiple sheets to PDF simultaneously without using ActiveSheet or Select
  24. declaring a unicode string in vba in excel [duplicate]
  25. Application.WorksheetFunction.Match method
  26. Excel – Using COUNTIF/COUNTIFS across multiple sheets/same column
  27. Excel Hide/Show all tabs on Ribbon except custom tab
  28. Macros Not Showing Up in Macro Table
  29. Excel telling me my blank cells aren’t blank
  30. Excel VBA Run-time Error ‘32809’ – Trying to Understand it

Leave a Comment