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