Searching for a Function

[BigJimSlade]

Hi, Big Jim here:

Is there a way to reference the name of each function stored a module? I would like to place the name of the functions in a list box. Is this possible from code?

Thanks in advance!

Big Jim

 

[The_Doc_Man]

If this is going to be done at all from a list box, I think it implies that the list of entry points must exist in a table first. So the question then becomes, how to get the names in a table. I can answer this question, but you might not like the answer. At least for AC97, this next part is true. I don't know (but doubt) that AC2K is any better than this.

There is no visible "hidden" table that lists module entry point names. MSysModules2 lists the names of objects that contain modules - but the modules themselves are stored as long binary objects. (I.e. compiled objects.) MSysModule2 does not list entry points so you cannot extract from there. The only way to get this is to write some code to find it and to then populate your table. The bad news is, this code is tough to write. The worse news is, Access isn't very efficient when doing some of things I will describe in a moment. The good news is, you only have to run it after you edit a general or class module.

General modules are part of the database in a collection called

CurrentDB.Containers!Modules.Documents

Each document in that collection is an object corresponding to one of the icons you see in your Modules pane of the DB window. You must open the module to see its contents. (DoCmd.OpenModule {module-name-to-be-opened})

But there is also such a thing as a Class module. Each form is part of the database collection called

CurrentDB.Containers!Forms.Documents

For reports, this is

CurrentDB.Containers!Reports.Documents

You must OPEN a form or report to see its contents. When opened, the form or report has a property called .HasModule, which is True/False. If you have a module for a given form or report, it is {opened-form-name}.Module - and you guessed it, you have to open that module to see its contents.

OK, now that we have open modules, you have to accept an ugliness - you have to READ the module to find its entry points.

Each module has a collection of lines, as

....module.Lines

which can be stepped through by enumerating the line number using ....module.Lines syntax. The number of lines in the module is

....module.CountOfLines

So now, all that is left is a little loop to step through each line from 1 to CountOfLines for that module. In each line, you will do two InStr searches, trying to find declarations such as Function or Procedure. The text standing between the end of either of those keywords and the next open parenthesis is the entry point name. And because of the way you had to find it, you even know whether the entry is a function or a procedure.

So in order to populate this list you seek, you would have to open a recordset to your table that holds your entry point names.

Then you would step through all documents that could imply or express modules. This includes general modules and the class modules for all forms and reports.

For each module, you would step through all lines looking for the keywords you wanted.

For each keyword match, you would parse out the line to find the entry point name.

And for each entry point name, you would add a new record to the recordset. It is up to you as to how much extra info you would store during that update operation.

When you are done, your table lists all entry point names. You could, if you wished, have also stored the name of the place where it was found, what kind of entry it was, and the number of lines in the implied procedure. (That is a pseudo-property function you can look up, ProcCountLines.)

Now, what I told you is the Brute Force method. There is another way to do this that is SLIGHTLY better.

You could ALSO get to the point of the module being open. Then you could look at the modules count of lines. Then look a property .CountOfDeclarationLines to see how many declaration lines there are. Then step to one line past that count and use the .ProcOfLine pseudo-property function to find the name of the procedure associated with that line. Then find the pseudo-property function .ProcCountLines of that named procedure. Step through the module by adding counts to get to the next line after each procedure you found this way. Eventually you will add enough lines to "fall off the end" of the module.

Doing it this way, you will not find out whether the entry is a function or sub procedure. But you can still find the number of lines in it and its starting line, because the pseudo-property functions give you that much once you have the procedure name.

 

[BigJimSlade]

Doc_Man!

Big Jim is impressed that you can type as fast as you did. I think I was onto something similiar to your second set of thoughts. What do you think of this:

Dim ff As Module
Dim fm As Modules
Dim rstMacros As Recordset
Dim rstMac
Dim strLine As String
Dim strFunction As String
Dim varFound As Variant
Dim varFoundDeclare As Variant
Dim varFoundEnd As Variant
Dim varFoundExit As Variant
Dim varParen As Variant
Dim varFoundDim As Variant
Dim varFoundEq As Variant

Dim I As Variant
DoCmd.SetWarnings False
DoCmd.RunSQL ("DELETE * FROM Macro_Functions;")
DoCmd.SetWarnings True

Set rstMacros = CurrentDb.OpenRecordset("SELECT Name FROM MSysObjects WHERE Type = -32761;")
Do Until rstMacros.EOF
DoCmd.OpenModule (rstMacros!Name)
Set ff = Modules(rstMacros!Name)
For I = 1 To ff.CountOfLines
If ff.Name <> "Form_MacroGenerator" Then
strLine = ff.Lines(I, 1)
varFound = InStr(1, strLine, "Function ")
varFoundDeclare = InStr(1, strLine, "Declare")
varFoundExit = InStr(1, strLine, "Exit")
varFoundEnd = InStr(1, strLine, "End")
varFoundDim = InStr(1, strLine, "Dim")
varFoundEq = InStr(1, strLine, "=")

