Hi,

I'm writing some code to detect if there is any VBA code
behind a sheet - useful to those that audit large models
with 20+ sheets.

From a previous post to this group, 2 solutions have been
suggested (for which I thank the individuals concerned)-
but both have problems;

I run Excel 2000, but may want to have it run on later
versions. Also I don't want people fiddling around in the
references section of the VB Editor...

This solution creates an error "user type not defined" for
objComponent....

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

this solution runs, but ocassionally seems to detect lines
of code in a sheet which aren't there...

Option Explicit
Sub testme()

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).CodeModule
If VBCodeMod.CountOfLines 0 Then
macro = "True"
Else
macro = ""
End If
Next intCount
End Sub

Any help appreciated...and thanks again to those that have
provided the above code, no criticism of their expertise
is intended...Chris

If you change your references to type Object as Dave Peterson did in your
second example, you won't need a reference to the Visual Basic Extensibility
library as recommended by Rob Bovey (and which you apparently didn't create
and is why you are getting an error).

I believe you have received good code. You need to invest a little time in
understanding it and figuring out why it might appear to not provide what
you need.

--
Regards,
Tom Ogilvy

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

I'm writing some code to detect if there is any VBA code
behind a sheet - useful to those that audit large models
with 20+ sheets.

From a previous post to this group, 2 solutions have been
suggested (for which I thank the individuals concerned)-
but both have problems;

I run Excel 2000, but may want to have it run on later
versions. Also I don't want people fiddling around in the
references section of the VB Editor...

This solution creates an error "user type not defined" for
objComponent....

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

this solution runs, but ocassionally seems to detect lines
of code in a sheet which aren't there...

Option Explicit
Sub testme()

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).CodeModule
If VBCodeMod.CountOfLines 0 Then
macro = "True"
Else
macro = ""
End If
Next intCount
End Sub

Any help appreciated...and thanks again to those that have
provided the above code, no criticism of their expertise
is intended...Chris

Hi,

yes, Dave Peterson's code works fine - but even he admits
in his reply that ocassional spurious code is detected -
when none exists

Thks...Chris

-----Original Message-----
If you change your references to type Object as Dave
Peterson did in your
second example, you won't need a reference to the Visual
Basic Extensibility
library as recommended by Rob Bovey (and which you
apparently didn't create
and is why you are getting an error).

I believe you have received good code. You need to
invest a little time in
understanding it and figuring out why it might appear to
not provide what
you need.

--
Regards,
Tom Ogilvy

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

I'm writing some code to detect if there is any VBA code
behind a sheet - useful to those that audit large models
with 20+ sheets.

From a previous post to this group, 2 solutions have
been
suggested (for which I thank the individuals concerned)-
but both have problems;

I run Excel 2000, but may want to have it run on later
versions. Also I don't want people fiddling around in
the
references section of the VB Editor...

This solution creates an error "user type not defined"
for
objComponent....

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

this solution runs, but ocassionally seems to detect
lines
of code in a sheet which aren't there...

Option Explicit
Sub testme()

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).CodeModule
If VBCodeMod.CountOfLines 0 Then
macro = "True"
Else
macro = ""
End If
Next intCount
End Sub

Any help appreciated...and thanks again to those that
have
provided the above code, no criticism of their expertise
is intended...Chris



.

If you get such a false report, then if you go into each module and do
ctrl+A, then delete and close the module.

then rerun, do you get a false report?

--
Regards,
Tom Ogilvy

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

yes, Dave Peterson's code works fine - but even he admits
in his reply that ocassional spurious code is detected -
when none exists

Thks...Chris

-----Original Message-----
If you change your references to type Object as Dave
Peterson did in your
second example, you won't need a reference to the Visual
Basic Extensibility
library as recommended by Rob Bovey (and which you
apparently didn't create
and is why you are getting an error).

I believe you have received good code. You need to
invest a little time in
understanding it and figuring out why it might appear to
not provide what
you need.

--
Regards,
Tom Ogilvy

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

I'm writing some code to detect if there is any VBA code
behind a sheet - useful to those that audit large models
with 20+ sheets.

From a previous post to this group, 2 solutions have
been
suggested (for which I thank the individuals concerned)-
but both have problems;

I run Excel 2000, but may want to have it run on later
versions. Also I don't want people fiddling around in
the
references section of the VB Editor...

This solution creates an error "user type not defined"
for
objComponent....

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

this solution runs, but ocassionally seems to detect
lines
of code in a sheet which aren't there...

