.CodeModule.Find AND PatternSearch (using Regular Expressions)

[Paul Martin]

Hi all

I am looping through multiple files and scanning the code modules for
the use of "Calculate" OR ".PrintOut" in code.

I'm not sure how to use the Regular Expressions in the PatternSearch
argument of Workbooks(i).VBProject.VBComponents.CodeModule.Find to
include the search of these substrings. Any suggestions?

Thanks in advance

Paul Martin
Melbourne, Australia

 

[Bob Phillips]

Paul,

I don't think all the text in a code module is exposed in that way, but that
you would have to loop through all lines and check if any holds that
pattern. A simple example of such is

Dim oVBMod As Object
Dim oWb As Workbook
Dim iLine As Long

With ThisWorkbook.VBProject
For Each oVBMod In .VBComponents
Select Case oVBMod.Type
Case vbext_ct_StdModule:
For iLine = 1 To oVBMod.codemodule.CountOfLines
If oVBMod.codemodule.Lines(iLine, 1) Like
"*Calculate*" Or _
oVBMod.codemodule.Lines(iLine, 1) Like
"*.PrintOut*" Then
'do something
End If
Next iLine
Case vbext_ct_MSForm:
Case vbext_ct_ClassModule:
End Select
Next oVBMod
End

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

 

[Paul Martin]

Hi Bob

Thanks for your reply. It appears to me that your suggested code is a
long-winded way of coding something that Excel VBA already automates.
The Find method has a PatternSearch argument. My problem is that I
don't know the syntax for the pattern search. I've been looking
elsewhere on MSDN (haven't got the answer yet) and I was hoping someone
here knew the syntax.

Regards

Paul

 

[Paul Martin]

For anyone interested, the solution to my problem was:

ThisWorkbook.VBProject.VBComponents("mControl").CodeModule.Find("(Calculate|PrintOut)",
1, 1, -1, -1, True, , True)

This code will return True if "Calculate" OR "PrintOut" is found in
mControl module.

Information about Regular Expression Syntax can be found at:
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/ab0766e1-7037-45ed-aa23-706f58358c0e.asp>