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:
Here is the partial output when running Macro1:
What I'd still like to be able to do:
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:
- Reference the UserForm with a variable instead of hardcoding. I tried using UserForms(cmpComp.Name), but no go.
- 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.