Hi,

I'm designing a tool to cycle through the sheets in a
workbook to detect if there is any code behind them. In
workbooks with a large number of sheets this can be quite
tricky to detect using the VBA editor.
Does anybody know how to do this...??

Rgds

Chris

See Chip Pearson's page on working in with code in the VBE:

http://www.cpearson.com/excel/vbe.htm

--
Regards,
Tom Ogilvy

Chris Gorham wrote in message
...
Hi,

I'm designing a tool to cycle through the sheets in a
workbook to detect if there is any code behind them. In
workbooks with a large number of sheets this can be quite
tricky to detect using the VBA editor.
Does anybody know how to do this...??

Rgds

Chris

This is a macro I use for detecting "code" in an unknown workbook. Maybe
it'll be helpful.

''The purpose of this sub is to find if the active workbook has
''' "programming" in it like VB or XL4 or userforms, etc.
'' Say you've downloaded an XLS and opened it
'' with Shift down. Now you can look for modules in the VBE but you
'' have to check the Thisworkbook and Sheet modules to be sure.

Sub CodeInProject()
''XL2K+:
Dim VBComp As VBComponent, AllComp As VBComponents, ThisProj As
VBProject
''XL97 & XL2K:
''Dim VBComp As Object, AllComp As Object, ThisProj As Object
Dim WS As Worksheet, DLG As DialogSheet
Dim StdModCount As Integer, StdModWithCodeCount As Integer
Dim ClassModCount As Integer
Dim FormCount As Integer, SheetMods As Integer
Dim CodeBehindStr As String
Dim DlgSheetCount As Integer, XL4Sheets As Integer
Dim MsgStr As String
On Error GoTo ProjProt
Set ThisProj = ActiveWorkbook.VBProject

Set AllComp = ThisProj.VBComponents
For Each VBComp In AllComp
With VBComp
Select Case .Type
Case vbext_ct_StdModule
StdModCount = StdModCount + 1
If HasProc(VBComp) Then _
StdModWithCodeCount = StdModWithCodeCount + 1
Case vbext_ct_ClassModule
ClassModCount = ClassModCount + 1
Case vbext_ct_MSForm
FormCount = FormCount + 1
Case vbext_ct_Document
If HasProc(VBComp) Then
CodeBehindStr = CodeBehindStr & VBComp.Name &
Chr(10) & Chr(9)
End If
End Select
End With
Next
If CodeBehindStr < "" Then CodeBehindStr = Left(CodeBehindStr,
Len(CodeBehindStr) - 2)
XL4Sheets = Excel4MacroSheets.Count
DlgSheetCount = DialogSheets.Count
If CodeBehindStr < "" Then
MsgStr = "Document modules with code: " & Chr(10) & Chr(9) &
CodeBehindStr & Chr(10)
End If
If StdModCount 0 Then
MsgStr = MsgStr & StdModCount & " standard modules (" &
StdModWithCodeCount & " with code)." & Chr(10)
End If
If ClassModCount 0 Then
MsgStr = MsgStr & ClassModCount & " class module(s)." & Chr(10)
End If
If FormCount 0 Then
MsgStr = MsgStr & FormCount & " user form(s)." & Chr(10)
End If
If XL4Sheets 0 Then
MsgStr = MsgStr & XL4Sheets & " XL4 macro sheet(s)." & Chr(10)
End If
If DlgSheetCount 0 Then
MsgStr = MsgStr & DlgSheetCount & " dialog sheet(s)." & Chr(10)
End If
If MsgStr = "" Then MsgStr = "No code evident."
MsgBox MsgStr
Exit Sub
ProjProt:
MsgBox "Project projected"
End Sub

Function HasProc(Comp As VBComponent) As Boolean
Dim Counter As Integer, Txt As String
With Comp.CodeModule
For Counter = 1 To .CountOfLines
If .ProcOfLine(Counter, vbext_pk_Proc) < "" Then
HasProc = True
Exit Function
End If
Next
End With
End Function


--
Jim Rech
Excel MVP