MACROS - why doesn't this code list the modules as they are

[itr674]

I am using the the following code to list modeules and procedures. It is not listing "modules" alphabetically like that are listed in the VBE project????

Here is a sample of "module" names and how they are listed when I run the code>

Print1
Clock
HyperLinks
CB_Create
CB_HideShowDelete
OnAction
WS_GetNames<pre>Sub Macro_List_All_Subs_and_Functions3()

Dim MasterVBComp As VBComponents
Dim SingleVBComp As VBComponent
Dim VBCodeMod As CodeModule
Dim StartLine As Integer
Dim row As Long
Dim EndOfPageRow As Long
Dim col As Integer

Sheets("fil-Menu Maker-2").Select

'find the row for listing space
startRow = Range("A1:A100").Find("Macro Information").row + 1
row = startRow
EndOfPageRow = 70
col = 1

' Cells(startRow, 1).Resize(100, 6).Clear
' OR
With Range(Cells(startRow, 1), Cells(startRow + 100, 6))
.ClearContents
.Font.Bold = False
.IndentLevel = 0
End With

Set MasterVBComp = ThisWorkbook.VBProject.VBComponents
'module names first
For Each SingleVBComp In MasterVBComp
If SingleVBComp.Type = vbext_ct_StdModule Then
Cells(row, col) = SingleVBComp.Name
With Cells(row, col)
.Font.Bold = True
End With
row = row + 1
If row > EndOfPageRow Then
row = startRow
col = col + 2
End If

End If
'procedure names after its module name
If SingleVBComp.Type = vbext_ct_StdModule Then
Set VBCodeMod = SingleVBComp.CodeModule
StartLine = VBCodeMod.CountOfDeclarationLines + 1
Do Until StartLine >= VBCodeMod.CountOfLines
Cells(row, col) = VBCodeMod.ProcOfLine(StartLine, vbext_pk_Proc)
With Cells(row, col)
.IndentLevel = 1
End With
StartLine = StartLine + _
VBCodeMod.ProcCountLines(VBCodeMod.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)

row = row + 1
If row > EndOfPageRow Then
row = startRow
col = col + 2
End If

Loop
End If
Next
End Sub</pre>
This message was edited by em on 2002-11-04 08:17
This message was edited by em on 2003-02-01 00:03

 

[itr674]

Sending this back for another try ...

 

[Mudface]

Presumably, if you're using a For..Each..Next loop, the routine is picking up module names etc in the order (by date/ time) you added/ created them, rather than in alphabetical order. To get your listing in alphabetical order, just record yourself sorting the cells and add this to the end of your code.

 

[Andrew Poulsom]

It lists them in the order they appear in your Module - you are just looping round the text line by line.

Excel sorts the list when it displays it in the Object Browser.

 

[itr674]

Thanks guys--I'll try to make a sort routine later today. Have more questions coming...