Option Explicit
Sub testme()

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).CodeModule
If VBCodeMod.CountOfLines 0 Then
macro = "True"
Else
macro = ""
End If
Next intCount
End Sub

Any help appreciated...and thanks again to those that
have
provided the above code, no criticism of their expertise
is intended...Chris



.

Good Call..
it appears that even if there is no code, but there is a
blank line created by simply pressing return, then this
will be detected as macro code.
any work arounds..??

Chris

-----Original Message-----
If you get such a false report, then if you go into each
module and do
ctrl+A, then delete and close the module.

then rerun, do you get a false report?

--
Regards,
Tom Ogilvy

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

yes, Dave Peterson's code works fine - but even he
admits
in his reply that ocassional spurious code is detected -
when none exists

Thks...Chris

-----Original Message-----
If you change your references to type Object as Dave
Peterson did in your
second example, you won't need a reference to the
Visual
Basic Extensibility
library as recommended by Rob Bovey (and which you
apparently didn't create
and is why you are getting an error).

I believe you have received good code. You need to
invest a little time in
understanding it and figuring out why it might appear
to
not provide what
you need.

--
Regards,
Tom Ogilvy

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

I'm writing some code to detect if there is any VBA
code
behind a sheet - useful to those that audit large
models
with 20+ sheets.

From a previous post to this group, 2 solutions have
been
suggested (for which I thank the individuals
concerned)-
but both have problems;

I run Excel 2000, but may want to have it run on
later
versions. Also I don't want people fiddling around in
the
references section of the VB Editor...

This solution creates an error "user type not
defined"
for
objComponent....

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

this solution runs, but ocassionally seems to detect
lines
of code in a sheet which aren't there...

Option Explicit
Sub testme()

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).CodeModule
If VBCodeMod.CountOfLines 0 Then
macro = "True"
Else
macro = ""
End If
Next intCount
End Sub

Any help appreciated...and thanks again to those that
have
provided the above code, no criticism of their
expertise
is intended...Chris



.



.

I don't know if it's spurious and it wasn't occasional. It was everytime I ran
it--both the late binding version and Rob Bovey's early binding (with the
references). I figured that it was a result of telling VBE to force me to
declare my variables.

That was my reference to having
"Tools|Options|Editor tab|Require Variable declaration" checked.

Which is different that spurious.

I figured the easiest way would be to check to see if the line count 2.

But I really don't know if running your code puts those lines in the module.
And if it does, do you want to count that as code. It's there when I look--but
I'm not sure if it's there before I look!

The other thing is you won't catch code like this:

Private Sub Worksheet_Activate(): Application.EnableEvents = False
: Me.Cells.Clear: Application.EnableEvents = True: End Sub

(all one physical line--but who writes like this???)

Chris Gorham wrote:

Hi,

yes, Dave Peterson's code works fine - but even he admits
in his reply that ocassional spurious code is detected -
when none exists

Thks...Chris

-----Original Message-----
If you change your references to type Object as Dave
Peterson did in your
second example, you won't need a reference to the Visual
Basic Extensibility
library as recommended by Rob Bovey (and which you
apparently didn't create
and is why you are getting an error).

I believe you have received good code. You need to
invest a little time in
understanding it and figuring out why it might appear to
not provide what
you need.

--
Regards,
Tom Ogilvy

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

I'm writing some code to detect if there is any VBA code
behind a sheet - useful to those that audit large models
with 20+ sheets.

From a previous post to this group, 2 solutions have
been
suggested (for which I thank the individuals concerned)-
but both have problems;

I run Excel 2000, but may want to have it run on later
versions. Also I don't want people fiddling around in
the
references section of the VB Editor...

This solution creates an error "user type not defined"
for
objComponent....

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

this solution runs, but ocassionally seems to detect
lines
of code in a sheet which aren't there...

Option Explicit
Sub testme()

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).CodeModule
If VBCodeMod.CountOfLines 0 Then
macro = "True"
Else
macro = ""
End If
Next intCount
End Sub

Any help appreciated...and thanks again to those that
have
provided the above code, no criticism of their expertise
is intended...Chris



.


--

Dave Peterson

I guess add code in your routine to see if there is really code detected or
blank spaces - ie, check for the offending condition.

This would require getting the lines and doing a trim, then checking the
length - haven't thought out the possibilities, however.

--
Regards,
Tom Ogilvy

"Chris Gorham" wrote in message
...
Good Call..
it appears that even if there is no code, but there is a
blank line created by simply pressing return, then this
will be detected as macro code.
any work arounds..??

Chris

