Visual Basic for Applications/檢查工作簿是否包含 VBA 程式碼

摘要

這個 VBA 程式碼模組是為 Excel 製作的，但很容易適應其他 MS Office 應用程式。它檢查工作簿以檢視它是否包含任何有用的 VBA *維* 或 *結構* 程式碼。行計數已被發現不如此可靠，因為即使是空的模組也會顯示兩行程式碼。

程式碼模組

• 將以下所有程式碼放入同一個標準模組中，並在 *wb* 中標識測試工作簿地址。
• 然後，執行過程 *CheckForVBA* 以檢查測試工作簿是否包含可識別的 VBA 程式碼結構。
• 這些過程首先檢查工作簿是否未鎖定。
• 使用者可以在過程 *ContainsVBAKeyWords* 中修改測試關鍵字列表。
• 檢查後，測試工作簿將再次關閉。
• 結果將在此處顯示在訊息框中，但頂部部分很容易修改以用於其他用途。

Option Explicit

Sub CheckForVBA()
    'Run this procedure to know whether a specified workbook has VBA code
    'Assumes that workbook to test is in same folder and called Book2.xlsm
    'Set reference to Microsoft VBA Extensibility 5.5
    
    Dim wb As Workbook, nL As Long, bR As Boolean
    
    'set full address of workbook to test here
    'if just file name then same folder is assumed
    Set wb = Workbooks.Open("Book2.xlsm")
    
    'check for code if project is not locked
    If IsProtectedVBProject(wb) = False Then
       'check for vba code
       If WbkHasVBA(wb) = True Then
          MsgBox "Workbook " & wb.FullName & vbCrLf & _
          "CONTAINS VBA code structure."
       Else
          MsgBox "Workbook " & wb.FullName & vbCrLf & _
          "DOES NOT contain VBA code structure."
       End If
    Else
       MsgBox "The VBA Project is LOCKED;" & vbCrLf & _
              "might have VBA but unable to confirm."
    End If

    'close the test workbook
    wb.Close

End Sub

Function IsProtectedVBProject(ByVal wb As Workbook) As Boolean
    'returns TRUE if VBA is password protected, else false
        
    Dim nComp As Integer
    
    nComp = -1
    
    On Error Resume Next
       nComp = wb.VBProject.VBComponents.Count
    On Error GoTo 0
    
    If nComp = -1 Then
       IsProtectedVBProject = True
    Else
       IsProtectedVBProject = False
    End If

End Function

Private Function WbkHasVBA(ByVal wb As Workbook) As Boolean
    'returns true if workbook contains VBA, else false.
    'Code must not be locked.
    'Set reference to Microsoft VBA Extensibility 5.5
    
    Dim VBComp As VBIDE.VBComponent
    Dim VBMod As VBIDE.CodeModule
    Dim nLines As Long, sMod As String
         
    'get each module one at a time
    For Each VBComp In wb.VBProject.VBComponents
        Set VBMod = VBComp.CodeModule
        nLines = VBMod.CountOfLines
            If nLines <> 0 Then
                sMod = VBMod.Lines(1, nLines)
                'check for significant code entries
                If ContainsVBAKeyWords(sMod) Then
                   WbkHasVBA = True
                   Exit For
                End If
            End If
    Next VBComp
    
    Set VBComp = Nothing
    Set VBMod = Nothing

End Function

Function ContainsVBAKeyWords(ByVal sModule As String) As Boolean
   'Returns true if input string contains any listed word,
   'else false. User should add keywords of interest to vKeyList

   Dim vKeyList As Variant, nC As Integer, bM As Boolean
   
   'set the key list of interest here
   vKeyList = Array("End", "Dim", "Public", "Private", "Friend", "Property", _
                 "Type", "Declare", "Sub", "Function")

   'loop through keylist and compare with parameter module string
   For nC = LBound(vKeyList) To UBound(vKeyList)
      bM = sModule Like "*" & vKeyList(nC) & "*"
      If bM = True Then
         ContainsVBAKeyWords = True
         Exit For
      End If
   Next nC

End Function