如何在 VBA 中测试 Excel 工作簿中是否存在 VBA？

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

提问by Patrick Honorez

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.

我正在编写一个报告工具来记录各种“合规标准”的 Excel 文件，包括 wkb.VBProject.Protection 来报告 VBA 是否被锁定。

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 ?

Excel 中是否有办法（例如 Access frm.HasModule）来确定工作簿中是否有任何 VBA 代码？

回答by Dutch

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

Excel 2007+ 有一个名为“.HasVBProject”的新工作簿属性，您可以查询它。

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.

对于 Excel 2003 及更早版本，上述解决方案测试工作簿的任何 VBComponents 的 CodeModule 中的代码行是合适的。

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.

您应该单独测试“.CountOfLines”属性，因为代码模块的声明部分中的代码行（通过“.CountOfDeclarationLines”获得）被 Excel 视为“宏代码”并且需要保存为启用宏的格式。

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

荷兰语

回答by Lunatik

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.

我以前使用以下内容来计算项目中的总行数。它将选取ThisWorkbook、代码模块、类模块和表单中的代码。

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 Explicitforced then this will count as two lines per object (Option Explicitand 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 VBALineCountdoes not exceed this number.

但是请注意，如果您的工作簿已Option Explicit强制执行，那么这将计为每个对象两行（Option Explicit和一个换行符）。如果您知道是这种情况，并且正在检查另一个项目的 LOC，那么您可以简单地计算对象的数量，将其加倍并测试VBALineCount不超过此数量的对象。

回答by Patrick Honorez

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

在 Lunatik 的提示之后，这是我的最后一个函数（它可能对谁有帮助）：

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