Extensibility

Extensibility

Searching For Text In A Module

The CodeModule object has a Find method that you can use to search for text within the code module.
The Find method accepts parameters that specify the range of lines and column to search.
To find the next occurrence of the text, you need to set the parameters to refer to the text following the found line and column.
The Find method returns True or False indicating whether the text was found.
This code will search all of the code in Module1 and print a Debug message for each found occurrence.

Sub SearchCodeModule() 
Dim VBProj As VBIDE.VBProject 
Dim VBComp As VBIDE.VBComponent 
Dim CodeMod As VBIDE.CodeModule 
Dim FindWhat As String 
Dim lStartLine As Long 
Dim lEndLine As Long 
Dim lStartColumn As Long 
Dim lEndColumn As Long 
Dim bFound As Boolean 
        
   Set VBProj = ActiveWorkbook.VBProject 
   Set VBComp = VBProj.VBComponents("Module1") 
   Set CodeMod = VBComp.CodeModule 
        
   FindWhat = "findthis" 
        
   With CodeMod 
      lStartLine = 1 
      lEndLine = .CountOfLines 
      lStartColumn = 1 
      lEndColumn = 255 
      bFound = .Find(Target:=FindWhat, _ 
                     StartLine:=lStartLine, _ 
                     StartColumn:=lStartColumn, _    
                     EndLine:=lEndLine, _ 
                     EndColumn:=lEndColumn, _  
                     WholeWord:=True, _ 
                     MatchCase:=False, _ 
                     PatternSearch:=False) 

      Do Until (bFound = False) 
         Debug.Print "Found at: Line: " & CStr(lStartLine) & " Column: " & CStr(lStartColumn) 
         lEndLine = .CountOfLines 
         lStartColumn = lEndColumn + 1 
         lEndColumn = 255 
         bFound = .Find(Target:=FindWhat, _ 
                        StartLine:=lStartLine, _ 
                        StartColumn:=lStartColumn, _    
                        EndLine:=lEndLine, _ 
                        EndColumn:=lEndColumn, _  
                        WholeWord:=True, _ 
                        MatchCase:=False, _ 
                        PatternSearch:=False) 
      Loop 
   End With 
End Sub