Determining If A Worksheet Or Workbook Has Code

Category: VBA Functions | [Item URL]

Every workbook and sheet has a corresponding code module. These code modules can contain VBA code to handle workbook or sheet-level events. For example, a workbook code module (named ThisWorkbook by default) might have a subroutine declared as follows:

Private Sub Workbook_Open()
' Code goes here
End Sub

The Workbook_Open sub is executed whenever the workbook is opened.

Similarly, code modules for worksheets can contain subroutines to handle worksheet event such as Activate, Deactivate, Change, etc.

Listed below are two custom VBA functions that you can use to determine if the code module for a particular workbook or worksheet contains any code.

The WorkbookHasVBACode Function

The function below takes a single argument: a workbook object. It returns True if the workbook's code module contains any VBA code.

Private Function WorkbookHasVBACode(wb As Workbook)
    ModuleLineCount = wb.VBProject.VBComponents(wb.CodeName). _
      CodeModule.CountOfLines
    If ModuleLineCount = 0 Then
        WorkbookHasVBACode = False
    Else
        WorkbookHasVBACode = True
    End If
End Function

The SheetHasVBACode Function

The function below takes a single argument: a worksheet object. It returns True if the worksheet's code module contains any VBA code.

Private Function SheetHasVBACode(wks As Worksheet)
    ModuleLineCount = wks.Parent.VBProject. _
      VBComponents(wks.CodeName).CodeModule.CountOfLines
    If ModuleLineCount = 0 Then
        SheetHasVBACode = False
    Else
        SheetHasVBACode = True
    End If
End Function

An Example

The example below demonstrates a practical use of the SheetHasVBACode function. The DeleteBlankSheets subroutine deletes all blank sheets in the active workbook -- but only if the sheet does not contain any VBA code.

Sub DeleteBlankSheets()
    Dim sht As Worksheet
    On Error GoTo ErrHandler
'   Avoid Excel's confirmation prompt
    Application.DisplayAlerts = False
'   Loop through each sheet
    For Each sht In ActiveWorkbook.Worksheets
'       Is non-blank cell count zero?
        If Application.CountA(sht.Cells) = 0 Then
'           Don't try to delete the last sheet
            If ActiveWorkbook.Sheets.Count <> 1 Then
'               Don't delete sheet if it has VBA code
                If Not SheetHasVBACode(sht) Then
                    sht.Delete
                End If
            End If
        End If
    Next sht
    Exit Sub
ErrHandler:
    MsgBox sht.Name & Chr(13) & Chr(13) & Error(Err)
End Sub


Search for Tips


All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

© Copyright 2016, J-Walk & Associates, Inc.
Privacy Policy