How to search through VBA code files

| | August 5, 2015

I just started a job with a new company where previous developers had created many automated tasks. Of course, there is virtually no documentation and I didn’t have a chance to work with the previous developer so now I’m trying to sift through all these processes looking for one that modifies some specific files.

I’ve scripted all the stored procs in SQL and used a search tool and didn’t find what I was looking for, so now I am wondering if the process I need is located in one of many Access databases that are used. With SQL Server, it was easy to write a C# app to script the procs so I could search through them, but with Access it looks like I’m confined to opening each db individually to search through the code files.

Is there any way to programatically search through VBA code files?

2 Responses to “How to search through VBA code files”

  1. If your interest is searching code modules in an Access database file, you can use the VBE object model. This sample searches for a word in all the modules of the ActiveVBProject of the current database. If the database includes more than one VBProject, you can enumerate the VBProjects collection and search the projects one at a time by name:

    For Each objComponent In Application.VBE.VBProjects(ProjName).VBComponents
    

    Or if you prefer to reference the project by number rather than name, just be aware the numbering starts with 1 rather than 0.

    Public Sub findWordInModules(ByVal pSearchWord As String)
        'Dim objComponent As VBComponent
        ' VBComponent requires reference to Microsoft Visual Basic
        ' for Applications Extensibility; use late binding instead:
        Dim objComponent As Object
        Dim strMessage As String
        Dim strModuleList As String
    
        strModuleList = vbNullString
        For Each objComponent In Application.VBE.ActiveVBProject.VBComponents
            If objComponent.CodeModule.Find(pSearchWord, 1, 1, -1, -1) = True Then
                strModuleList = strModuleList & "; " & objComponent.Name
            End If
        Next objComponent
        strMessage = "Text '" & pSearchWord & "' found in "
        If Len(strModuleList) > 0 Then
            strMessage = strMessage & "modules: " & Mid(strModuleList, 3)
        Else
            strMessage = strMessage & "no modules"
        End If
        Debug.Print strMessage
    End Sub
    

    Review the Access help topic for that Find method; you may prefer different options than I used.

    If you want to target multiple db files and search the modules in each, you could automate this using the OpenDatabase method. I’ll leave the details of that part up to you.

  2. Best to download the free MZ-Tools for VBA and use their search/replace function.

    Start Find
    Find Results

Leave a Reply