.CodeModule.Find AND PatternSearch (using Regular Expressions)

  • Thread starter Thread starter Paul Martin
  • Start date Start date
P

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
 
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)
 
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
 
Back
Top