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