[AccessD] Collect information with vba code

Marcel,

    You should be able to obtain the required information directly from the modules collection.

    Sample sub-routine given below, will list all lines (along with names of respective modules) satisfying your criteria, in the immediate window. For your specific case, the following statement, calling this subroutine, will do the needful.
    P_GetMatchingCodeLines "MsgBox"

    Important - You have to set reference to Microsoft Visual Basic For Applications Extensibility 5.3

A.D.Tejpal
--------------

===================================
Public Sub P_GetMatchingCodeLines(ByVal _
                                            MatchString As String)
    ' This needs reference to Microsoft Visual Basic
    ' For Applications Extensibility 5.3
    Dim mdc As Modules, md As Module
    Dim Cnt As Long, Lct As Long, Txt As String
    Dim DecLines As Long, ProcStLine As Long
    Dim ModName As String, PrName As String
    Dim ProcLine As Long
    
    Set mdc = Application.Modules
    ' Loop through all the modules in this collection
    For Cnt = 0 To mdc.Count - 1
        Set md = mdc(Cnt)
        ' Get name of module
        ModName = md.Name
        ' Get number of lines in declaration portion
        DecLines = md.CountOfDeclarationLines
        ' Loop through all the lines in this module
        For Lct = 1 To md.CountOfLines
            ' Get the contents of line Lct
            Txt = md.Lines(Lct, 1)
            If InStr(Txt, MatchString) > 0 Then
                If md.Type = acStandardModule _
                                            Or InStr(ModName, _
                                            "Form_") > 0 Then    ' (A)
                    If Lct > DecLines Then                      ' (B)
                        ' Get name of procedure
                        ' (in which, line Lct is contained)
                        PrName = md.ProcOfLine(Lct, _
                                            vbext_pk_Proc)
                        ' Get starting Line No for this procedure
                        ProcStLine = md.ProcBodyLine(PrName, _
                                            vbext_pk_Proc)        ' (C)
                        ' Compute Line number as counted
                        ' from start of procedure
                        ProcLine = Lct - ProcStLine + 1
                    Else
                        PrName = "Declaration Sec"
                        ProcStLine = 1
                        ProcLine = Lct
                    End If
                Else
                    PrName = "Class"
                    ProcStLine = 1
                    ProcLine = Lct
                End If
                Debug.Print "Mod - " & ModName & ",  Proc - " & _
                            PrName & ",  Line(ProcLine) - " & _
                            Lct & "(" & ProcLine & ")"
                Debug.Print Space(6) & Trim(Txt)
            End If
        Next
    Next
    
    Set md = Nothing
    Set mdc = Nothing
    
    ' Note - (A) & (B) are needed so as to ensure that there is
    '              no mismatch of argument regarding assumed type
    '              of procedure ()
    '              (C) The value returned by md.ProcBodyLine() is
    '              more dependable as it gives the line number where
    '              the procedure actually starts. On the other hand,
    '              md.ProcStartLine() returns the first line after end
    '              of prev procedure. This can be misleading when
    '              there are one or more blank lines between
    '              adjacent procedures.
End Sub
===================================

  ----- Original Message ----- 
  From: Marcel Vreuls 
  To: 'Access Developers discussion and problem solving' 
  Sent: Friday, April 01, 2005 14:25
  Subject: [AccessD] Collect information with vba code


  Dear Group,

  Just a quick question. Does someone know how I can collect all rows in vbacode where the function msgbox is stated and put this in the debug window or just drop in a file.

  Thanks in advance,

  Marcel