Search VBA Code
John Wilson
add-in that searched for text within all the VBA code of a workbook
and returned the line numbers where the text was found.
I lost it.
Does anyone remember this (and where to find it)?
Thanx,
John
Mark Driscol
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=ePpg2alu%23GA.259
%40cppssbbsa02.microsoft.com&rnum=6&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DIS
O-8859-1%26q%3Dsearch%2Bcode%2Breturn%2Bline%2Bnumber%2Bgroup%253Amicrosoft.
public.excel.programming
"John Wilson" <jwi...@optonline.net> wrote in message
news:3E395B40...@optonline.net...
Leo Heuser
This code will do the work for you.
It finds every instance of a string in the VBA code
in any workbook in a directory, and inserts a new
sheet in the active workbook with the information.
'Leo Heuser, June 1999
'Windows only due to the property "FileSearch"
Sub FindStringInVBComponents()
Dim CurrentDirectory As String
Dim GetProcType As Integer
Dim SearchDir As String
Dim FindString As String
Dim ProcType As Long
Dim OldStatusBar As String
Dim Headings As Variant
Dim StartCell As Object
Dim NextRow As Long
Dim MaxLines As Long
Dim wbName As String
Dim ShName As String
Dim FindText As String
Dim vbcom As Object
Dim ProcName As String
Dim Found As Long
Dim FirstLine As Long
Dim SaveFirstLine As Long
Dim SaveFileName As String
Dim SaveCompName As String
Dim CloseFile As Boolean
Dim wb As Object
Dim FileToOpen As String
Dim fFileName As String
Dim EmptyLine As Boolean
Dim CompName As String
Dim FirstProcLine As Long
Dim ProcLine As Long
Dim Counter As Long
On Error Resume Next
CurrentDirectory = CurDir
Do
SearchDir = _
Application.InputBox("Enter path to directory.", _
"Directory", Type:=2)
Err.Number = 0
ChDir (SearchDir)
Loop Until Err.Number = 0
ChDir CurrentDirectory
FindString = _
Application.InputBox("Enter string to search for.", _
"Searchstring", Type:=2)
'In the VBA-editor (<Alt><F11>) set a reference to
'"Microsoft Visual Basic for Applications Extensibility"
'with Tools > References to get access to the vbext_pk-constants
GetProcType = 4
Select Case GetProcType
Case 1
' Procedures that return value of a property
ProcType = vbext_pk_Get
Case 2
' Procedures that assign value to a property
ProcType = vbext_pk_Let
Case 3
' Procedures that set a reference to an object
ProcType = vbext_pk_Set
Case 4
' Sub and Function procedures
ProcType = vbext_pk_Proc
End Select
On Error GoTo Finito
OldStatusBar = Application.DisplayStatusBar
Headings = Array("File", "Component", _
"Procedure", "Comp-line", "Proc-line")
MaxLines = 100000
ActiveWorkbook.Worksheets.Add.Move _
After:=ActiveWorkbook.Worksheets(Worksheets.Count)
wbName = ActiveWorkbook.Name
ShName = Worksheets(Worksheets.Count).Name
Set StartCell = Workbooks(wbName).Worksheets(ShName).Range("A1")
NextRow = StartCell.Row + 2
FindText = "Find the string: " & Chr(34) & FindString & _
Chr(34) & " in " & SearchDir
With StartCell
.Value = FindText
.Font.Size = 12
End With
Application.StatusBar = String(40, Chr(32)) & Found & " found"
Worksheets(ShName).Activate
For Counter = 0 To UBound(Headings)
With StartCell.Offset(2, Counter)
.Value = Headings(Counter)
.Font.Bold = True
End With
Next Counter
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With
With Application.FileSearch
.Filename = "*.xls"
.LookIn = SearchDir
.Execute
For Counter = 1 To .FoundFiles.Count
CloseFile = True
FileToOpen = .FoundFiles(Counter)
For Each wb In Workbooks
If wb.FullName = FileToOpen Then
wb.Activate
CloseFile = False
End If
Next wb
If CloseFile Then
Application.EnableEvents = False
Workbooks.Open Filename:=FileToOpen, updatelinks:=0, _
ignorereadonlyrecommended:=True
Application.EnableEvents = True
End If
fFileName = ActiveWorkbook.Name
EmptyLine = False
For Each vbcom In Workbooks(fFileName).VBProject.VBComponents
FirstLine = 1
SaveFirstLine = 0
SaveCompName = ""
CompName = vbcom.Name
FirstProcLine = 1
Do While vbcom.CodeModule. _
Find(FindString, FirstLine, 1, MaxLines, 1)
If FirstLine < SaveFirstLine Then Exit Do
Found = Found + 1
EmptyLine = True
Application.StatusBar = String(40, Chr(32)) & _
Found & " found"
ProcName = vbcom.CodeModule. _
ProcOfLine(FirstLine, ProcType)
If ProcName = "" Then ProcName = "Declarations"
On Error Resume Next
FirstProcLine = vbcom.CodeModule. _
ProcBodyLine(ProcName, ProcType)
On Error GoTo Finito
ProcLine = FirstLine - FirstProcLine + 1
With StartCell
If fFileName <> SaveFileName Then
.Offset(NextRow, 0).Value = fFileName
End If
If CompName <> SaveCompName Then
.Offset(NextRow, 1).Value = CompName
End If
.Offset(NextRow, 2).Value = ProcName
.Offset(NextRow, 3).Value = FirstLine
If ProcName <> "Declarations" Then
.Offset(NextRow, 4).Value = ProcLine
End If
End With
FirstLine = FirstLine + 1
SaveFirstLine = FirstLine
SaveFileName = fFileName
NextRow = NextRow + 1
Loop
SaveCompName = CompName
Next vbcom
If CloseFile Then
Application.EnableEvents = False
Workbooks(fFileName).Close savechanges:=False
Application.EnableEvents = True
End If
If EmptyLine = True Then NextRow = NextRow + 1
Next Counter
End With
Workbooks(wbName).Activate
StartCell.Offset(3, 0).Select
ActiveWindow.FreezePanes = True
StartCell.Resize(1, 10).MergeCells = True
Worksheets(ShName).Columns("A:IV").AutoFit
Finito:
With Application
.EnableEvents = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
.StatusBar = False
.DisplayStatusBar = OldStatusBar
End With
Set StartCell = Nothing
Set vbcom = Nothing
Set wb = Nothing
End Sub
--
Best regards
Leo Heuser
MVP Excel
"John Wilson" <jwi...@optonline.net> skrev i en meddelelse
news:3E395B40...@optonline.net...
John Wilson
I did eventually find what I was looking for.
The add-in is "FindInFiles.xla"
The web site is:
http://www.xlrotor.com/excel_stuff.htm
Says that it was adapted by Brian Murphy from
Rob Bovey's VBA Code Documentor.
Anyway, it seems to work quite well (and rather quickly too).
Thanks,
John
David McRitchie
Another way is to do it manually then you get to see the
string you are looking for in the actual context. One
project (workbook) at a time.
Start within the first module for the project as found
from Ctrl+R in the Visual Basic Editor.
Ctrl+F then in the dialog activate the
last radio button on the left "current project"
HTH, I'm definitely bookmarking this thread.
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"John Wilson" <jwi...@optonline.net> wrote in message news:3E395B40...@optonline.net...
John Wilson
This wasn't what I was originally looking for but it's a really
useful concept and something that would be quite a handy
tool to have (especially for someone like me who can't
remember from one day to the next what I wrote (or where)).
I tried it, but it only seems to find the instances of a particular
string in the last .xls file in a directory.
Took four differently named files (with duplicate coding) and
it only reports instances of a string in one of those files.
If I delete that file and run it again, it finds the string in only
one of the remaining files.
I really do think this could be a very useful tool if I could get
it to work.
Using Excel 2000 under Windows 98
Thanks,
John
John Wilson
For what I was trying to do yesterday, it never even occurred
to me to try the simple way that you suggested.
I couldn't see the forest because there were too many trees
in the way.
Thanks for the reminder.
The code on Brian Murphy's site works quite well for a project
that you already have opened and it's lightning fast.
The concept behind Leo's code holds a lot of promise too
if you're looking for a snippet that you know you used somewhere
but have no idea what file it's in. Couldn't get Leo's to work
as he described it yet though.
Thanks,
John
Leo Heuser
Thanks for the feedback!
I haven't had this kind of problem (at least I don't think
so :-) with the code, but I'll have a look at it and get back
to you in a couple of days.
--
Best regards
Leo Heuser
MVP Excel
"John Wilson" <jwi...@optonline.net> skrev i en meddelelse
news:3E3946BB...@optonline.net...
John Wilson
Thanks.
Looking forward to your reply.
Wish I could figure out how to fix it myself.
Oh well :-( .........someday.
John
Myrna Larson
John Wilson
Had a minor problem in a workbook today.
Workbook.BeforeSave Event wasn't firing.
Had a pretty good idea why, but where in 1,466 lines of
code was the problem.
Used Brian Murphy's AddIn.
Searched for "EnableEvents"
Found 10 lines of code containing that string in less than a second.
Four of them were set to "True" and six were "False"
hmmmmmm?????
DoubleClicked on the last "False" statement and was taken
directly to that specific line of code to edit it.
It doesn't get much easier than this.
John
John Wilson
The code is included in Leo's message in this same thread if you're interested.
John
Leo Heuser
Marvellous how a couple of days just whizz by :-)
This version should work.
The only difference is, that I have moved the line
MaxLines = 100000
down, but this is crucial (in Excel 2000 at least),
since the value of MaxLines changes inside the Do While - Loop.
The routine was developed under Excel 97, and I have
to test it there one day to see, if there is a difference.
'Leo Heuser, June 1999/January 2003
MaxLines = 100000
--
Best regards
Leo Heuser
MVP Excel
"John Wilson" <jwi...@optonline.net> skrev i en meddelelse
news:3E396E6E...@optonline.net...
Myrna Larson
Replace in the VBE, specifying that it should search the entire project, as David said.
John Wilson
I didn't need the add-in to accomplish what I was trying to do.
I was just suggesting that it's so much simpler to use the add-in.
A lot of threads in these ng's are for ways to do things, simpler,
faster and easier. IMHO, this is a really neat little tool (especially
for people like myself who still make stupid mistakes).
Select the Add-In from the VBE toolbar, type in the search string and
bingo, every line of code that the string appears in (with the
module name and line number) appears instantly on the display.
Double click a line on the display and you go right to the line of code.
Try it for yourself and see what you think:
http://www.xlrotor.com/excel_stuff.htm
The file is FindInFiles.zip
The code isn't protected so you can browse through it before you
try it.
Leo's code offers a different perspective in that you can search an
entire directory for a string in VBA code without having to open up
each workbook manually and search through it.
Usefulness??? Let's say that I knew I used an Excel4Macro in one
of my workbooks but can't remember which one. Leo's code
(which I'm sure he'll have a fix for) would list every workbook that
the string "Excel4Macro" appeared in complete with workbook name
module and line number.
John
Leo Heuser
I have added some space and a couple of minor
adjustments to the code, so please use this version.
'Leo Heuser, June 1999/January 31 - 2003
'Windows only due to the property "FileSearch"
'
'In the VBA-editor (<Alt><F11>) set a reference to
'"Microsoft Visual Basic for Applications Extensibility"
'with Tools > References to get access to the vbext_pk-constants
Sub FindStringInVBComponents()
Dim CloseFile As Boolean
Dim CompName As String
Dim Counter As Long
Dim CurrentDirectory As String
Dim EmptyLine As Boolean
Dim fFileName As String
Dim FileToOpen As String
Dim FindString As String
Dim FindText As String
Dim FirstLine As Long
Dim FirstProcLine As Long
Dim Found As Long
Dim GetProcType As Long
Dim Headings As Variant
Dim NextRow As Long
Dim OldStatusBar As String
Dim ProcLine As Long
Dim ProcName As String
Dim ProcType As Long
Dim SaveCompName As String
Dim SaveFirstLine As Long
Dim SaveFileName As String
Dim SearchDir As String
Dim ShName As String
Dim StartCell As Range
Dim wb As Workbook
Dim vbcom As Object
Dim wbName As String
On Error Resume Next
CurrentDirectory = CurDir
Do
SearchDir = _
Application.InputBox("Enter path to directory.", _
"Directory", Type:=2)
If SearchDir = "False" Then GoTo Finito
Err.Number = 0
ChDir (SearchDir)
Loop Until Err.Number = 0
ChDir CurrentDirectory
FindString = _
Application.InputBox("Enter string to search for.", _
"Searchstring", Type:=2)
If FindString = "False" Then GoTo Finito
GetProcType = 4
Select Case GetProcType
Case 1
' Procedures that return value of a property
ProcType = vbext_pk_Get
Case 2
' Procedures that assign value to a property
ProcType = vbext_pk_Let
Case 3
' Procedures that set a reference to an object
ProcType = vbext_pk_Set
Case 4
' Sub and Function procedures
ProcType = vbext_pk_Proc
End Select
On Error GoTo Finito
OldStatusBar = Application.DisplayStatusBar
Headings = Array("File", "Component", _
"Procedure", "Comp-line", "Proc-line")
ActiveWorkbook.Worksheets.Add.Move _
After:=ActiveWorkbook.Worksheets(Worksheets.Count)
wbName = ActiveWorkbook.Name
ShName = Worksheets(Worksheets.Count).Name
Set StartCell = _
Workbooks(wbName).Worksheets(ShName).Range("A1")
NextRow = StartCell.Row + 2
FindText = "Find the string: " & Chr(34) & FindString & _
Chr(34) & " in " & SearchDir
With StartCell
.Value = FindText
.Font.Size = 12
End With
Application.StatusBar = _
String(40, Chr(32)) & Found & " found"
Worksheets(ShName).Activate
For Counter = 0 To UBound(Headings)
With StartCell.Offset(2, Counter)
.Value = Headings(Counter)
.Font.Bold = True
End With
Next Counter
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With
With Application.FileSearch
.Filename = "*.xls"
.LookIn = SearchDir
.Execute
For Counter = 1 To .FoundFiles.Count
CloseFile = True
FileToOpen = .FoundFiles(Counter)
For Each wb In Workbooks
If wb.FullName = FileToOpen Then
wb.Activate
CloseFile = False
Exit For
End If
Next wb
If CloseFile Then
Application.EnableEvents = False
Workbooks.Open Filename:=FileToOpen, updatelinks:=0, _
ignorereadonlyrecommended:=True
Application.EnableEvents = True
End If
fFileName = ActiveWorkbook.Name
EmptyLine = False
For Each vbcom In Workbooks(fFileName). _
VBProject.VBComponents
FirstLine = 1
SaveFirstLine = 0
SaveCompName = ""
CompName = vbcom.Name
FirstProcLine = 1
Do While vbcom.CodeModule. _
Find(FindString, FirstLine, 1, 100000, 1)
On Error Resume Next
On Error GoTo Finito
John Wilson
Tried it out today and it works quite well.
It'll be a welcome addition to my toolbox (and hopefully
others following this thread can use it too).
It won't work if the VBA project is password protected
(and it obviously can't), but it does stop when it runs into
that situation. Is there any way to have it just skip that file
and continue on it's merry way?
Thanks,
John
Leo Heuser
Here愀 a fix for that:
Sub FindStringInVBComponents()
'Leo Heuser, June 1999/February 1 - 2003
'Windows only due to the property "FileSearch"
'
'Protected VBAprojects are skipped in the process.
'
'In the VBA-editor (<Alt><F11>) set a reference to
'"Microsoft Visual Basic for Applications Extensibility"
'with Tools > References to get access to the vbext_pk-constants
'
Dim CloseFile As Boolean
Dim CompName As String
Dim Counter As Long
Dim CurrentDirectory As String
Dim Dummy As VBComponent
Dim EmptyLine As Boolean
Dim fFileName As String
Dim FileToOpen As String
Dim FindString As String
Dim FindText As String
Dim FirstLine As Long
Dim FirstProcLine As Long
Dim Found As Long
Dim GetProcType As Long
Dim Headings As Variant
Dim NextRow As Long
Dim OldStatusBar As String
Dim ProcLine As Long
Dim ProcName As String
Dim ProcType As Long
Dim SaveCompName As String
Dim SaveFirstLine As Long
Dim SaveFileName As String
Dim SearchDir As String
Dim ShName As String
Dim StartCell As Range
Dim wb As Workbook
Dim vbCom As VBComponent
Dim wbName As String
On Error Resume Next
CurrentDirectory = CurDir
ChDir CurrentDirectory
GetProcType = 4
On Error GoTo Finito
On Error Resume Next
Set Dummy = Workbooks(fFileName).VBProject.VBComponents(1)
If Err.Number = 50289 Then
On Error GoTo Finito
With StartCell
.Offset(NextRow, 0).Value = fFileName
.Offset(NextRow, 1).Value = "VBAProject is protected"
.Offset(NextRow, 0).Resize(1, 2).Font.Bold = True
End With
NextRow = NextRow + 2
Else
On Error GoTo Finito
For Each vbCom In Workbooks(fFileName). _
VBProject.VBComponents
FirstLine = 1
SaveFirstLine = 0
SaveCompName = ""
CompName = vbCom.Name
FirstProcLine = 1
Do While vbCom.CodeModule. _
Find(FindString, FirstLine, 1, 100000, 1)
If FirstLine < SaveFirstLine Then Exit Do
Found = Found + 1
EmptyLine = True
Application.StatusBar = String(40, Chr(32)) & _
Found & " found"
ProcName = vbCom.CodeModule. _
ProcOfLine(FirstLine, ProcType)
If ProcName = "" Then ProcName = "Declarations"
On Error Resume Next
FirstProcLine = vbCom.CodeModule. _
ProcBodyLine(ProcName, ProcType)
On Error GoTo Finito
ProcLine = FirstLine - FirstProcLine + 1
With StartCell
If fFileName <> SaveFileName Then
.Offset(NextRow, 0).Value = fFileName
End If
If CompName <> SaveCompName Then
.Offset(NextRow, 1).Value = CompName
End If
.Offset(NextRow, 2).Value = ProcName
.Offset(NextRow, 3).Value = FirstLine
If ProcName <> "Declarations" Then
.Offset(NextRow, 4).Value = ProcLine
End If
End With
FirstLine = FirstLine + 1
SaveFirstLine = FirstLine
SaveFileName = fFileName
NextRow = NextRow + 1
Loop
SaveCompName = CompName
Next vbCom
End If
"John Wilson" <jwi...@optonline.net> skrev i en meddelelse
news:3E3AFC12...@optonline.net...
John Wilson
And it even lists the names of the files it couldn't access
because the VBA was protected.
Cool......
This'll definitely come in handy.
Thanks a lot,
John
Leo Heuser
Glad to be able to help.
--
Best regards
Leo Heuser
MVP Excel
"John Wilson" <jwi...@optonline.net> skrev i en meddelelse
news:3E3D39B0...@optonline.net...
Brian Murphy
I hope you get good use out of FindInFiles. I use it a lot myself.
If you encounter any problems or quirks with it, please email me.
Be aware that you can search for strings that begin or end with a
carriage return. You have to copy and paste the string to the Find
field since you can't type it there (type it in the immediate window,
and copy it from there). There is no way to do a search like this
without a macro.
Regards,
Brian Murphy
John Wilson
It seems to work just fine.
Just had the opportunity to use it again this morning.
Changed a tab name and had to search the entire project
(approx 2500 lines of code) to make corrections.
FindinFiles made it an easy task to accomplish.
Thanks again,
John