[AccessD] Collect information with vba code

    You are most welcome Marcel!

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

  ----- Original Message ----- 
  From: Marcel Vreuls 
  To: 'Access Developers discussion and problem solving' 
  Sent: Tuesday, April 05, 2005 02:02
  Subject: RE: [AccessD] Collect information with vba code


  TNXS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

  Just what I needed.

  Marcel
   
  -----Oorspronkelijk bericht-----
  Van: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] Namens A.D.Tejpal
  Verzonden: maandag 4 april 2005 5:08
  Aan: Access Developers discussion and problem solving
  Onderwerp: Re: [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