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.