代码之家 › 专栏 › 技术社区 › iDevlop

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

excel-vba vba excel

iDevlop · 技术社区 · 15 年前

我正在编写一个报告工具,用于记录各种“合规性标准”的Excel文件,包括wkb.VBProject.Protection,以便在VBA被锁定时报告。

但是我如何才能找到工作簿中是否有任何项目?

如果我计算

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

这将给我模块+类模块+表单的数量,但我仍然可以在一张工作表后面有一些代码。

在Excel中是否有方法(如Access frm.HasModule)查找工作簿中是否有VBA代码?

3 回复 | 直到 15 年前

Dutch 15 年前

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

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

Lunatik 15 年前

我以前使用过以下方法来计算项目中的行总数。它将从中提取代码 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

但是请注意,如果您的工作簿 Option Explicit 强制,则每个对象将计为两行( 选项显式 和一个线路馈线)。如果您知道情况是这样的,并且正在检查另一个项目中的LOC,那么您可以简单地计算对象的数量,将其加倍并进行测试 VBALineCount 不超过这个数字。

iDevlop 15 年前

在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