Search VBA Code

56 views
Skip to first unread message

John Wilson

unread,
Jan 30, 2003, 12:05:04 PM1/30/03
to
Someone recently posted a link to a website that had a very nice little
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

unread,
Jan 29, 2003, 1:35:26 PM1/29/03
to
This isn't an add-in solution, but here is a previous post that may help.

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

unread,
Jan 29, 2003, 3:38:58 PM1/29/03
to
John

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

unread,
Jan 29, 2003, 5:58:15 PM1/29/03
to
Mark and Leo,

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

unread,
Jan 30, 2003, 7:52:13 AM1/30/03
to
Hi John,
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

unread,
Jan 30, 2003, 10:37:31 AM1/30/03
to
Leo,

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

unread,
Jan 30, 2003, 10:49:41 AM1/30/03
to
David,

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

unread,
Jan 30, 2003, 11:55:14 AM1/30/03
to
John

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

unread,
Jan 30, 2003, 1:26:54 PM1/30/03
to
Leo,

Thanks.
Looking forward to your reply.
Wish I could figure out how to fix it myself.
Oh well :-( .........someday.

John

Myrna Larson

unread,
Jan 30, 2003, 3:04:41 PM1/30/03
to
I haven't seen the code you refer to, but it problem exits the loop when it finds a match.

John Wilson

unread,
Jan 30, 2003, 3:09:41 PM1/30/03
to
Just my two cents worth.................

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

unread,
Jan 30, 2003, 3:12:12 PM1/30/03
to
Myrna,

The code is included in Leo's message in this same thread if you're interested.

John

Leo Heuser

unread,
Jan 30, 2003, 5:16:21 PM1/30/03
to
John

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

unread,
Jan 30, 2003, 5:23:48 PM1/30/03
to
I don't understand why you need an add-in for the example you cite. You can use Search and
Replace in the VBE, specifying that it should search the entire project, as David said.

John Wilson

unread,
Jan 30, 2003, 6:27:03 PM1/30/03
to
Myrna,

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

unread,
Jan 31, 2003, 5:45:07 AM1/31/03
to
Hi John

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

unread,
Jan 31, 2003, 5:43:30 PM1/31/03
to
Leo,

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

unread,
Feb 2, 2003, 9:42:57 AM2/2/03
to
John

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

unread,
Feb 2, 2003, 10:30:56 AM2/2/03
to
Leo,

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

unread,
Feb 2, 2003, 10:39:54 AM2/2/03
to
You're welcome, John.
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

unread,
Feb 23, 2003, 11:51:47 PM2/23/03
to
Hello John,

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

unread,
Feb 26, 2003, 12:50:37 PM2/26/03
to
Brian,

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

Reply all
Reply to author
Forward
0 new messages