Visual Basic Programming Code Examples

Counting the number of instances of a string within another string The code below returns the number of instances of a string within another string. 'Purpose : Counts the number of instances of a specified string within another string. 'Inputs : sText The string to search in. ' sSearchFor The string to search for. ' [bIgnoreCase] If True does a case insensitive comparison. ' [sIgnoreText] If specified will ignore items between subsequent instances of ' "sSearchFor" which match this text (see example 2) 'Outputs : Returns the number of instances of the string. 'Example : eg. Find the instances of the character "A" within a string ' 1. ' Debug.Print CountString("ABCAA","A") ' Returns 3. ' Now find how many lines of data are contained within a string, ignoring any blanks lines. ' 2. ' Debug.Print CountString("red" & vbnewline & "yellow" & vbnewline & vbnewline & "IS" & vbnewline & "GREAT!" & vbnewline,vbnewline,,"") ' Returns 3 (NOT 4). 'Revisions : Function CountString(sText As String, sSearchFor As String, Optional bIgnoreCase As Boolean = True, Optional sIgnoreText As String) As Long Dim asItems() As String, lThisItem As Long On Error GoTo ErrFailed If bIgnoreCase Then asItems = Split(UCase$(sText), UCase$(sSearchFor)) CountString = UBound(asItems) Else asItems = Split(sText, sSearchFor) CountString = UBound(asItems) End If If Len(sIgnoreText) Then 'Deduct any items which contain the specified "sIgnoreText" For lThisItem = 0 To UBound(asItems) - 1 If asItems(lThisItem) = sIgnoreText Then 'Deduct this item CountString = CountString - 1 End If Next End If Exit Function ErrFailed: 'Error occurred Debug.Print "Error in CountString " & Err.Description Debug.Assert False CountString = 0 End Function