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

I've used the following to count the total number of lines in a project before. It will pick up code in ThisWorkbook, code modules, class modules and forms.

Private Sub countCodeLines()
    Dim obj As Object
    Dim VBALineCount As Long
    For Each obj In ThisWorkbook.VBProject.VBComponents
        VBALineCount = VBALineCount + obj.CodeModule.CountOfLines
    Next obj
    Debug.Print VBALineCount
End Sub

Note however that if your workbooks have Option Explicit forced then this will count as two lines per object (Option Explicit and a line feed). If you know this to be the case, and are checking the LOC from another project, then you could simply count the number of objects, double it and test that VBALineCount does not exceed this number.

Answer 2

After Lunatik's hint, here's my final function (for whom it may help):

Function fTest4Code(wkb As Workbook) As Boolean
    'returns true if wkb contains VBA code, false otherwise
    Dim obj As Object
    Dim iCount As Integer
    For Each obj In wkb.VBProject.VBComponents
        With obj.CodeModule
            '# lines - # declaration lines > 2 means we do have code
            iCount = iCount + ((.CountOfLines - .CountOfDeclarationLines) > 2)
        End With
        If iCount  0 Then Exit For    'stop when 1st found
    Next obj
    fTest4Code = CBool(iCount)
End Function

Answer 3

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