Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
.CodeModule.Find AND PatternSearch (using Regular Expressions)
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.Fin d to include the search of these substrings. Any suggestions? Thanks in advance Paul Martin Melbourne, Australia |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
.CodeModule.Find AND PatternSearch (using Regular Expressions)
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" wrote in message oups.com... 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.Fin d to include the search of these substrings. Any suggestions? Thanks in advance Paul Martin Melbourne, Australia |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
.CodeModule.Find AND PatternSearch (using Regular Expressions)
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
.CodeModule.Find AND PatternSearch (using Regular Expressions)
For anyone interested, the solution to my problem was:
ThisWorkbook.VBProject.VBComponents("mControl").Co deModule.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 Paul Martin wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Get rid of with regular expressions | Excel Discussion (Misc queries) | |||
Regular expressions in VB | Excel Discussion (Misc queries) | |||
Regular expressions | Excel Programming | |||
Regular Expressions in VBA? | Excel Programming | |||
VBA and Regular expressions | Excel Programming |