detect if a macro or code module exist

[rico]

From Word or Access I need to find out if a workbook has any macros or code/class modules. Is there away to do this in VBA?

Thank you

 

[Juan Pablo González]

Hi, welcome to the board. You can use this function, passing the workbook name to it.<pre>Function WBHasMacros(WBName As String) As Variant
Dim VBProj 'As VBProject
Dim VBComp 'As VBComponent

Set VBProj = Application.Workbooks(WBName).VBProject
If VBProj.Protection = vbext_pp_locked Then WBHasMacros = _
CVErr(xlErrValue): Exit Function
For Each VBComp In VBProj.VBComponents
If VBComp.CodeModule.CountOfLines > 2 Then
WBHasMacros = True
Exit Function
End If
Next VBComp
End Function</pre>Example:

?WBHasMacros("MyFormulas.xla")
True

If the workbook is locked, the function will return an error (#VALUE)

_________________
Regards,

Juan Pablo G.
MrExcel.com Consulting
This message was edited by Juan Pablo G. on 2002-08-27 17:25

 

[rico]

thank you that was exactly the answer I needed.

 

[XL-Dennis]

Juan,

Just a question of clarification:

"...If VBComp.CodeModule.CountOfLines > 2 Then..."

You use it in case there exist an Option Explicit declaration on Line 1?

TIA,
Dennis

 

[Juan Pablo González]

Yes. If you have a worksheet module with that at the top it won't trigger the Macro warning, therefor, it's not "officialy" a macro...

 

[XL-Dennis]

Juan,

Thanks for the clarification

Kind regards,
Dennis