Hi,

I'm writing a macro that cycles through all the sheets in
another workbook detecting if there is any code behind
them. Useful if you want to quickly find out if someones
slipped in some code in a 20+ sheet model and don't fancy
clicking on each one in the VB Editor to find out...only
way I know!!

Anyway the code below is kicking out errors - starting
with user-type not defined...

This may involve making changes to the references section
in the VB Editor, but I want a solution that will work for
anyone..

Dim VBComp As Object
Dim VBCodeMod As CodeModule

For intcount = 1 To ActiveWorkbook.Sheets.count

sht_name = Sheets(intcount).Name
Set VBCodeMod = ActiveWorkbook.VBProject.VBComponents
(sht_name).CodeModule
If VBCodeMod.CountOfLines 0 Then
macro = "True"
Else
macro = ""
End If

Next intcount

Chris,

Try this:

Sub testit()
Const vbext_ct_Document = 100
Dim vbc As Object, blnFound As Boolean

blnFound = False
For Each vbc In ActiveWorkbook.VBProject.VBComponents
If vbc.Type = vbext_ct_Document Then
If vbc.CodeModule.CountOfLines -
vbc.CodeModule.CountOfDeclarationLines 0 Then blnFound = True
End If
Next

MsgBox IIf(blnFound, "Macros", "No Macros")
End Sub

Rob

"Chris Gorham" wrote in message
...
Hi,

I'm writing a macro that cycles through all the sheets in
another workbook detecting if there is any code behind
them. Useful if you want to quickly find out if someones
slipped in some code in a 20+ sheet model and don't fancy
clicking on each one in the VB Editor to find out...only
way I know!!

Anyway the code below is kicking out errors - starting
with user-type not defined...

This may involve making changes to the references section
in the VB Editor, but I want a solution that will work for
anyone..

Dim VBComp As Object
Dim VBCodeMod As CodeModule

For intcount = 1 To ActiveWorkbook.Sheets.count

sht_name = Sheets(intcount).Name
Set VBCodeMod = ActiveWorkbook.VBProject.VBComponents
(sht_name).CodeModule
If VBCodeMod.CountOfLines 0 Then
macro = "True"
Else
macro = ""
End If

Next intcount

Hi Chris,

Here's a bit better approach to the problem. All of this type of code
requires a reference to the Visual Basic for Applications Extensibility
object library. This object library is forward compatible, but not backward
compatible, so you need to compile and save this in the earliest version of
Excel that you plan to have it run in. If the user is running Excel 2002 or
higher, they must also check the Trust Access to Visual Basic Project
checkbox under the Tools/Macro/Security/Trusted Sources menu.

Sub CheckForDocObjectCode()
Dim objComponent As VBIDE.VBComponent
For Each objComponent In ActiveWorkbook.VBProject.VBComponents
If objComponent.Type = vbext_ct_Document Then
If objComponent.CodeModule.CountOfLines 0 Then
MsgBox objComponent.Name & " has code."
Else
MsgBox objComponent.Name & " does not have code."
End If
End If
Next objComponent
End Sub

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

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Chris Gorham" wrote in message
...
Hi,

I'm writing a macro that cycles through all the sheets in
another workbook detecting if there is any code behind
them. Useful if you want to quickly find out if someones
slipped in some code in a 20+ sheet model and don't fancy
clicking on each one in the VB Editor to find out...only
way I know!!

Anyway the code below is kicking out errors - starting
with user-type not defined...

This may involve making changes to the references section
in the VB Editor, but I want a solution that will work for
anyone..

Dim VBComp As Object
Dim VBCodeMod As CodeModule

For intcount = 1 To ActiveWorkbook.Sheets.count

sht_name = Sheets(intcount).Name
Set VBCodeMod = ActiveWorkbook.VBProject.VBComponents
(sht_name).CodeModule
If VBCodeMod.CountOfLines 0 Then
macro = "True"
Else
macro = ""
End If

Next intcount

When you're in the VBE, do Tools|references and check:
Microsoft visual basic for Applications Extensibility x.x

Then those types will become available.

On the other hand, you can use a generic object variable:

Option Explicit
Sub testme()

'Dim VBComp as Object ' As VBComponent 'but you didn't use this
Dim VBCodeMod As Object 'As CodeModule
Dim intCount As Long
Dim Sht_name As String
Dim macro As String

For intCount = 1 To ActiveWorkbook.Sheets.Count

Sht_name = Sheets(intCount).CodeName
Set VBCodeMod _
= ActiveWorkbook.VBProject.VBComponents(Sht_name).Co deModule
If VBCodeMod.CountOfLines 0 Then
macro = "True"
Else
macro = ""
End If
Next intCount
End Sub

It's nicer to develop with the reference--you'll get all the intellisense help.
But right before you turn it over to users, get rid of the reference and go back
to generic Objects.

You won't have to worry about having a different version of the library that you
referred to.

ps. note that I changed what sht_name was, too.

And you may want to read more about this type of code from Chip Pearson's web
site:
http://www.cpearson.com/excel/vbe.htm

And watch out. I have "Tools|options|Editor tab, Require variable declaration"
checked.

Even if my module didn't have any real code behind it, I got 2 for the
..countoflines.



Chris Gorham wrote:

Hi,

I'm writing a macro that cycles through all the sheets in
another workbook detecting if there is any code behind
them. Useful if you want to quickly find out if someones
slipped in some code in a 20+ sheet model and don't fancy
clicking on each one in the VB Editor to find out...only
way I know!!

Anyway the code below is kicking out errors - starting
with user-type not defined...

This may involve making changes to the references section
in the VB Editor, but I want a solution that will work for
anyone..

Dim VBComp As Object
Dim VBCodeMod As CodeModule

For intcount = 1 To ActiveWorkbook.Sheets.count

sht_name = Sheets(intcount).Name
Set VBCodeMod = ActiveWorkbook.VBProject.VBComponents
(sht_name).CodeModule
If VBCodeMod.CountOfLines 0 Then
macro = "True"
Else
macro = ""
End If

Next intcount

--

Dave Peterson