Page 218 - Excel Progamming Weekend Crash Course

k540629 ch15.qxd 9/2/03 9:34 AM Page 193

Session 15 — Find and Replace Operations 193

Listing 15-3 A program to perform text replacement throughout a workbook

Public Sub ReplaceAllInActiveWorkbook(target As String, _
replacement As String)
Dim ws As Worksheet
Dim r As Range
‘ Make sure there is a valid target.
If target = “” Then Exit Sub

For Each ws In ActiveWorkbook.Worksheets
Set r = ws.UsedRange
r.Replace target, replacement
Next

End Sub
Public Sub TestReplaceAll()

Dim target As String, replacement As String
target = InputBox(“Enter text to be replaced (blank to exit):”)
If target = “” Then Exit Sub
replacement = InputBox(“Enter replacement text:”)
ReplaceAllInActiveWorkbook target, replacement

End Sub

Listing 15-3 also includes a procedure named TestReplaceAll that you can execute to
test the program. This procedure uses the InputBox function to prompt the user for the
text to replace and the replacement text. Note that entering a blank for the target exits the
program without performing any replacements.
The InputBox function is covered in Session 9.

Cross-Ref
To test this program:
1. Enter the code from Listing 15-3 into a module in your VBA editor.
2. Open the workbook in which you want to make the replacements, or you can enter
data in a blank workbook for testing purposes.
3. Make sure the desired workbook (from step 2) is active.
4. In Excel, open the Macro dialog box (Alt+F8) and then run the TestReplaceAll
macro.