Visual Basic for Applications/Check if a Workbook has VBA code

Summary

This VBA code module is made for Excel, but is easily adapted for other MS Office applications. It checks a workbook to see if it contains any useful VBA dimensional or structural code. Line counting has been found to be less reliable than this since even empty modules will show two lines of code each.

The Code Module

• Place all of the code below into the same standard module, and identify the test workbook address in wb.
• Then, run the procedure CheckForVBA to check whether or not the test workbook contains identifiable VBA code structures.
• The procedures first check to see that the workbook is not locked.
• Users can modify the test keyword list in procedure ContainsVBAKeyWords.
• The test workbook is closed again after inspection.
• Results are shown in message boxes here, but the top section is easily modified for other uses.

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