Programmatically determine if Workbook contains Macros in Excel 20

[Barb Reinhardt]

I'm using Excel 2003 and the end goal is to extract the references that are
used in different projects. I'm guessing I need to open the workbook first.
How do I programmatically determine if the workbook contains macros.

I'm going to use something like this to extract the references (I know it
needs work)

Sub ListReferences()

Dim aWB As Workbook
Dim myWS As Worksheet
Dim myReference As Reference
Dim lRow As Long

Set aWB = ActiveWorkbook
Set myWS = ThisWorkbook.Sheets(1)
Debug.Print aWB.Name
Debug.Print myWS.Name


myWS.Cells(1, 1).Value = "Name"
myWS.Cells(1, 2).Value = "Description"
myWS.Cells(1, 3).Value = "FullPath"
myWS.Cells(1, 4).Value = "GUID"
myWS.Cells(1, 5).Value = "Major"
myWS.Cells(1, 6).Value = "Minor"
lRow = 1
For Each myReference In aWB.VBProject.references
lRow = lRow + 1
myWS.Cells(lRow + 1, 1).Value = myReference.Name
myWS.Cells(lRow + 1, 2).Value = myReference.Description
myWS.Cells(lRow + 1, 3).Value = myReference.FullPath
myWS.Cells(lRow + 1, 4).Value = myReference.GUID
myWS.Cells(lRow + 1, 5).Value = myReference.Major
myWS.Cells(lRow + 1, 6).Value = myReference.Minor
Next myReference

End Sub

 

[Rob Bovey]

Hi Barb,

The only way to do this is to loop through all the VB components in the
project and infer whether there are macros from what you find. This really
depends on your definition of "contains macros". For example, is a workbook
with an empty code module considered to contain macros?

I'll assume for the moment that it requires more than one line of code
in the code module of any VB component for the workbook to be considered as
containing macros. Here's an example of a function that you can pass a
workbook to and test for this:

Sub Test()
If bHasMacros(ActiveWorkbook) Then
MsgBox ActiveWorkbook.Name & " has macros."
End If
End Sub

Function bHasMacros(ByRef wkbBook As Workbook) As Boolean
Dim cmpComponent As VBIDE.VBComponent
For Each cmpComponent In wkbBook.VBProject.VBComponents
If cmpComponent.CodeModule.CountOfLines > 1 Then
bHasMacros = True
Exit Function
End If
Next cmpComponent
End Function

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Ron de Bruin]

For 2007 users

In Excel 2007 we can use this now
If ActiveWorkbook.HasVBProject = True

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Hi Barb,

The only way to do this is to loop through all the VB components in the
project and infer whether there are macros from what you find. This really
depends on your definition of "contains macros". For example, is a workbook
with an empty code module considered to contain macros?

I'll assume for the moment that it requires more than one line of code
in the code module of any VB component for the workbook to be considered as
containing macros. Here's an example of a function that you can pass a
workbook to and test for this:

Sub Test()
If bHasMacros(ActiveWorkbook) Then
MsgBox ActiveWorkbook.Name & " has macros."
End If
End Sub

Function bHasMacros(ByRef wkbBook As Workbook) As Boolean
Dim cmpComponent As VBIDE.VBComponent
For Each cmpComponent In wkbBook.VBProject.VBComponents
If cmpComponent.CodeModule.CountOfLines > 1 Then
bHasMacros = True
Exit Function
End If
Next cmpComponent
End Function

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Jon Peltier]

Barb -

It might be worthwhile asking your IT buffoons to set you up with 2007
earlier, to make this easier with the code Ron has supplied.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

For 2007 users

In Excel 2007 we can use this now
If ActiveWorkbook.HasVBProject = True