Looking for a Sub in VBA code

[grovelli]

I have hundreds of mdb files on my hard drive and would like to find a sub in their vba code whose name I remember.
Is there a utility that allows you to locate the file where the sub is stored?

[HansV]

I don't know of such a utility. You could write it yourself, but it would be a lot of work:
- Loop through a folder and recursively through all its subfolders. You can use Scripting.FileSystemObject for this, or the good old Dir function (but you'd have to use an array or collection to store the folder paths, for you can have only one Dir loop running at a time).
- Use Automation to open each database you encounter.
- Loop through the modules in the database and search for the name.

[grovelli]

Thanks Hans, I'll try to carry out your suggestion

[grovelli]

The hard drive containing the mdb's whose VBA code I intend to scan for the sub is using Windows XP and the mdb's were all created using Access 2003 so I dug out and modified a search routine provided by Rick Dobson in his Programming Access 2003 book; this routine makes use of the FileSearch object which should return a FoundFiles collection but as it stands right now I get the message, "There were no files found."
How can I modify it so as to return files that I could open and scan, presumably using the OpenDatabase method?

Code: Select all

Sub FileSearch4XP()
Dim fls1 As FileSearch
Dim str1 As String
Dim int1 As Integer

Set fls1 = Application.FileSearch

'Set LookIn and SearchSubFolder properties
With fls1
    .NewSearch
    .LookIn = "c:\" 'Program Files\" & _
    '"Microsoft Office\Office11\Samples"
    .SearchSubFolders = True
End With

'Remove default PropertyTest for Office files
fls1.PropertyTests.Remove (1)

'Set a pair of PropertyTests to find all Web pages
'last modified on either 10/30/2000 or 10/31/2000
With fls1.PropertyTests
    .Add Name:="Files of Type", _
        Condition:=msoConditionFileTypeDatabases, _
        Connector:=msoConnectorOr
    .Add Name:="Last Modified", _
        Condition:=msoConditionAnytimeBetween, _
        Value:="1/1/2004", SecondValue:="12/31/2008"
End With

'Display PropertyTests
For int1 = 1 To fls1.PropertyTests.Count
    With Application.FileSearch.PropertyTests(int1)
    str1 = "This is the search criteria: " & vbCrLf & _
        "The name is: " & .Name & ". " & vbCrLf & _
        "The condition is: " & .Condition
    If .Value <> "" Then
        str1 = str1 & "." & vbCrLf & "The value is: " & .Value
        If .SecondValue <> "" Then
            str1 = str1 _
                & ". " & vbCrLf & "The second value is: " _
                & .SecondValue & ", and the connector is " _
                & .Connector
        End If
    End If
    MsgBox str1

    End With
Next int1

'Display result set from property tests
With fls1
'Execute the search
    If .Execute() > 0 Then
        MsgBox "There were " & .FoundFiles.Count & _
            " file(s) found."
'Display names of all found files
        For int1 = 1 To .FoundFiles.Count
            MsgBox .FoundFiles(int1)
        Next int1
    Else
'If no files found, say so
        MsgBox "There were no files found."
    End If
End With

End Sub 

[HansV]

I can't help with that - FileSearch doesn't work in Access 2010, and I don't have 2003 anymore.

Perhaps the following can be used as starting point. It should work for Access 2000 and later.

Code: Select all

' Set a reference to Microsoft Scripting Runtime
' If you don't run this code from Access, also set a reference to Access
' And to Microsoft Visual Basic for Applications Extensibility 5.3

' Top folder to search
Const strBaseFolder = "C:\Access"
' Text to look for
Const strSearch = "MyText"

Dim fso As Scripting.FileSystemObject
Dim app As Access.Application

Sub FileSearch()
    Dim fld As Scripting.Folder
    Set app = New Access.Application
    Set fso = New Scripting.FileSystemObject
    Set fld = fso.GetFolder(strBaseFolder)
    ProcessFolder fld
    Set fso = Nothing
    app.Quit acQuitSaveNone
    Set app = Nothing
End Sub

Sub ProcessFolder(ByVal fld As Scripting.Folder)
    Dim strPath As String
    Dim strFile As String
    Dim sfl As Scripting.Folder
    strPath = fld.Path
    If Right(strPath, 1) <> "\" Then
        strPath = strPath & "\"
    End If
    strFile = Dir(strPath & "*.mdb")
    Do While strFile <> ""
        ProcessDatabase strPath & strFile
        strFile = Dir
    Loop
    For Each sfl In fld.SubFolders
        ProcessFolder sfl
    Next sfl
End Sub

Sub ProcessDatabase(ByVal strPath As String)
    Dim vbc As VBIDE.VBComponent
    app.OpenCurrentDatabase strPath
    For Each vbc In app.VBE.ActiveVBProject.VBComponents
        ProcessModule vbc.CodeModule, strPath
    Next vbc
    app.CloseCurrentDatabase
End Sub

Sub ProcessModule(ByVal mdl As VBIDE.CodeModule, ByVal strPath As String)
    Dim lngStartLine As Long, lngEndLine As Long, lngStartCol As Long, lngEndCol As Long
    If mdl.Find(Target:=strSearch, StartLine:=lngStartLine, StartColumn:=lngStartCol, _
            EndLine:=lngEndLine, EndColumn:=lngEndCol, WholeWord:=True) Then
        MsgBox "Text found in module '" & mdl.Name & "' in database '" & _
            strPath & "'", vbInformation
    End If
End Sub

[grovelli]

That's just the usual greatness !!! Thank you Hans
I've added the reference to Microsoft Scripting Runtime as you can see from the jpg attached but when I debug it I get a message
Compile error: User-defined type not defined
with the line
Sub ProcessModule(ByVal mdl As VBIDE.CodeModule, ByVal strPath As String)
highlighted

[HansV]

Try setting a reference to Microsoft Visual Basic for Applications Extensibility 5.3

S205.png

[grovelli]

Fantastic, I'm using
Const strBaseFolder = "C:\"
to make it search throughout the hard disk and it's cranking away as I type...

[HansV]

That might take quite a while, since the code will then traverse ALL subfolders on the C: drive!

[grovelli]

I have no idea where I put that sub so I'm happy to wait

[ChrisGreaves]

I have hundreds of mdb files on my hard drive and would like to find a sub in their vba code whose name I remember.
Is there a utility that allows you to locate the file where the sub is stored?

I wrote such a utility back in the days of Woody's Lounge. I use it still.
It is called PROJE(ct) because it trawls a hard drive looking for project code in any MSOffice file (DOT, DOC, XLS, XLA, MDB, ...) and text files (such as Lounge Archives).

You are welcome to use a copy.

It works on the premise that a Line of (VBC) code is a string, terminated by a carriage-return.
A Procedure is a collection of lines, separated by a special character (which we define, could be an ASC(001) for all we care.
A Procedure is a collection of strings, and can therefore be seen as a string.
A Module is a collection of Procedures delimited by another special character; a Module is therefore a string.
A Project is a collection of modules; a Project is therefore a string.
Projects live in files (DOT, DOC, XLS ...) and so a file is a string.
Files live in folders, and so a folder is a string (in terms of lines of VBA code!).
Folders live on hard drives, so a hard drive is a string.
Hard drives live on computers, so a computer is a string.

With that premise in hand, PROJE wanders off scouring the computer (or hard drive, or folder, ...) looking for VBA code and accumulates all found code into a humungous string. (Humungous: I currently play with 20MB strings of code; I call them “Libraries“).

Once the String is accumulated and stored, it can be searched, and Proje uses a fast intrinsic VBA function called INSTR to do this.
Type in the search string, and Proje throws up a screen of hits.
You choose a hit, and the screen throws up the procedure showing where the search string occurs.


If all this sounds too much, the same utility can produce a table of procedures that lists each procedure by path, file, project, module, procedure name, public/private, sub/function and arguments.
You could just build this list and scan it (by eye or with Edit-Find).

Let me know .....

[HansV]

Basically, the entire universe is built of strings, so the universe is a string. String Theory...

[grovelli]

Thanks Chris, you guys are awesome, please do send me a copy of your utility. I hit a snag in the sense the vba code in some of the mdb's is password-protected by a password which(in my case) is always the same. Is there a way to have vba automatically fill in the password when it encounters (a request for) one and resume the scan automatically?

[HansV]

Change the line

Code: Select all

    app.OpenCurrentDatabase strPath

to

Code: Select all

    app.OpenCurrentDatabase strPath, , "MySecretPassword"

where MySecretPassword is the database password. Databases that do not have a password set will be opened without error.

[grovelli]

Thank you Hans, by looking at help for OpenCurrentDatabase, it seems to me its password argument refers to the password you set by using Tools, Security, Set Database Password while I'm talking about the password set to protect the vba code, am I wrong?

[HansV]

Yes, you are correct. So you can remove the password from OpenCurrentDatabase.
I know how to test whether the VB project is protected, but not how to open it if that is the case. So here is code that skips the database if the code is password-protected:

Code: Select all

Sub ProcessDatabase(ByVal strPath As String)
    Dim vbc As VBIDE.VBComponent
    app.OpenCurrentDatabase strPath
    With app.VBE.ActiveVBProject
        If .Protection = 0 Then
            For Each vbc In .VBComponents
                ProcessModule vbc.CodeModule, strPath
            Next vbc
        End If
    app.CloseCurrentDatabase
End Sub

[grovelli]

Thank you Hans; Chris, does your utility take into account that the files you open might have their vba project protected?

[grovelli]

Actually among the mdb's to be searched there are also a few protected by a database password which I don't know, how can you modify your ProcessDatabase sub so as to skip opening all mdb's protected by a database password?

[HansV]

Like this:

Code: Select all

Sub ProcessDatabase(ByVal strPath As String)
    Dim vbc As VBIDE.VBComponent
    On Error GoTo ExitHere
    app.OpenCurrentDatabase strPath
    With app.VBE.ActiveVBProject
        If .Protection = 0 Then
            For Each vbc In .VBComponents
                ProcessModule vbc.CodeModule, strPath
            Next vbc
        End If
ExitHere:
    On Error Resume Next
    app.CloseCurrentDatabase
End Sub

[grovelli]

Thank you Hans, the problem is that, even with this modification, any password-protected mdb still gets to be open thus the dialog box asking for the database password still comes up, isn't there a way to actually prevent such databases to be open?