PDA

View Full Version : VBa Code exist?



jov_damo86
05-15-2010, 07:35 AM
HEllo...:hi: help please!: pray2:

Can you please give some vba/macro codes on how to determine if an excel files contain a macro or vba code?

Thank you very much...:ipray:

mikerickson
05-15-2010, 08:29 AM
Perhaps this
Sub test()
Dim myComp As Variant, isMacro As Boolean

With Workbooks("Workbook2")
For Each myComp In .VBProject.VBComponents
isMacro = isMacro Or myComp.CodeModule.Find("End", 1, 1, 1, 1)
Next myComp
End With

If isMacro Then
MsgBox "There is code in this book"
Else
MsgBox "no code"
End If
End Sub

mdmackillop
05-15-2010, 11:25 AM
Expanding on Mike's code

Sub test()
Dim myComp As Variant, isMacro As Boolean
Dim wb As Variant
msg1 = "There is code in: " & vbCr
msg2 = "There is no code in: " & vbCr

For Each wb In Workbooks
isMacro = False
With wb
If UCase(Left(wb.Name, 8)) <> "PERSONAL" Then
For Each myComp In wb.VBProject.VBComponents
isMacro = isMacro Or myComp.CodeModule.Find("End", 1, 1, 1, 1)
Next myComp

If isMacro Then
msg1 = msg1 & wb.Name & vbCr
Else
msg2 = msg2 & wb.Name & vbCr
End If
End If
End With
Next
MsgBox msg1 & vbCr & msg2
End Sub