Use Personal Macro Workbook to Check for Phrases in Another Workbooks VBA

[Chris Waller]

Hi All,

I am hoping that someone can help me. I am trying to identify 4 phrases in the VBA of a large number of workbooks. I am currently doing this manually, but I'd rather automate it just because it's more accurate and quicker than I can do it manually. I am looking for the following Application.Workbooks.Add or Application.Workbooks.Open or Scripting.filesystemobject or ThisDocument.Path. If someone could give me an idea of how I could create some VBA that could check the VBA of a workbook that I open that would be great. I have to make a note of all the files I have checked and the results, so any help would be greatly appreciated.

 

[severynm]

Actually good question ... this will be useful for me too. In the VB editor, first make sure you have the reference for "Microsoft Visual Basic for Applications Extensibility" enabled. I created a small function that takes the workbook to search and string to find as parameters and returns true or false, depending on if the search string was found in the targeted module.

Public Sub main()
    Debug.Print FindStringInModuleCode(ThisWorkbook, "Application.Workbooks.Open")
End Sub


''==========================================================================================================================
'' Name:      CopyModuleToTargetWorkbook
'' Desc:      Searches the target module in the specified workbook for the provided string. Returns true if found, false if not.
'' Called by: N/A
'' Args:      targetWorkbook As Workbook ... Workbook containing the module to search
''            stringToFind As String ... string to search for in the module
'' Comments:  (1) https://docs.microsoft.com/en-us/previous-versions/visualstudio/visual-basic-6/aa443952(v=vs.60)
''            (2) Customize target module names as appropriate
''            (3) Must enable 'Microsoft Visual Basic for Applications Extensibility 5.3' reference
''==========================================================================================================================
Public Function FindStringInModuleCode(targetWorkbook As Workbook, stringToFind As String)
    Dim targetModule As VBIDE.CodeModule
    Set targetModule = targetWorkbook.VBProject.VBComponents("Sheet1").CodeModule 'Change to the relevant module name
    FindStringInModuleCode = targetModule.Find(stringToFind, 1, 1, 1, 1)
End Function

 

[Chris Waller]

Actually good question ... this will be useful for me too. In the VB editor, first make sure you have the reference for "Microsoft Visual Basic for Applications Extensibility" enabled. I created a small function that takes the workbook to search and string to find as parameters and returns true or false, depending on if the search string was found in the targeted module.

Public Sub main()
    Debug.Print FindStringInModuleCode(ThisWorkbook, "Application.Workbooks.Open")
End Sub


''==========================================================================================================================
'' Name:      CopyModuleToTargetWorkbook
'' Desc:      Searches the target module in the specified workbook for the provided string. Returns true if found, false if not.
'' Called by: N/A
'' Args:      targetWorkbook As Workbook ... Workbook containing the module to search
''            stringToFind As String ... string to search for in the module
'' Comments:  (1) https://docs.microsoft.com/en-us/previous-versions/visualstudio/visual-basic-6/aa443952(v=vs.60)
''            (2) Customize target module names as appropriate
''            (3) Must enable 'Microsoft Visual Basic for Applications Extensibility 5.3' reference
''==========================================================================================================================
Public Function FindStringInModuleCode(targetWorkbook As Workbook, stringToFind As String)
    Dim targetModule As VBIDE.CodeModule
    Set targetModule = targetWorkbook.VBProject.VBComponents("Sheet1").CodeModule 'Change to the relevant module name
    FindStringInModuleCode = targetModule.Find(stringToFind, 1, 1, 1, 1)
End Function

Severynm:

Thanks for your help above. I have only just managed to start to look at the VBA. The PC I use for work contains Office 365 and my home computer has an older version of office and this being the case I cannot seem to find the "Microsoft Visual Basic for Applications Extensibility". I will have to take a closer look later on.

I am currently doing this manually by opeing the Edit and Find button and pasting in the item I am looking for. I thought VBA might be quicker, and correct me if I am wrong, but I will still have to change the code for the modules, worksheets etc. So, whilest I am grateful for the help, It's my lack of understanding that it still appears to be quicker for me to do this manually.

Tahnks again.

 

[severynm]

No problem. In the code editor, if you go Tools → References, do you see it in the list and are you able to enable it? This is not any kind of new functionality which might be part of a new office version (I double checked on a machine running office 2016 to be sure).

I thought VBA might be quicker, and correct me if I am wrong, but I will still have to change the code for the modules, worksheets etc. So, whilest I am grateful for the help, It's my lack of understanding that it still appears to be quicker for me to do this manually.

The string above called "Sheet1" can actually be either the name of a worksheet or an actual code module. It examines the CodeModule object of what you pass to it. Sorry if that wasn't clear.

And yes this is a good question depending on how many files you have to examine. If its a half dozen files, by hand would probably be better especially if you're learning.