-----Original Message-----
If you get such a false report, then if you go into each
module and do
ctrl+A, then delete and close the module.

then rerun, do you get a false report?

--
Regards,
Tom Ogilvy

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

yes, Dave Peterson's code works fine - but even he
admits
in his reply that ocassional spurious code is detected -
when none exists

Thks...Chris

-----Original Message-----
If you change your references to type Object as Dave
Peterson did in your
second example, you won't need a reference to the
Visual
Basic Extensibility
library as recommended by Rob Bovey (and which you
apparently didn't create
and is why you are getting an error).

I believe you have received good code. You need to
invest a little time in
understanding it and figuring out why it might appear
to
not provide what
you need.

--
Regards,
Tom Ogilvy

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

I'm writing some code to detect if there is any VBA
code
behind a sheet - useful to those that audit large
models
with 20+ sheets.

From a previous post to this group, 2 solutions have
been
suggested (for which I thank the individuals
concerned)-
but both have problems;

I run Excel 2000, but may want to have it run on
later
versions. Also I don't want people fiddling around in
the
references section of the VB Editor...

This solution creates an error "user type not
defined"
for
objComponent....

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

this solution runs, but ocassionally seems to detect
lines
of code in a sheet which aren't there...

Option Explicit
Sub testme()

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).CodeModule
If VBCodeMod.CountOfLines 0 Then
macro = "True"
Else
macro = ""
End If
Next intCount
End Sub

Any help appreciated...and thanks again to those that
have
provided the above code, no criticism of their
expertise
is intended...Chris



.



.

And to piggyback on Tom's earlier response--if it is "option explicit" that's
causing the problem, you could look for that and if that's the only thing you
find, you can choose to ignore it (or delete it?).

Dave Peterson wrote:

I don't know if it's spurious and it wasn't occasional. It was everytime I ran
it--both the late binding version and Rob Bovey's early binding (with the
references). I figured that it was a result of telling VBE to force me to
declare my variables.

That was my reference to having
"Tools|Options|Editor tab|Require Variable declaration" checked.

Which is different that spurious.

I figured the easiest way would be to check to see if the line count 2.

But I really don't know if running your code puts those lines in the module.
And if it does, do you want to count that as code. It's there when I look--but
I'm not sure if it's there before I look!

The other thing is you won't catch code like this:

Private Sub Worksheet_Activate(): Application.EnableEvents = False
: Me.Cells.Clear: Application.EnableEvents = True: End Sub

(all one physical line--but who writes like this???)

Chris Gorham wrote:

Hi,

yes, Dave Peterson's code works fine - but even he admits
in his reply that ocassional spurious code is detected -
when none exists

Thks...Chris

-----Original Message-----
If you change your references to type Object as Dave
Peterson did in your
second example, you won't need a reference to the Visual
Basic Extensibility
library as recommended by Rob Bovey (and which you
apparently didn't create
and is why you are getting an error).

I believe you have received good code. You need to
invest a little time in
understanding it and figuring out why it might appear to
not provide what
you need.

--
Regards,
Tom Ogilvy

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

I'm writing some code to detect if there is any VBA code
behind a sheet - useful to those that audit large models
with 20+ sheets.

From a previous post to this group, 2 solutions have
been
suggested (for which I thank the individuals concerned)-
but both have problems;

I run Excel 2000, but may want to have it run on later
versions. Also I don't want people fiddling around in
the
references section of the VB Editor...

This solution creates an error "user type not defined"
for
objComponent....

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

this solution runs, but ocassionally seems to detect
lines
of code in a sheet which aren't there...

Option Explicit
Sub testme()

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).CodeModule
If VBCodeMod.CountOfLines 0 Then
macro = "True"
Else
macro = ""
End If
Next intCount
End Sub

Any help appreciated...and thanks again to those that
have
provided the above code, no criticism of their expertise
is intended...Chris



.


--

Dave Peterson


--

Dave Peterson

Dave,

the code detects even blank lines - if you go the the
module and delete everything, then it work's fine. I guess
I need code that will detect if there is any code on each
line, if not then it's blank

Sorry to ask to be spoon fed...whats the statement for
returning the content of a line if its detected...??

Thks...Chris

-----Original Message-----
And to piggyback on Tom's earlier response--if it
is "option explicit" that's
causing the problem, you could look for that and if
that's the only thing you
find, you can choose to ignore it (or delete it?).

Dave Peterson wrote:

I don't know if it's spurious and it wasn't
occasional. It was everytime I ran
it--both the late binding version and Rob Bovey's early
binding (with the
references). I figured that it was a result of telling
VBE to force me to
declare my variables.

