VBA – Find Term in VBA Modules/Code

Sometimes I need to perform searches in the VBA code to try and cleanup client databases, and sometimes Mz_tools, although an exceptional add-in, just doesn’t give me enough flexibility to perform the type of searches I need. It is rare, but it does happen.

Below is a generic procedure to search through all your VBA code for a term. As it stands below, it offers little benefit over Mz-Tools, but it allows you the flexibility to modify it as you see fit to do things Mz-Tools simply can’t.

For instance, I needed to search through the VBA code of a database looking for a given term, where the next line included a specific function. There is simply no way to do this with Mz-Tools, but I added one line to my code below and presto I had my listing to work off of. And let me tell you when you are dealing with complex databases with 100’s of forms modules, reports module, modules, class modules, a routine like the one below can make a drastic improvement!

I must also state that I was surprised by the speed of such a search, it is fast considering the quantity of code I was combing through when I last used it. Access & VBA never cease to amaze me sometimes.
 

'Call findWordInModules("DoCmd.Delete Object acTable")
Public Sub findWordInModules(ByVal sSearchTerm As String)
'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            As Object    'VBComponent

    For Each oComponent In Application.VBE.ActiveVBProject.VBComponents
        If oComponent.CodeModule.Find(sSearchTerm, 1, 1, -1, -1, False, False, False) = True Then
            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)
        End If
    Next oComponent
End Sub

Sub listLinesinModuleWhereFound(ByVal oComponent As Object, ByVal sSearchTerm As String)
    Dim lTotalNoLines         As Long   'total number of lines within the module being examined
    Dim lLineNo               As Long   'will return the line no where the term is found

    lLineNo = 1
    With oComponent
        lTotalNoLines = .CodeModule.CountOfLines
        Do While .CodeModule.Find(sSearchTerm, lLineNo, 1, -1, -1, False, False, False) = True
            Debug.Print vbTab & "Line No:" & lLineNo  & Trim(.CodeModule.Lines(lLineNo, 1)) 'Remove any padding spaces
            lLineNo = lLineNo + 1    'Restart the search at the next line looking for the next occurence
        Loop
    End With
End Sub

As always, be sure to add proper error handling.

I hope this helps.

6 responses on “VBA – Find Term in VBA Modules/Code”

  1. azer

    hi i am trying to use your function but I dont know how to call it …
    1) the link for the Library on MSDN refers to Visual Studio 6.0 , but i see in the comment also “VBComponent requires reference to Microsoft Visual Basic for Applications Extensibility” .
    In my access 2010 references i have loaded : MS visual basic for application Extensibility 5.3
    Is it allright ? or i need to find a Library for visual studio 6 also ?

    2) in the execution Windows i typed : findWordInModules(valide)
    to find some module code containing the word “valide” but then i got an error 40203 ” the research string should be specified “

    1. Daniel Pineault Post author

      1) Follow the instructions given in the function, so what you selected is perfect.

      2) it is because you are not supplying a String to the function. The way you coded it, you are supplying a variable (which does not exist). Instead, you’d need to try:

      ? findWordInModules(“valide”)

  2. Kael Dowdy

    Very slick…exactly what I was looking for!

    I haven’t dug into the Application.VBE.ActiveVBProject object yet to see what properties/objects/collections are available, but I’m curious if you can also return the procedure name and procedure type (function, sub, property, enum, declaration, etc.) along with the line of code where the search term was found.

    If I get a change to work through it, I’ll post what I figure out…

  3. Harry O

    I found this very useful, and made a couple of changes to ‘listLinesinModuleWhereFound’ that better suited my needs: When word found then find name of function or sub that contains the word searched for. There is probably a better way of doing this, but this got me what I wanted.

    Sub listLinesinModuleWhereFound(ByVal oComponent As Object, ByVal sSearchTerm As String)
    Dim lLineNo As Long   'will return the line no where the term is found
    Dim PrevLine As Long
    Dim ProcName As String
 
    lLineNo = 1
    With oComponent
        Do While .CodeModule.Find(sSearchTerm, lLineNo, 1, -1, -1, False, False, False) = True
            If InStr(.CodeModule.Lines(lLineNo, 1), "Sub ") > 0 Or _
                InStr(.CodeModule.Lines(lLineNo, 1), "Function ") > 0 Then
                Debug.Print vbTab & "Mod Ln: " & lLineNo & " " & Trim(.CodeModule.Lines(lLineNo, 1))
                ProcName = Trim(.CodeModule.Lines(lLineNo, 1))
            Else
                For PrevLine = lLineNo - 1 To 0 Step -1 'Not a sub - find Sub/Func name
                    If PrevLine = 0 Then 'Not found, must be declaration?
                        Debug.Print vbTab & "Decl Ln: " & lLineNo & " " & Trim(.CodeModule.Lines(lLineNo, 1))
                        Exit For
                    End If
                    If InStr(.CodeModule.Lines(PrevLine, 1), "Sub ") > 0 Or _
                        InStr(.CodeModule.Lines(PrevLine, 1), "Function ") > 0 Then
                        If ProcName  Trim(.CodeModule.Lines(PrevLine, 1)) Then
                            Debug.Print vbTab & "Mod Ln: " & PrevLine & " " & Trim(.CodeModule.Lines(PrevLine, 1))
                            ProcName = Trim(.CodeModule.Lines(PrevLine, 1))
                        End If
                        Debug.Print vbTab & vbTab & "Proc Ln: " & lLineNo - PrevLine & " " & Trim(.CodeModule.Lines(lLineNo, 1)) 'Remove any padding spaces
                        Exit For
                    End If
                Next
            End If
            lLineNo = lLineNo + 1    'Restart the search at the next line looking for the next occurence
        Loop
    End With
End Sub
  4. Denys Calvin

    My needs were quite modest (create a list of Excel and Word files containing a search string). But working through your clever code got me (hardly a professional!) pointed in the right direction to write my own. Many, many thanks!