Modifying the "Missing Option Explicit" Code, to Search for Existing Text/Code

Question

The Missing Option Explicit search (below) is wonderful.  I just copy and paste the results from the Immediate Window, to a Notepad document, then open the respective objects to add in the missing clause.  Super fast!  Thank you.  While this is an extraordinary time-saver, the Access "Find" feature doesn't get me excited at all.  To select the scope of "Entire Project" with the built-in Find feature, all I am able to do is "X" out of each module that is in "front" of the remaining ones.

If I am looking for a piece of alpha-numerics, such as "*frmsub_city*" it would be faster and more efficient to use a similar procedure to list, in the Immediate Window, each module which contains this textual segment.  Can this code, below, be modified to search for something which exists?

http://answers.microsoft.com/en-us/msoffice/forum/msoffice_access-mso_winother/locating-each-form-module-which-is-missing-option/dd54fb0d-34e5-4ae7-a763-c58971c2c9e9

Answer 1

You don't insert it in the code; you call the Sub and pass the desired value. For example, in the Immediate window you can type

Call findWordInModules("City")

Answer 2

Reviewed the opening page, and cannot afford it right now.

Answer 3

And if I'm searching for the term "City" in all the modules in the db, where do I insert the word City?'Call findWordInModules("DoCmd.Delete Object acTable") PublicSub findWordInModules(ByVal sSearchTerm AsString) 'object.Find(target, startline, startcol, endline, endcol [, wholeword] [, matchcase] [, patternsearch]) 'https://msdn.microsoft.com/en-us/library/aa443952(v=vs.60).aspx ' VBComponent requires reference to Microsoft Visual Basic for Applications Extensibility ' or keep it as is and use Late Binding instead Dim oComponent AsObject'VBComponent ForEach oComponent In Application.VBE.ActiveVBProject.VBComponents If oComponent.CodeModule.Find(sSearchTerm, 1, 1, -1, -1, False, False, False) = TrueThen Debug.Print"Module: " & oComponent.Name 'Name of the current module in which the term was found (at least once) 'Need to execute a recursive listing of where it is found in the module since it could be found more than once Call listLinesinModuleWhereFound(oComponent, sSearchTerm) EndIfNext oComponent EndSubSub listLinesinModuleWhereFound(ByVal oComponent AsObject, ByVal sSearchTerm AsString) Dim lTotalNoLines AsLong'total number of lines within the module being examined Dim lLineNo AsLong'will return the line no where the term is found

    lLineNo = 1
    With oComponent
        lTotalNoLines = .CodeModule.CountOfLines
        DoWhile .CodeModule.Find(sSearchTerm, lLineNo, 1, -1, -1, False, False, False) = True
            Debug.Print vbTab & Trim(.CodeModule.Lines(lLineNo, 1)) 'Remove any padding spaces
            lLineNo = lLineNo + 1    'Restart the search at the next line looking for the next occurence
LoopEndWithEndSub

Answer 4

I should also mention, have you ever seen Mz-Tools, it is an exceptional add-in for the VBE with many features, amongst other a much improved Search function.

Answer 5

Have you seen/tried: http://www.devhut.net/2016/02/24/vba-find-term-in-vba-modulescode/ ?