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

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
Thanks guys--I'll try to make a sort routine later today. Have more questions coming...
 

Forum statistics

Threads
1,143,640
Messages
5,719,987
Members
422,257
Latest member
Calion

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top