Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox with available procedures
Hello!
Is there a way to list all the procedures in a specific module as Items in a Listbox? Thanx in advance, Albert C |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox with available procedures
Chip Pearson has code at this link for listing all the procedures in a module:
http://www.cpearson.com/excel/vbe.aspx Should be easy to load a listbox from the output. Hope this helps, Hutch "Albert" wrote: Hello! Is there a way to list all the procedures in a specific module as Items in a Listbox? Thanx in advance, Albert C |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox with available procedures
Albert
Chip Pearson's site is great on this and many related subjects. http://www.cpearson.com/excel/vbe.aspx I tweaked his code very slightly to do what you want: Sub ListProcedures() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Dim LineNum As Long Dim NumLines As Long Dim WS As Worksheet Dim Rng As Range Dim ProcName As String Dim ProcKind As VBIDE.vbext_ProcKind Set VBProj = ActiveWorkbook.VBProject Set VBComp = VBProj.VBComponents("Module1") Set CodeMod = VBComp.CodeModule Set WS = ActiveWorkbook.Worksheets("Sheet1") Set Rng = WS.Range("A1") With CodeMod LineNum = .CountOfDeclarationLines + 1 ProcName = .ProcOfLine(LineNum, ProcKind) Do Until LineNum = .CountOfLines ' Rng(1, 1).Value = ProcName ' Rng(1, 2).Value = ProcKindString(ProcKind) Sheet1.ListBox1.AddItem ProcName Set Rng = Rng(2, 1) LineNum = LineNum + .ProcCountLines(ProcName, ProcKind) + 1 ProcName = .ProcOfLine(LineNum, ProcKind) Loop End With End Sub Function ProcKindString(ProcKind As VBIDE.vbext_ProcKind) As String Select Case ProcKind Case vbext_pk_Get ProcKindString = "Property Get" Case vbext_pk_Let ProcKindString = "Property Let" Case vbext_pk_Set ProcKindString = "Property Set" Case vbext_pk_Proc ProcKindString = "Sub Or Function" Case Else ProcKindString = "Unknown Type: " & CStr(ProcKind) End Select End Function You need to set a reference to the Microsoft Visual Basic for App Extensibility 5.3, and have a listbox1 on sheet1. His original code had the component name and type written to the spreadsheet. I don't know how some of his stuff works, but it sure does. Ken On Sep 19, 1:33*pm, Albert wrote: Hello! Is there a way to list all the procedures in a specific module as Items in a Listbox? Thanx in advance, Albert C |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox with available procedures
Thank you both!
Worked great!!! "Albert" wrote: Hello! Is there a way to list all the procedures in a specific module as Items in a Listbox? Thanx in advance, Albert C |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
userform listbox cannot get listbox.value to transfer back to main sub | Excel Programming | |||
VBA: Creating listbox similar to the one in Pivot table (Listbox+Checkbox) | Excel Programming | |||
ListBox Procedures | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
Is refreshing listbox rowsource in listbox click event possible? | Excel Programming |