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