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

Chris Waller

Board Regular
Joined
Jan 18, 2009
Messages
183
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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.
VBA Code:
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
 
Upvote 0
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.
VBA Code:
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.
 
Upvote 0
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).
1636989077322.png


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.
 
Upvote 0

Forum statistics

Threads
1,223,910
Messages
6,175,316
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top