That was my reference to having
"Tools|Options|Editor tab|Require Variable declaration"
checked.

Which is different that spurious.

I figured the easiest way would be to check to see if
the line count 2.

But I really don't know if running your code puts those
lines in the module.
And if it does, do you want to count that as code.
It's there when I look--but
I'm not sure if it's there before I look!

The other thing is you won't catch code like this:

Private Sub Worksheet_Activate():
Application.EnableEvents = False
: Me.Cells.Clear: Application.EnableEvents = True: End
Sub

(all one physical line--but who writes like this???)

Chris Gorham wrote:

Hi,

yes, Dave Peterson's code works fine - but even he
admits
in his reply that ocassional spurious code is
detected -
when none exists

Thks...Chris

-----Original Message-----
If you change your references to type Object as Dave
Peterson did in your
second example, you won't need a reference to the
Visual
Basic Extensibility
library as recommended by Rob Bovey (and which you
apparently didn't create
and is why you are getting an error).

I believe you have received good code. You need to
invest a little time in
understanding it and figuring out why it might
appear to
not provide what
you need.

--
Regards,
Tom Ogilvy

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

I'm writing some code to detect if there is any
VBA code
behind a sheet - useful to those that audit large
models
with 20+ sheets.

From a previous post to this group, 2 solutions
have
been
suggested (for which I thank the individuals
concerned)-
but both have problems;

I run Excel 2000, but may want to have it run on
later
versions. Also I don't want people fiddling around
in
the
references section of the VB Editor...

This solution creates an error "user type not
defined"
for
objComponent....

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

this solution runs, but ocassionally seems to
detect
lines
of code in a sheet which aren't there...

Option Explicit
Sub testme()

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).CodeModule
If VBCodeMod.CountOfLines 0 Then
macro = "True"
Else
macro = ""
End If
Next intCount
End Sub

Any help appreciated...and thanks again to those
that
have
provided the above code, no criticism of their
expertise
is intended...Chris



.


--

Dave Peterson


--

Dave Peterson

.

This should get you started:

Option Explicit
Sub testme()

Dim VBCodeMod As CodeModule
Dim totalLines As Long
Dim iCtr As Long
Dim myStr As String


Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("sheet1").Code Module
With VBCodeMod
totalLines = .CountOfLines
If totalLines 0 Then
For iCtr = 1 To totalLines
myStr = LCase(Trim(.Lines(iCtr, 1)))
MsgBox myStr
Next iCtr
End If
End With

End Sub

But remember to look at Chip Pearson's page:
http://www.cpearson.com/excel/vbe.htm

It's where I grabbed the shell for this example.

Chris Gorham wrote:

Dave,

the code detects even blank lines - if you go the the
module and delete everything, then it work's fine. I guess
I need code that will detect if there is any code on each
line, if not then it's blank

Sorry to ask to be spoon fed...whats the statement for
returning the content of a line if its detected...??

Thks...Chris

-----Original Message-----
And to piggyback on Tom's earlier response--if it
is "option explicit" that's
causing the problem, you could look for that and if
that's the only thing you
find, you can choose to ignore it (or delete it?).

Dave Peterson wrote:

I don't know if it's spurious and it wasn't
occasional. It was everytime I ran
it--both the late binding version and Rob Bovey's early
binding (with the
references). I figured that it was a result of telling
VBE to force me to
declare my variables.

That was my reference to having
"Tools|Options|Editor tab|Require Variable declaration"
checked.

Which is different that spurious.

I figured the easiest way would be to check to see if
the line count 2.

But I really don't know if running your code puts those
lines in the module.
And if it does, do you want to count that as code.
It's there when I look--but
I'm not sure if it's there before I look!

The other thing is you won't catch code like this:

Private Sub Worksheet_Activate():
Application.EnableEvents = False
: Me.Cells.Clear: Application.EnableEvents = True: End
Sub

(all one physical line--but who writes like this???)

Chris Gorham wrote:

Hi,

yes, Dave Peterson's code works fine - but even he
admits
in his reply that ocassional spurious code is
detected -
when none exists

Thks...Chris

-----Original Message-----
If you change your references to type Object as Dave
Peterson did in your
second example, you won't need a reference to the
Visual
Basic Extensibility
library as recommended by Rob Bovey (and which you
apparently didn't create
and is why you are getting an error).

I believe you have received good code. You need to
invest a little time in
understanding it and figuring out why it might
appear to
not provide what
you need.

--
Regards,
Tom Ogilvy

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

