How to test for existence of VBA in Excel workbook, in VBA?

Question

I am writing a reporting tool to document Excel files for various "compliance criteria", including wkb.VBProject.Protection to report if the VBA is locked.

But how can I find if the workbook HAS any project ?

If I calculate

wkb.VBProject.VBComponents.Count - wkb.Worksheets.Count - 1 '(for the workbook)

that will give me the number of modules + class modules + forms, but I could still have some code behind a sheet.

Is there a way in Excel - like Access frm.HasModule - to find out if there's any VBA code in the workbook ?

Answer 1

Excel 2007+ has a new workbook property called ".HasVBProject" that you can enquire.

For Excel 2003 and earlier the above solution testing for lines of code in the CodeModule of any of the VBComponents of the workbook is appropriate.

You should test the ".CountOfLines" property all alone, since lines of code in the Declaration section of a code module (obtained via ".CountOfDeclarationLines") are considered by Excel as "Macro code" and require saving to macro-enabled formats.

Public Function HasVBProject(Optional pWorkbook As Workbook) As Boolean
'
' Checks if the workbook contains a VBProject.
'
On Error Resume Next
    Dim wWorkbook    As Workbook
    Dim wVBComponent As VBIDE.VBComponent ' As Object if used with Late Binding

    ' Default.
    '
    HasVBProject = False

    ' Use a specific workbook if specified, otherwise use current.
    '
    If pWorkbook Is Nothing _
    Then Set wWorkbook = ActiveWorkbook _
    Else Set wWorkbook = pWorkbook
    If wWorkbook Is Nothing Then GoTo EndFunction

    If (VBA.CInt(Application.Version) >= 12) _
    Then
        ' The next method only works for Excel 2007+
        '
        HasVBProject = wWorkbook.HasVBProject
    Else
       ' Signs the workbook has a VBProject is code in any of the VBComponents that make up this workbook.
        '
        For Each wVBComponent In wWorkbook.VBProject.VBComponents
            If (wVBComponent.CodeModule.CountOfLines > 0) _
            Then
                ' Found a sign of programmer's activity. Mark and quit.
                '
                HasVBProject = True: Exit For
            End If
        Next wVBComponent
    End If

EndFunction:
    Set wVBComponent = Nothing
    Set wWorkbook = Nothing
End Function

Dutch