Sheet Exists

I was looking at Siddharth Rout’s new blog (Adding/Deleting Sheets to the Excel File) and realized that I haven’t posted my version of sheet exists. Mine probably does too much per Code Complete standards, but it is nice not to have to think of multiple.

If I were to refactor this code I would take out the code for the charts and probably just make it work only for checking if the sheet exists and adding a new sheet if it doesn’t. I would create a separate function for deleting sheets. But once you have lot’s of code written it is difficult to refactor all your code just to change one function (and I use this function quite a bit).

' --------------------------------------------------------------
'Comments:  This function returns TRUE if the sheet exists in the
'           active workbook and adds new sheet if bAddSheet is true.
'Arguments: sSheetName  Name of sheet.
'           bAddSheet   If true adds new sheet, if false returns false.
'Return:    Returns true when sheet exists.
'Notes:     This was created for use of Jon Nyman and can be distributed by Paciolan.
'           Tristates require reference to "Microsoft Scripting Runtime"
'Date           Developer   History
'01/13/10       Jon Nyman   Initial version
'11/12/10       Jon Nyman   Changed to use ExistInCollection function, changed all to byref for increased speed.
'04/30/12       Jon Nyman   Added reference to worksheet, so no need to add it later!
Public Function SheetExists(ByVal sSheetName As String, Optional ByVal bAddSheet As Boolean = False, _
    Optional ByVal wkb As Workbook = Nothing, Optional ByVal bCreateChart As Boolean = False _
    , Optional ByVal bDeleteSheet As Boolean = False, Optional ByRef wks As Object) As Tristate

    Dim bByRef As Boolean

    'Get current workbook that function will be working on.
    bByRef = True
    If wkb Is Nothing Then
        If Not ActiveWorkbook Is Nothing Then
            Set wkb = ActiveWorkbook
            bByRef = False
            SheetExists = TristateFalse
            GoTo SheetExists_Exit
        End If
    End If
    'Determine if sheet exists
    SheetExists = ExistsInCollection(wkb.Sheets, sSheetName)
    'If sheet exists and delet option is true then delete worksheet without
    'displaying an alert to the user.
    If SheetExists And bDeleteSheet Then
        Application.DisplayAlerts = False
        With wkb.Sheets(sSheetName)
            .Visible = True
        End With
        Application.DisplayAlerts = True
        SheetExists = TristateFalse
    End If
    'If the sheet doesn't exist and the add sheet (or chart) option is checked then
    'add a new sheet with name given.
    If Not SheetExists And bAddSheet Then
        If Not bCreateChart Then
            Set wks = wkb.Sheets.Add
            wks.Name = sSheetName
            Set wks = wkb.Charts.Add
            wks.Name = sSheetName
        End If
        SheetExists = TristateMixed
    ElseIf SheetExists Then
        Set wks = wkb.Sheets(sSheetName)
    End If

    If Not bByRef Then Set wkb = Nothing

End Function
' --------------------------------------------------------------
'Comments:  This procedure deletes names in a workbook.
'Arguments: colObject   Collection object.
'           sItem       Item that could be in collection.
'Returns:   True when item is in the collection.
'Source: Excel 2010: Power Programming with VBA - John Walkenbach - pg. 367
'Notes: This was created by Jon Nyman and can be distributed by Paciolan.
'Date           Developer   History
'11/12/2010     Jon Nyman   Initial Version
Public Function ExistsInCollection(ByRef colObject As Object, ByRef sItem As String) As Boolean

    Dim oObj As Object

    On Error Resume Next
    Set oObj = colObject(sItem)
    ExistsInCollection = Not oObj Is Nothing

    Set oObj = Nothing

End Function

Thu Sep 13, 2012 | tags: vba excel code

comments powered by Disqus