CODEWITHSUNDEEP
excelvbamoduleobject-model
Coderman
Coderman

Reputation: 11

How do you analyze VBA code to extract pertinent items such as variable names, workbook references, and links to various drives?

so I've been working with a modified script to extract important information from a workbook with a ton of VBA modules that I need to breakdown and understand. How can I modify the script below to return the entire line where the keyword was found in the VBA module?

For example, if I set "findWhat" to "Workbooks.Open" -- how can I return the entire line in the VBA code so I know exactly what workbook the subroutine is opening (without having to scan the code manually?

Additionally, say I wanted to loop through 30+ keywords (Dim, Workbook, Drive, etc.) saved in an Excel range, how could I add an additional loop to test out additional keywords in the entire project?

I'd like to extract every reference to a variable, workbook, function, and subroutine within every module in the workbook -- and then return the entire line from the VBA code into a workbook for analysis. There are hundreds of modules here so this will hopefully save me a ton of time. Thank you.

Sub Find_Keywords()

Row = 1
Dim Vbc As VBComponent
Dim Line_Number, Start_Line, Line_Count As Long
Dim Line, Temp_Procedure_Name, Final_Procedure_Name, SubOrFun As String, Pk As vbext_ProcKind
Dim wb as Workbook
Dim Found as Boolean
Dim findWhat As String

Pk = vbext_pk_Proc
Set wb = Workbooks.Open("Module Workbook.xlsx")
findWhat ="Workbooks.Open"

  
    
    For Each Vbc In ThisWorkbook.VBProject.VBComponents
        Line_Number = 1
        Temp_Procedure_Name = ""
        
            For Line_Number = 1 To Vbc.CodeModule.CountOfLines
            Line = Vbc.CodeModule.Lines(Line_Number, 1)
            
            Final_Procedure_Name = Vbc.CodeModule.ProcOfLine(Line_Number, Pk)

                Temp_Procedure_Name = Final_Procedure_Name
        Found = Vbc.CodeModule.Find(Target:=findWhat,StartLine:=Vbc.CodeModule.ProcStart(Final_Procedure_Name,Pk), StartColumn:=1, EndLine:=Vbc.CodeModule.ProcCountLines(Find_Procedure_Name, Pk), EndColumn:=255, WholeWorld:=False,MatchCase:=False,PatternSearch:=False)

                If Found Then
                ThisWorkbook.Sheets(1).Cells(Row, 1).Value = Vbc.Name
                ThisWorkbook.Sheets(1).Cells(Row, 2).Value = Final_Procedure_Name
                ThisWorkbook.Sheets(1).Cells(Row, 3).Value = findWhat
             
                
                Row = Row + 1
                End If
            
                Next
        Next
End Sub

Upvotes: 1

Views: 119

Answers (0)

Related Questions