Microsoft Office 2000/Visual Basic Programmer's Guide

Wildcard Search and Replace

The Replace function greatly simplifies string search-and-replace operations, but it doesn't enable you to perform wildcard searches. Here's another place where the Split and Join functions come in handy.

The ReplaceWord procedure shown below takes three mandatory arguments: a string to be searched, the word to find within the string, and the replacement text. When you call this procedure, you can include wildcard characters in the string that you pass for the strFind argument. For example, you might call the ReplaceWord procedure from the Immediate window with these parameters:

? ReplaceWord("There will be a test today", "t*t", "party")

The procedure splits the strText argument into an array, then uses the Like operator to compare each element of the array to strFind, replacing the elements that match the wildcard specification.

Function ReplaceWord(strText As String, _
                     strFind As String, _
                     strReplace As String) As String
                    
   ' This function searches a string for a word and replaces it.
   ' You can use a wildcard mask to specify the search string.
   
   Dim astrText()    As String
   Dim lngCount      As Long
   
   ' Split the string at specified delimiter.
   astrText = Split(strText)
   
   ' Loop through array, performing comparison
   ' against wildcard mask.
   For lngCount = LBound(astrText) To UBound(astrText)
      If astrText(lngCount) Like strFind Then
         ' If array element satisfies wildcard search,
         ' replace it.
         astrText(lngCount) = strReplace
      End If
   Next
   ' Join string, using same delimiter.
   ReplaceWord = Join(astrText)
End Function

This procedure is available in the modStrings module in VBA.mdb in the ODETools\V9\Samples\ODETools\V9\Samples\OPG\Samples\CH07 subfolder on the Office 2000 Developer CD-ROM.