Results 1 to 7 of 7
  1. #1
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Read Code Modules (2002)

    I have 3 modules in my personal.xls. I am trying to write code to read all modules to tell me how many macros I have in each one. I can
    You know it's time to diet when you push away from the table and the table moves.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Read Code Modules (2002)

    You would need to set a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 Library Tools | References... in the Visual Basic Editor). This library allows you to manipulate code in code. However, it is much easier to download Rob Bovey's free Code Documentor.

  3. #3
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Read Code Modules (2002)

    Thank you Hans. I downloaded Rob's Code Documentor and that did the job. Just so that I can increase my experience in Excel, I will also take a look at the objects in the reference to Microsoft VB for Apps Extensability 5.3 and see which objects will I would use to get the same information. Thanks again.
    You know it's time to diet when you push away from the table and the table moves.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Read Code Modules (2002)

    You might also review CHip Pearson's discussion of working with the VB editor

    Steve

  5. #5
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Read Code Modules (2002)

    Hans, thanks for the example. It will certainly get me started in the right direction. And Rob Bovey's Code Documenter got me the information I needed without waiting for me to catch up on learning new objects. Thanks again!
    You know it's time to diet when you push away from the table and the table moves.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Read Code Modules (2002)

    Here is an example:

    Sub CountProcs()
    Dim wbk As Workbook
    Dim vbp As VBProject
    Dim vbc As VBComponent
    Dim cdm As CodeModule
    Dim n As Long
    Dim i As Long
    Dim s As String

    Set wbk = Workbooks("Personal.xls")
    Set vbp = wbk.VBProject
    For Each vbc In vbp.VBComponents
    If vbc.Type = vbext_ct_StdModule Then
    Set cdm = vbc.CodeModule
    n = 0
    For i = cdm.CountOfDeclarationLines + 1 To cdm.CountOfLines
    s = cdm.ProcOfLine(i, vbext_pk_Proc)
    If Len(s) > 0 Then
    n = n + 1
    i = i + cdm.ProcCountLines(s, vbext_pk_Proc)
    End If
    Next i
    Debug.Print cdm.Name, n
    End If
    Next vbc

    Set vbc = Nothing
    Set vbc = Nothing
    Set vbp = Nothing
    Set wbk = Nothing
    End Sub

  7. #7
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Read Code Modules (2002)

    Hi Steve,

    Thank you for that link. It has a wealth of good information for me to learn my way around VBE. I appreciate your assistance in getting me started.
    You know it's time to diet when you push away from the table and the table moves.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •