Does a worksheet or workbook have code?
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