VBA userform with function list via VBE

[Dimasian]

Hello out there.
Can someone post a code example on how to show VBE userform with multipage via VBA, containing list of functions in current project, each page for each module correspondingly.
I know how to create userform via VBE, extract module and function names to arrays and put them in multipage, so in editor view userform shows all correct, but when i load it to Excel view via VBA (like VBA.userforms.Add(ufName).Show) - userform and multipage with names of modules in each page are there, but the function names aren't.

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

If you will post the relevant parts of your existing code, it will be easier to suggest modifications needed to have the function names displayed.

I know how to create userform via VBE, extract module and function names to arrays and put them in multipage, so in editor view userform shows all correct,...

How are you putting the function names in the Userform controls in editor view (design time)? Are you doing that manually or by executing code?

 

[Dimasian]

How are you putting the function names in the Userform controls in editor view (design time)? Are you doing that manually or by executing code?

Hello, here's the code. I haven't find a way to attach files, so just textways here:

This is general step to run the userform created via VBE, with insterted multipage and function names.

Sub RunUserForm()
Dim uf As VBComponent, wbName As String, ufName As String, ufCaption As String
Dim arrModules As Variant, arrFunctions As Variant
Dim i As Integer, j As Integer
Dim uf1 As UserForm


  wbName = "myAddIn.xla"
  ufName = "uf_FunctionSelection"
  ufCaption = "Function List"
  arrModules = ListModules
  
  ReDim arrFunctions(LBound(arrModules) To UBound(arrModules))
  For i = LBound(arrFunctions) To UBound(arrFunctions)
      arrFunctions(i) = ListFunctionsInModule(arrModules(i))
  Next i
  BuildUserForm wbName, ufName, ufCaption, arrModules, arrFunctions


  VBA.userforms.Add(ufName).Show
End Sub

This is creating userform:

Sub BuildUserForm(wbName As String, ufName As String, ufCaption As String, arrModules As Variant, arrFunctions As Variant)
Dim uf As VBComponent, myMP As Variant, myLB As Variant
Dim i As Integer, j As Integer
Dim newPage As Page, element As Object, test As UserForm


   Set uf = Workbooks(wbName).VBProject.VBComponents.Add(vbext_ct_MSForm)
      With uf
         .Properties("Height") = 600
         .Properties("Width") = 600
         .Properties("Name") = ufName
         .Properties("Caption") = ufCaption
      End With


Set myMP = uf.Designer.Controls.Add("Forms.MultiPage.1")
With myMP
  .Left = 20
  .Top = 20
  .Height = 500
  .Width = 300
End With
      
For i = LBound(arrModules) To UBound(arrModules)
  If i = 1 Or i = 2 Then
    myMP.Pages(i - 1).Caption = arrModules(i).Name
  ElseIf i - myMP.Pages.count > 0 Then
    Set newPage = myMP.Pages.Add
    newPage.Caption = arrModules(i).Name
  End If


  Set myLB = myMP.Pages(i - 1).Controls.Add("Forms.ListBox.1")
  With myLB
    .Top = 20
    .Left = 20
    .Height = 400
    .Width = 200
    .Name = "myLB" & i
  End With
  On Error Resume Next
    For j = LBound(arrFunctions(i)) To UBound(arrFunctions(i))
      myLB.AddItem arrFunctions(i)(j)
    Next j
  On Error GoTo 0
Next i


Debug.Print "form is created " & Now()
End Sub

Listing modules via VBE:

Function ListModules()    Dim VBProj As VBIDE.VBProject, VBComp As VBIDE.VBComponent, arr() As Variant, i As Integer
    
    Set VBProj = Workbooks("myAddIn.xla").VBProject
    i = 1
    For Each VBComp In VBProj.VBComponents
      If VBComp.Type = vbext_ct_StdModule Then
        arr = RedimPreserveOrSizeTo1stElement(arr)
        Set arr(i) = VBComp
        i = i + 1
      End If
    Next VBComp
    ListModules = arr
End Function

Listing functions in each module via VBE:

  Function ListFunctionsInModule(VBComp As Variant)        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
        Dim res() As Variant, cc As Integer, i As Integer
        
        Set CodeMod = VBComp.CodeModule


        With CodeMod
            LineNum = .CountOfDeclarationLines + 1
            Do Until LineNum >= .CountOfLines
                ProcName = .ProcOfLine(LineNum, ProcKind)
'                Debug.Print ProcName
'                Debug.Print ProcKindString(ProcKind)
                LineNum = .ProcStartLine(ProcName, ProcKind) + _
                        .ProcCountLines(ProcName, ProcKind) + 1
                res = ArrayProcedures.RedimPreserveOrSizeTo1stElement(res)
                res(UBound(res)) = .ProcOfLine(LineNum, ProcKind)
            Loop
        End With
        ListFunctionsInModule = res
    End Function

How it is in editor view-before calling to Excel view:
-couldn't insert image from harddrive...how to make url from it?

 

[Jerry Sullivan]

When Userforms are manually created in the VB Editor the designer can't populate ListBox objects with items.
It's interesting that when creating a Userform dynamically using automation, VBA allows ListBoxes to be populated, even though a specific instance of the Userform VBComponent hasn't been created.

I believe the reason that the ListBox items that you've been able to see in the VB Editor don't appear when the userform is shown is because this line creates a new instance of the custom userform object you've defined.

  VBA.userforms.Add(ufName).Show

The population of the ListBoxes which you performed on the VBComponent aren't transferred over to the new instance.
You'll notice that when the RunUserForm Sub has ended, the uf_FunctionSelection object no longer has the function names populated in the ListBoxes. This is further evidence that ListBox items probably can't be stored in the default instance of the new form and passed on to new instances.

One fix would be to modify your code so that it populates the ListBoxes after a new instance of the userform is created.

Stepping back to look at the big picture of your application, is there any reason why you need to create the userform dynamically?

Perhaps you could simplify things by having a userform template in the Add-In where you currently have this code.
The multipage object and its ListBoxes could be added to a new instance of that userform at runtime. One benefit of that is it would allow you to store code for the userform's properties and methods at design time instead of having to add that code dynamically, or reference the form's object's externally.

 

[Norie]

Why are you creating a userform using code?

 

[Jerry Sullivan]

Why are you creating a userform using code?

That's what I asked! (although I used a lot of words to ask it)

 

[Norie]

Aye, I know, sorry.

 

[Dimasian]

That's what I asked! (although I used a lot of words to ask it)

Jerry, Norie, thanks for your reply.
I will check both ways - creating userform manually with dynamically filling it with function names and try to fill list boxes after new instance is created when userform is created on the fly.
I never thought(knew) i can have a template of userform and populate it dynamically.
I will revert with what i'll get from this soon.

P.S.

Originally Posted by Norie
Why are you creating a userform using code?

Well, if you think abstract about it, - if you can do it on the fly, why wouldn't you? First i had userform created manually as well as checkboxes with function names created manually, and everything else was manual)) Then i thought - what the hell, if i call it a programming i should automate everything, even the userform creation...