Link to home
Start Free TrialLog in
Avatar of Travis Hydzik
Travis HydzikFlag for Australia

asked on

Determine all Excel WorkBooks with VBA code

Experts,

I would like to find all Excel WorkBooks with any VBA code in them.
I have the following from
http://www.ozgrid.com/forum/showthread.php?t=58216&p=300667#post300667
But this only finds modules, how would I also determine if a sheet has VBA code?

Thanks
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I created my own version:

Public Function IsVBACodeInWorkbook( _
        ByVal Workbook As Workbook _
    ) As Boolean

' Return True if any VBA code exists in the workbook.

    Dim VBComponent As VBComponent
   
    For Each VBComponent In Workbook.VBProject.VBComponents
        Select Case VBComponent.Type
            Case vbext_ct_ActiveXDesigner
                IsVBACodeInWorkbook = True
            Case vbext_ct_ClassModule
                IsVBACodeInWorkbook = True
            Case vbext_ct_Document
                If VBComponent.CodeModule.CountOfLines > 0 Then
                    If VBComponent.CodeModule.CountOfLines = 2 Then
                        If VBComponent.CodeModule.Lines(1, 2) <> "Option Explicit" & vbCrLf Then
                            IsVBACodeInWorkbook = True
                        End If
                    Else
                        IsVBACodeInWorkbook = True
                    End If
                End If
            Case vbext_ct_MSForm
                IsVBACodeInWorkbook = True
            Case vbext_ct_StdModule
                IsVBACodeInWorkbook = True
            Case Else
        End Select
        If IsVBACodeInWorkbook Then Exit Function
    Next VBComponent
   
End Function

From a security perspective, Excel considers a workbook contains macros (VBA code) if any regular, form, or class module exists (even if it doesn't contain any code) or the ThisWorkbook or any sheet module contains any code beyond the default "Option Explicit" declaration line.

Kevin
Avatar of Travis Hydzik

ASKER

accepted my complete code as the solution. points awarded to zorvek for the expert help.