none
Search all code in a project to find all specific kwywords words using VBA to automate RRS feed

  • Question

  • I would like to write a routine that searches all the code in a project (all modules, worksheets...) and find specific keywords.

    For example I would like to know if the KILL statement is being used anywhere in the project.

    I know I can do a find or a replace from within the Edit menu, but I want to be able to automate it.

    Thanks.

    Tuesday, February 20, 2018 1:55 PM

Answers

  • Try this:

    Sub SearchCode()
        Dim vbp As Object ' VBProject
        Dim vbc As Object ' VBComponent
        Dim mdl As Object ' CodeModule
        Dim f As Boolean
        Set vbp = ActiveWorkbook.VBProject
        For Each vbc In vbp.VBComponents
            Set mdl = vbc.CodeModule
            If mdl.Find("Target:=Kill", StartLine:=1, StartColumn:=1, _
                    EndLine:=-1, EndColumn:=-1, WholeWord:=True) Then
                f = True
                Exit For
            End If
        Next vbc
        If f Then
            MsgBox "'Kill' was found in " & vbc.Name
        Else
            MsgBox "'Kill' was not found in any module"
        End If
    End Sub

    You must allow programmatic access to the Visual Basic project, otherwise you'll get an error.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by James N San Wednesday, February 21, 2018 12:39 PM
    Tuesday, February 20, 2018 3:28 PM

All replies

  • Try this:

    Sub SearchCode()
        Dim vbp As Object ' VBProject
        Dim vbc As Object ' VBComponent
        Dim mdl As Object ' CodeModule
        Dim f As Boolean
        Set vbp = ActiveWorkbook.VBProject
        For Each vbc In vbp.VBComponents
            Set mdl = vbc.CodeModule
            If mdl.Find("Target:=Kill", StartLine:=1, StartColumn:=1, _
                    EndLine:=-1, EndColumn:=-1, WholeWord:=True) Then
                f = True
                Exit For
            End If
        Next vbc
        If f Then
            MsgBox "'Kill' was found in " & vbc.Name
        Else
            MsgBox "'Kill' was not found in any module"
        End If
    End Sub

    You must allow programmatic access to the Visual Basic project, otherwise you'll get an error.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by James N San Wednesday, February 21, 2018 12:39 PM
    Tuesday, February 20, 2018 3:28 PM
  • GrepWin does this.

    https://sourceforge.net/p/grepwin/wiki/commandlineparameters/

    Tuesday, February 20, 2018 4:02 PM
  • This worked great. Thank!
    Wednesday, February 21, 2018 12:39 PM