How to check for VBA in an Excel workbook in VBA?

Question

I am writing a reporting tool to document Excel files for various "matching criteria", including wkb.VBProject.Protection, to let you know if VBA is blocked.

But how can I find if there is any project in the book?

If I figured

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

which will give me the number of modules + classes + forms, but I can still have the code behind the sheet.

Is there a way in Excel - for example, Access frm.HasModule - to find out if there is any VBA code in the book?

Answer 1

I used the following to calculate the total number of lines in a project before. It will pick up code in ThisWorkbook , code modules, modules, and class 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 

Please note that if your books have Option Explicit forced, then this will be considered two lines per object ( Option Explicit and line feed). If you know that this is the case, and check the LOC from another project, you can simply count the number of objects, double it and check that VBALineCount does not exceed this number.

Answer 2

Excel 2007+ has a new workbook property called ".HasVBProject" that you can request.

In Excel 2003 and earlier, the testing of solutions for lines of code in the CodeModule of any of the VBComponents workbooks is discussed above.

You must test the ".CountOfLines" property yourself, because the lines of code in the "Declaration" section of the code module (obtained through the ".CountOfDeclarationLines") are considered by Excel as "Macrocode" and require the preservation of macros allowed formats.

 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 activity. Mark and quit. ' HasVBProject = True: Exit For End If Next wVBComponent End If EndFunction: Set wVBComponent = Nothing Set wWorkbook = Nothing End Function 

Dutch

Answer 3

After the Lunatic hints, here is my last function (for whom it might help):

  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