Programmatically determine if Workbook contains Macros in Excel 20

B

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
 
R

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
 
R

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


Rob Bovey said:
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
 
J

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
_______


Ron de Bruin said:
For 2007 users

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top