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 ByRef Long parameters. Upon input, these parameters specify the range of lines and column to search. On output, these values will point to the found text. To find the second and subsequent 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. The code below will search all of the code in Module1 and print a Debug message for each found occurrence. Note the values set with the SL, SC, EL, and EC variables. The code loops until the Found variable is False.

Sub SearchCodeModule() 
        Dim VBProj As VBIDE.VBProject 
        Dim VBComp As VBIDE.VBComponent 
        Dim CodeMod As VBIDE.CodeModule 
        Dim FindWhat As String 
        Dim SL As Long ' start line  
        Dim EL As Long ' end line  
        Dim SC As Long ' start column  
        Dim EC As Long ' end column  
        Dim Found As Boolean 
        
        Set VBProj = ActiveWorkbook.VBProject 
        Set VBComp = VBProj.VBComponents("Module1") 
        Set CodeMod = VBComp.CodeModule 
        
        FindWhat = "findthis" 
        
        With CodeMod 
            SL = 1 
            EL = .CountOfLines 
            SC = 1 
            EC = 255 
            Found = .Find(target:=FindWhat, StartLine:=SL, StartColumn:=SC, _ 
                EndLine:=EL, EndColumn:=EC, _ 
                wholeword:=True, MatchCase:=False, patternsearch:=False) 
            Do Until Found = False 
                Debug.Print "Found at: Line: " & CStr(SL) & " Column: " & CStr(SC) 
                EL = .CountOfLines 
                SC = EC + 1 
                EC = 255 
                Found = .Find(target:=FindWhat, StartLine:=SL, StartColumn:=SC, _ 
                    EndLine:=EL, EndColumn:=EC, _ 
                    wholeword:=True, MatchCase:=False, patternsearch:=False) 
            Loop 
        End With 
    End Sub