If varFound > 0 And varFoundDeclare = 0 And varFoundExit = 0 And varFoundEnd = 0 And varFoundDim = 0 And varFoundEq = 0 Then
varParen = InStr(1, strLine, "(")
strFunction = Mid(strLine, (varFound + 9), (varParen - (varFound + 9)))
DoCmd.SetWarnings False
DoCmd.RunSQL ("INSERT INTO Macro_Functions (Functions) VALUES ('" & strFunction & "');")
DoCmd.SetWarnings True
End If
End If
Next I
DoCmd.Close acModule, rstMacros!Name
rstMacros.MoveNext
Loop

We got this to work, although there is obviously some messy cleanup work involved with making sure it is an actual function I am selecting. Any thoughts on this? Your ideas were a great help, so thank you very much!

Big Jim

 

[raskew]

This will give you all standard modules and their respective procedures. If you need class modules, please post back.

Function GetModules()
    'Creates tblModules and populates it with all module and procedure names
    'contained in the current database.
    'Basis of this function:  Search help for ProcOfLines Property
    Dim mdl As Module, db As DATABASE, rs As Recordset
    Dim lngCount As Long, lngCountDecl As Long, lngI As Long
    Dim strProcName As String, astrProcNames() As String
    Dim intI As Integer, strMsg As String, tname As String
    Dim lngR As Long, strSQL As String, found As Boolean, test As String
    Dim rs2 As Recordset, n As Long, i As Long
    Dim strModuleName As String
    
    strSQL = "SELECT MSysObjects.Name, MSysObjects.Type" _
           & " FROM MSysObjects" _
           & " WHERE (((MSysObjects.Type) = -32761))" _
           & " ORDER BY MSysObjects.Name;"


   ' Open specified Module object.
   Set db = CurrentDb
   On Error Resume Next
   Set rs = db.OpenRecordset(strSQL)
   If Not rs.BOF Then
   ' Get number of records in recordset
      rs.MoveLast
      n = rs.RecordCount
      rs.MoveFirst
   End If
   tname = "tblMadules"
   'Does table "tblMadules" exist?  If true, delete it;
   found = False
   test = db.TableDefs(tname).Name
   If Err <> 3265 Then
      found = True
      docmd.DeleteObject acTable, tname
   End If
   'Create new tblMadules

   db.Execute "CREATE TABLE tblMadules(ObjectID LONG, Type TEXT (55), Module TEXT (55), Procedure TEXT (55));"
   Set rs2 = db.OpenRecordset("tblMadules")
   docmd.Echo False
   For i = 0 To n - 1
      strModuleName = rs!Name
      docmd.OpenModule strModuleName
      ' Return reference to Module object.
      Set mdl = Modules(strModuleName)
      ' Count lines in module.
      lngCount = mdl.CountOfLines
      ' Count lines in Declaration section in module.
      lngCountDecl = mdl.CountOfDeclarationLines
      ' Determine name of first procedure.
      strProcName = mdl.ProcOfLine(lngCountDecl + 1, lngR)
      ' Initialize counter variable.
      intI = 0
      ' Redimension array.
      ReDim Preserve astrProcNames(intI)
      ' Store name of first procedure in array.
      astrProcNames(intI) = strProcName
      rs2.AddNew
      rs2!ObjectID = 5
      rs2!Type = "Module"
      rs2!Module = strModuleName
      rs2!Procedure = strProcName
      rs2.Update
      ' Determine procedure name for each line after declarations.
      For lngI = lngCountDecl + 1 To lngCount
        ' Compare procedure name with ProcOfLine property value.

         If strProcName <> mdl.ProcOfLine(lngI, lngR) Then
            ' Increment counter.
            intI = intI + 1
            strProcName = mdl.ProcOfLine(lngI, lngR)
            ReDim Preserve astrProcNames(intI)
            ' Assign unique procedure names to array.
            astrProcNames(intI) = strProcName
            rs2.AddNew
            rs2!ObjectID = 5
            rs2!Type = "Module"
            rs2!Module = strModuleName
            rs2!Procedure = strProcName
            rs2.Update
        End If
        
        docmd.Close acModule, strModuleName, acSaveYes
        
    Next lngI

    rs.MoveNext
    Next i

        docmd.Echo True
        docmd.Close acModule, strModuleName, acSaveYes
    
    rs2.Close
    rs.Close
            docmd.Close acModule, strModuleName, acSaveYes

    tname = "tblModules"
   'Does table "tblModules" exist?  If true, delete it;
    docmd.SetWarnings False
    found = False
   test = db.TableDefs(tname).Name
   If Err <> 3265 Then
      found = True
      docmd.DeleteObject acTable, tname
   End If
   'Create new tblModules
   strSQL = "SELECT ObjectID, Type, Module, Procedure INTO tblModules " _
          & "FROM tblMadules " _
          & "ORDER BY tblMadules.Module, tblMadules.Procedure;"
   docmd.RunSQL strSQL
   docmd.DeleteObject acTable, "tblMadules"
   docmd.SetWarnings True
    Set db = Nothing
End Function

 

[BigJimSlade]

Thanks raskew!

That works much better than my ad-hoc approach! Big Jim appreciates your time on this matter. You too Doc Man!

Thanks,

Big Jim!