I'm writing some code to detect if there is any
VBA code
behind a sheet - useful to those that audit large
models
with 20+ sheets.

From a previous post to this group, 2 solutions
have
been
suggested (for which I thank the individuals
concerned)-
but both have problems;

I run Excel 2000, but may want to have it run on
later
versions. Also I don't want people fiddling around
in
the
references section of the VB Editor...

This solution creates an error "user type not
defined"
for
objComponent....

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

this solution runs, but ocassionally seems to
detect
lines
of code in a sheet which aren't there...

Option Explicit
Sub testme()

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).CodeModule
If VBCodeMod.CountOfLines 0 Then
macro = "True"
Else
macro = ""
End If
Next intCount
End Sub

Any help appreciated...and thanks again to those
that
have
provided the above code, no criticism of their
expertise
is intended...Chris



.


--

Dave Peterson


--

Dave Peterson

.


--

Dave Peterson

Dave,

this seems to work great...Happy New Year to you!!

Chris

-----Original Message-----
This should get you started:

Option Explicit
Sub testme()

Dim VBCodeMod As CodeModule
Dim totalLines As Long
Dim iCtr As Long
Dim myStr As String


Set VBCodeMod = ThisWorkbook.VBProject.VBComponents
("sheet1").CodeModule
With VBCodeMod
totalLines = .CountOfLines
If totalLines 0 Then
For iCtr = 1 To totalLines
myStr = LCase(Trim(.Lines(iCtr, 1)))
MsgBox myStr
Next iCtr
End If
End With

End Sub

But remember to look at Chip Pearson's page:
http://www.cpearson.com/excel/vbe.htm

It's where I grabbed the shell for this example.

Chris Gorham wrote:

Dave,

the code detects even blank lines - if you go the the
module and delete everything, then it work's fine. I
guess
I need code that will detect if there is any code on
each
line, if not then it's blank

Sorry to ask to be spoon fed...whats the statement for
returning the content of a line if its detected...??

Thks...Chris

-----Original Message-----
And to piggyback on Tom's earlier response--if it
is "option explicit" that's
causing the problem, you could look for that and if
that's the only thing you
find, you can choose to ignore it (or delete it?).

Dave Peterson wrote:

I don't know if it's spurious and it wasn't
occasional. It was everytime I ran
it--both the late binding version and Rob Bovey's
early
binding (with the
references). I figured that it was a result of
telling
VBE to force me to
declare my variables.

That was my reference to having
"Tools|Options|Editor tab|Require Variable
declaration"
checked.

Which is different that spurious.

I figured the easiest way would be to check to see if
the line count 2.

But I really don't know if running your code puts
those
lines in the module.
And if it does, do you want to count that as code.
It's there when I look--but
I'm not sure if it's there before I look!

The other thing is you won't catch code like this:

Private Sub Worksheet_Activate():
Application.EnableEvents = False
: Me.Cells.Clear: Application.EnableEvents = True:
End
Sub

(all one physical line--but who writes like this???)

Chris Gorham wrote:

Hi,

yes, Dave Peterson's code works fine - but even he
admits
in his reply that ocassional spurious code is
detected -
when none exists

Thks...Chris

-----Original Message-----
If you change your references to type Object as
Dave
Peterson did in your
second example, you won't need a reference to the
Visual
Basic Extensibility
library as recommended by Rob Bovey (and which you
apparently didn't create
and is why you are getting an error).

I believe you have received good code. You need
to
invest a little time in
understanding it and figuring out why it might
appear to
not provide what
you need.

--
Regards,
Tom Ogilvy

"Chris Gorham"

wrote in message
...
Hi,

I'm writing some code to detect if there is any
VBA code
behind a sheet - useful to those that audit
large
models
with 20+ sheets.

From a previous post to this group, 2 solutions
have
been
suggested (for which I thank the individuals
concerned)-
but both have problems;

I run Excel 2000, but may want to have it run on
later
versions. Also I don't want people fiddling
around
in
the
references section of the VB Editor...

This solution creates an error "user type not
defined"
for
objComponent....

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

this solution runs, but ocassionally seems to
detect
lines
of code in a sheet which aren't there...

Option Explicit
Sub testme()

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).CodeModule
If VBCodeMod.CountOfLines 0 Then
macro = "True"
Else
macro = ""
End If
Next intCount
End Sub

Any help appreciated...and thanks again to those
that
have
provided the above code, no criticism of their
expertise
is intended...Chris



.


--

Dave Peterson


--

Dave Peterson

.


--

Dave Peterson

.