如何在VBA中测试Excel工作簿中VBA的存在?

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。如果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的访问frm的方法。HasModule——看看工作簿中是否有VBA代码?

3 个解决方案

#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.

但是请注意，如果您的工作簿有显式强制选项，那么每个对象将被算作两行(选项显式和行提要)。如果您知道这是事实，并且正在检查来自另一个项目的LOC，那么您可以简单地计算对象的数量，将其加倍，并测试VBALineCount不超过这个数字。

#2

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和更早的版本，在工作簿的任何vbcomponent的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

荷兰

#3

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

#1