Travis Hydzik
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
accepted my complete code as the solution. points awarded to zorvek for the expert help.
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.VBCompo
Select Case VBComponent.Type
Case vbext_ct_ActiveXDesigner
IsVBACodeInWorkbook = True
Case vbext_ct_ClassModule
IsVBACodeInWorkbook = True
Case vbext_ct_Document
If VBComponent.CodeModule.Cou
If VBComponent.CodeModule.Cou
If VBComponent.CodeModule.Lin
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