Detecting Macro Code behind a sheet (2)

  • Thread starter Thread starter Chris Gorham
  • Start date Start date
C

Chris Gorham

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.
 
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
 
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?
 
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
 
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???)
 
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.
 
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,

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
 
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.
 
Back
Top