Solved List A Modules procedures in a combo box (1 Viewer)

[oxicottin]

I have a form (frm_AddEditMenuItems) with a combo box (cboRunCode) and I want to be able to create a list in the combo box of procedures in a module named (mod_SwitchboardMenu). Can this be done?

 

[MajP]

vBA extensibility allows you to read your code. Should be able to google an example

VBA Visual Basic Editor - Extensibility

VBA Reference - Microsoft Office Add-ins and Consultancy. One website for all Microsoft Office Users and Developers.

bettersolutions.com

 

[Uncle Gizmo]

One of the features of my Nifty s
Switchboard builder is the ability to run code, and to do this, it has a combo box which lists the functions in a module.

you can see what I mean in this video at time index 6:10

 

[MajP]

Good resource

Programming In The VBA Editor

www.cpearson.com

 

[oxicottin]

@MajP, everything I see is for excel VBA, when it comes to extensibility does that matter? Both links provided are excel VBA.

@Uncle Gizmo, Thanks for the link its almost exactly what I have, I just need to add the retrieval of procedures in my combo box.

 

[isladogs]

Here's another excellent article by the late Chip Pearson about VBE extensibility: http://www.cpearson.com/Excel/VBE.aspx
NOTE: The code works in Access as well as Excel with only minor changes

Attached is my module including code adapted from Chip's article and a few extra items.
One of the items is ListProcedures in a named module
Hope that helps

 

[Isaac]

As a generalization, (with exceptions, I'm sure), I wouldn't recommend getting into extensibility (i.e., code that modifies code)....Definitely not for a user-facing app.
Why do you want to do this?

 

[isladogs]

As a generalization, (with exceptions, I'm sure), I wouldn't recommend getting into extensibility (i.e., code that modifies code)....Definitely not for a user-facing app.
Why do you want to do this?

That's not what its purpose is.

 

[oxicottin]

isladogs, in the immediate window I type in ListProcedures("mod_SwitchboardMenu") and it shows the list of procedures in the window. Now how can I use that for a rowsource for a combobox on my form?

 

[MajP]

Public Function GetModules() As String
    
        On Error GoTo Err_Handler
        Dim VBAEditor As VBIDE.VBE
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Dim LineNum As Long
        Dim NumLines As Long
        Dim ProcName As String
        Dim ProcKind As VBIDE.vbext_ProcKind
        
        Set VBAEditor = Application.VBE
        Set VBProj = VBAEditor.ActiveVBProject
        
        For Each VBComp In VBProj.VBComponents
            Set CodeMod = VBComp.CodeModule
            If GetModules = "" Then
              GetModules = "'" & CodeMod.Name & "'"
            Else
              GetModules = GetModules & ";'" & CodeMod.Name & "'"
            End If
        Next VBComp
                
Exit_Handler:
        Exit Function
        
Err_Handler:
    MsgBox "Error " & Err.Number & " in ListAllStandardProcedures : " & Err.Description
    GoTo Exit_Handler
    
End Function
 
 
 Public Function GetProcedures(strModule As String) As String
       On Error GoTo Err_Handler
        Dim VBAEditor As VBIDE.VBE
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Dim LineNum As Long
        Dim NumLines As Long
        Dim ProcName As String
        Dim ProcKind As VBIDE.vbext_ProcKind
        
        Set VBAEditor = Application.VBE
        Set VBProj = VBAEditor.ActiveVBProject
        Set VBComp = VBProj.VBComponents(strModule)
        Set CodeMod = VBComp.CodeModule
      
      
        With CodeMod
            LineNum = .CountOfDeclarationLines + 1
            Do Until LineNum >= .CountOfLines
                ProcName = .ProcOfLine(LineNum, ProcKind)
                LineNum = .ProcStartLine(ProcName, ProcKind) + _
                        .ProcCountLines(ProcName, ProcKind) + 1
                If GetProcedures = "" Then
                  GetProcedures = "'" & ProcName & "'"
                Else
                  GetProcedures = GetProcedures & "; '" & ProcName & "'"
                End If
            Loop
        End With
        
Exit_Handler:
        Exit Function
        
Err_Handler:
    If Err = 9 Then
        MsgBox "Module does not exist", vbCritical, "No such module"
    Else
        MsgBox "Error " & Err.Number & " in ListProcedures : " & Err.Description
    End If
    GoTo Exit_Handler
    
End Function

 

[oxicottin]

Once again thank you!

 

[MajP]

FYI. A combobox also has an additem method. In the provided solution I concatenate and got a string delineated with ";". You could have also wrote the original procedure to call the additem method which would add each name one by one. Sometimes the additem is easier when you have many columns and it is a pain to make a long single string.