.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


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


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.




Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads