Read Code Modules (2002)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Steve

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Steve

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote