CODEWITHSUNDEEP
vbavbe
ScottC
ScottC

Reputation: 41

How can I find a word with a new line in the VBA editor using find and replace?

I would like to go through and find all of the "End" statements in my code but skipping all of the "End x" statements like "End If", "End Sub", "End function", etc.--Just the pure "End". My thought was to use pattern matching, but I am unsure of how to do that.

I already tried using "End\n" and "End[\n]".

VBE search box example

Does anyone know how to search for words that end in new lines?

Upvotes: 4

Views: 805

Answers (3)

JohnM
JohnM

Reputation: 3350

Tyler H's answer is right that the specific search requested by the user cannot be performed in the VBE Find tool. For information, when "Use Pattern Matching" is selected the VBE Find tool supports use of:

? - single character

* - zero or more characters (on the same line)

# - single digit (0 to 9)

[charlist] - any single character in charlist

[!charlist] - any single character not in charlist

... where charlist can be a range of characters (eg [A-Z]) but must be in order (eg [Z-A] is not valid), it can also include multiple ranges of characters (eg [A-BD-E] matches A, B, D or E). Also to match any of ?, * or # then enclose them in square brackets (eg [*] matches an asterisk).

This means the VBE Find tool performs very similarly (perhaps identically ... but I can't provide assurances, VB and VBA not being the same language) to the VB Like operator, for which documentation is here

The alternative (which will perform the specific search in the question) is to use the 'Find Text' tool in the VBE Add-In MZ-Tools - though note MZ-Tools is a paid-for tool ... please note I am NOT in any way associated with MZ-Tools or it's author. The search text to use in MZ-Tools for the specific search requested in the question is: end\r?$

Upvotes: 1

Dave
Dave

Reputation: 11

You can edit VBA code using VBA itself. To do that you will need to enable the "Microsoft Visual Basic for Applications Extensibility 5.3" library reference. I believe you will also need to go into the Excel > Options > Trust Center > Macro Settings section and check the "Trust access to the VBA project object model".

Once that's done you will be able to read & write to the VBA code modules for the components (module, form, class, sheet, & ThisWorkbook objects) in the VBComponents collection of the VBProject object for your workbook.

For example, you could do something like this:

Dim TmpComponent As VBIDE.VBComponent
For Each TmpComponent In ActiveWorkbook.VBProject.VBComponents 
    'Do stuff with the component here
Next

Or, if you only want to deal with a specific component you can just set it and deal with it like this:

Dim TmpProject As VBIDE.VBProject
Dim AllComponents As VBIDE.VBComponents
Dim TmpComponent As VBIDE.VBComponent
Dim TmpCodeModule As VBIDE.CodeModule
Set TmpProject = ActiveWorkbook.VBProject
Set AllComponents = TmpProject.VBComponents
Set TmpComponent = AllComponents("Component's Name")
'Do stuff with the component

Or simply:

Set TmpCodeModule = ActiveWorkbook.VBProject.VBComponents("Component's Name")

For each component which has code that you want to effect with your changes, what you then need to look at is the code module object for each component:

Dim TmpCodeModule As VBIDE.CodeModule
Set TmpCodeModule = TmpComponent.CodeModule

You can do that by dealing with all of code in the code module as a single string:

Dim OldCode As String
Dim NewCode As String
With TmpCodeModule
    'Get the lines of code from line 1 through to the last line
    OldCode = .Lines(1, .CountOfLines) 
    'Do the replacement
    NewCode = Replace(OldCode,"SearchText","ReplacementText")
    'Delete all of the code in the component
    .DeleteLines 1, .CountOfLines           
    'Add your new code into the module                                                                            
    .AddFromString NewCompCode
End With

Or, you can do it by looping through the code module's lines one at a time like this:

Dim i As Integer
With TmpCodeModule
    For i = 1 to .CountOfLines
        ' Do the replacement on this line
        .Lines(i) = Replace(.Lines(i),"OldText","NewText")
    Next
End With

IMPORTANT: Any code which alters VBA code should be kept in a separate module and you should take steps to prevent it from altering itself. For example, put any code like this in a module with "VBIDE" in the module's name and then, when you are looping through the VBComponents, do a check on each component's name and only process components that don't have names containing "VBIDE". E.g.:

For Each TmpComponent in ActiveWorkbook.VBProject.VBComponents 
    If Not TmpComponent.Name Like "*VBIDE*" Then
        ' Processing code here
    End If
Next

That should give you enough to get you started but you may also want to look into the "Microsoft VBScript Regular Expressions 5.5" library reference as well.

Upvotes: 1

TylerH
TylerH

Reputation: 21098

The "find" function in the VBA editor does not support this kind of parameter/functionality.

You will have to manually step through the results and skip the ones you don't want to skip, or manually modify the "End" instances you don't want to catch, then search & replace, and finally restore all the End instances back to what you want.

Upvotes: 1

Related Questions