Searching a String

When you must know whether a string contains a particular character or group of characters, you can search the string by using one of two functions. The traditional candidate for this job is the InStr function, which you can use to find one string within another. The InStr function compares two strings, and if the second string is contained within the first, it returns the position at which the substring begins. If the InStr function doesn't find the substring, it returns 0.

The InStr function takes an optional argument, the start argument, in which you can specify the position to begin searching. If you omit this argument, the InStr function starts searching at the first character in the string.

The newest version of Microsoft® Visual Basic® for Applications (VBA) includes a function called InStrRev, which behaves in the same way as the InStr function, except that it begins searching at the end of the string rather than at the beginning. As with the InStr function, you can specify a starting position for the InStrRev function; it will search backward through the string beginning at that point. If you know that the substring you're looking for probably falls at the end of the string, the InStrRev function might be a better option. For example, the InStrRev function makes it easier to parse a file path and return just the file name.

Note   Both the InStr and InStrRev functions return the same value when they locate the same substring. Although the InStrRev function begins searching at the right side of the string, it counts characters from the left side, as does the InStr function. For example, calling either the InStr or InStrRev function to search the string "C:\Temp" for the substring "C:\" returns 1. However, if the substring appears more than when, and you haven't specified a value for the start argument, the InStr function returns the position of the first instance and the InStrRev function returns the position of the last instance.

The following procedure counts the occurrences of a particular character or group of characters in a string. To call the procedure, you pass in the string, the substring that you're looking for, and a constant indicating whether the search should be case-sensitive. The CountOccurrences procedure uses the InStr function to search for the specified text and return the value of the position at which it first occurs; for example, if it's the third character in the string, the InStr function returns 3. The procedure increments the counter variable, which keeps track of the number of occurrences found, and then sets the starting position for the next call to the InStr function. The new starting position is the position at which the search text was found, plus the length of the search string. By setting the start position in this manner, you ensure that you don't locate the same substring twice when you're searching for text that's more than one character in length.

The possible constant values are specified by the built-in enumerated constants in vbCompareMethod, which groups the three VBA string-comparison constants (vbBinaryCompare, vbDatabaseCompare, and vbTextCompare). If you declare an argument as type vbCompareMethod, VBA lists the constants in that grouping when you call the procedure. This is a convenient way to remember what values an argument takes. In addition, you can define your own enumerated constants and use them as data types.

Function CountOccurrences(strText As String, _
                          strFind As String, _
                          Optional lngCompare As VbCompareMethod) As Long

   ' Count occurrences of a particular character or characters.
   ' If lngCompare argument is omitted, procedure performs binary comparison.
   
   Dim lngPos       As Long
   Dim lngTemp      As Long
   Dim lngCount     As Long
   
   ' Specify a starting position. We don't need it the first
   ' time through the loop, but we'll need it on subsequent passes.
   lngPos = 1
   ' Execute the loop at least once.
   Do
      ' Store position at which strFind first occurs.
      lngPos = InStr(lngPos, strText, strFind, lngCompare)
      ' Store position in a temporary variable.
      lngTemp = lngPos
      ' Check that strFind has been found.
      If lngPos > 0 Then
         ' Increment counter variable.
         lngCount = lngCount + 1
         ' Define a new starting position.
         lngPos = lngPos + Len(strFind)
      End If
   ' Loop until last occurrence has been found.
   Loop Until lngPos = 0
   ' Return the number of occurrences found.
   CountOccurrences = lngCount
End Function

Calling this function from the Immediate window as follows returns "3":

? CountOccurrences("This is a test", "t", vbTextCompare)

See Also

Getting the Most Out of Visual Basic for Applications | Working with Strings | Comparing Strings | Calculating String Length | Returning Portions of a String | Working with Strings as Arrays | Replacing Text Within a String | Converting Strings | Working with String Variables | Custom Classes and Objects