| Microsoft Office 2000/Visual Basic Programmer's Guide | |
When you need to 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 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 may be a better option. For example, the InStrRev function makes it easier to parse a file path and return just the file name. The next section, "Returning Portions of a String," shows an example of how to use the InStrRev function.
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 once, 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 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. You can also define your own enumerated constants and use them as data types. For more information about creating enumerated constants, see Chapter 9, "Custom Classes and Objects."
The following 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. This value is stored in a temporary variable so that the value can be maintained after the next call to the InStr function. 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.
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
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.
Calling this function from the Immediate window as follows returns "3":
? CountOccurrences("This is a test", "t", vbTextCompare)