Searching FOLDERS for VBA string

[SkipVought]

Hey Gang,

From time to time I need to find strings in code in order to make modifications. It seems that Windows Explorer search does not "see" VBA code.

Is there a method for this kind of search?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

Not that I know of. Other than opening the file and using VBA itself to look in the VBAProjects code modules. But if there IS a way...boy, I would like to know as well.

faq219-2884

Gerry
My paintings and sculpture

 

[DaveInIowa]

Hopefully the code below will get you started. You'll have to loop through whatever workbooks you want to search and then output the results to somewhere more useful than MsgBox, but you get the idea.

Be sure to add Reference 'Microsoft Visual Basic for Applications Extensibility 5.3' to your project.

Public Sub FindInVBACode(SearchString As String, InWorkbook As Workbook)
    Dim ix As Long
    Dim vbc As VBIDE.VBComponent
    
    For Each vbc In InWorkbook.VBProject.VBComponents
        
        For ix = 1 To vbc.CodeModule.CountOfLines
        
            Dim line As String
            line = vbc.CodeModule.Lines(ix, 1)
            
            If (InStr(line, SearchString) > 0) Then
                MsgBox "Found " & SearchString & " at line " & ix & " of code module " & vbc.Name
            End If
         
        Next
        
    Next

End Sub

 

[fumei]

Hi Dave, I don't think you read Skip's post fully.

Nor mine.

1. Skip mentions trying to find a string from Windows Explorer. He would, I think, be happy with another route, but the point is (I think) to be able to search for VBA strings without opening each file.

2. I mentioned that I knew of no way beyond opening the files, and looking in the code modules.

Your code:

1. assumes....the file is open
2. only looks in the VBA code of THAT file

Skip, again, I do not know of any way to do this. You are quite correct in that Explorer (or any other shell I know of) does not see VBA code. Besides, even if you could see that file_X had the string, how could you modify it anyway? You would have to open the file and edit the VBA.

Mind you, I certainly CAN see a use for an ability to search for a string within encapsulated VBA. Then once you know that file_X does have the string, THEN you can open it to modify it.

However, I do not think there is a paddle for this one.

faq219-2884

Gerry
My paintings and sculpture

 

[DaveInIowa]

Gerry - I do owe you an apology for not reading your post more carefully as I do see now that I was just parroting your idea.

I think my thought process was reading your post as "Other than opening the file and using VBE itself to look in the VBAProjects code modules", mistakenly thinking you were suggesting a manual approach.