Parsing UserForm and Code Modules in VBA

Some research into what's possible with VBA and code generation of JavaScript for a proof-of-concept project.

In order to parse a UserForm and VBA code modules, first enable Microsoft Visual Basic for Application Extensibility in the Tools / References feature:



For this example create a simple UserForm:



The Parse button should be wired up to call the Parse() subroutine in the Module1 code module. The entire source will be listed in the worksheet when this Parse routine is run.

Here's the code to place in Module1:

1:  Option Explicit  
2:    
3:  Sub Macro1()  
4:    Form1.Show  
5:  End Sub  
6:    
7:  Sub Parse()  
8:    Dim cmpComp As VBIDE.VBComponent  
9:    Dim cCont As Control  
10:    Dim lRow As Long  
11:    Dim lLine As Long  
12:      
13:    Sheets("Sheet1").Activate  
14:    ActiveSheet.UsedRange.ClearContents  
15:    
16:    lRow = 0  
17:    lRow = SetRow(lRow, "Show UserForm and VBA Code Modules")  
18:    lRow = SetRow(lRow, "Workbook " & ThisWorkbook.Name & ", Protected = " & ThisWorkbook.VBProject.Protection)  
19:    For Each cmpComp In ThisWorkbook.VBProject.VBComponents  
20:      lRow = SetRow(lRow, "Component Type = " & cmpComp.Type & ", Name = " & cmpComp.Name)  
21:      Select Case (cmpComp.Type)  
22:      Case vbext_ct_MSForm:      ' UserForm  
23:        
24:        ' Form1 is hardcoded here. If you include this code to traverse the  
25:        ' controls in the UserForm Code itself you can use "Me" instead of "Form1"  
26:        ' if you know of a way to parse controls in a UserForm using a string variable  
27:        ' or better yet, from another worksheet, please let me know  
28:        
29:        For Each cCont In Form1.Controls  ' Form1 here, could use "Me" in UserForm code  
30:          lRow = SetRow(lRow, " Control type = " & TypeName(cCont) & ", Name = " & cCont.Name)  
31:        Next cCont  
32:          
33:      Case vbext_ct_StdModule:    ' VBA code  
34:        lRow = SetRow(lRow, "Lines of code = " & cmpComp.CodeModule.CountOfLines)  
35:        For lLine = 1 To cmpComp.CodeModule.CountOfLines  
36:          lRow = SetRow(lRow, Format(lLine, "00#") & ": " & cmpComp.CodeModule.Lines(lLine, 1))  
37:        Next lLine  
38:      Case vbext_ct_Document:     ' worksheet  
39:        ' not UserForm or Code so other type of worksheet  
40:      End Select  
41:    Next cmpComp  
42:  End Sub  
43:    
44:  Function SetRow(lSheetRow As Long, sLine As String) As Long  
45:    SetRow = lSheetRow + 1  
46:    Cells(SetRow, 1).Value = sLine  
47:  End Function  
48:

Here is the partial output when running Macro1:


What I'd still like to be able to do:

  1. Reference the UserForm with a variable instead of hardcoding. I tried using UserForms(cmpComp.Name), but no go.
  2. Reference the UserForm from a different (loaded) workbook. I think the issue might be that the Visual Basic Editor needs to have the UserForm loaded in order to traverse the controls.