Page 216 - Excel Progamming Weekend Crash Course

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

Session 15 — Find and Replace Operations 191

CountStringsInRange = count

End Function

Public Sub TestCountStrings()

Dim count As Long
Dim target As String
target = “Sales”
count = CountStringsInRange(Selection, target)
MsgBox “The string ‘“ & target & “‘ was found “ & count & “ times”

End Sub
To try out this program, follow these steps.
1. Add the code from the listing to a module in your VBA editor.
2. Edit the TestCountStrings procedure so the variable named target contains the
text for which you want to search.
3. Switch to Excel and then enter some text data in a section of the worksheet (or open
a worksheet that already contains data).
4. Use the mouse or keyboard to select the range you want to search.
5. Press Alt+F8 to open the Macro dialog box.
6. Select the TestCountStrings macro and then click Run.
The program performs its task and displays the results in a dialog box, as shown in
Figure 15-1.

Figure 15-1 The results displayed by the function in Listing 15-2

It would be a good programming exercise for you to modify the
CountStringsInRange function so it performs a case-sensitive search.
Note