'---------------------------------------------------------------------------
 ' NB:  I have avoided abbreviating variable and procedure names to make
 '      things easier to grasp for beginners and non-native English speakers.
 '      Feel free to use Find and Replace to swap these names with something
 '      you prefer.
 '
 '      This demonstration module has been commented with the beginner in
 '      mind.
 '---------------------------------------------------------------------------
 
Option Explicit 
 
 '---------------------------------------------------------------------------
 ' Insert the string to search below between the quotation marks
Const mcstrSearchThisString As String = "How much do those shoes cost?" 
 ' Insert the string to search *for* below between the quotation marks
Const mcstrSearchForThis As String = "os" 
 '---------------------------------------------------------------------------
 
Private Sub TellMeHowManyTimesAndWhere() 
     
     '---------------------------------------------------------------------------
     ' Desc      : This sub procedure calls the PositionsInString function using
     '             the module level constants defined above as the arguments,
     '             printing the results to the immediate window.  You do not need
     '             to adjust the values of these strings to make it work.
     '
     '             When it gets the results of the function, it stores them
     '             in a temporary array to count the total number of instances
     '             and print the starting and ending positions of each instance
     '             to the immediate window.
     '
     ' Remarks   : The purpose of this procedure is to demonstrate how the
     '             PositionsInString function *might* be used.  It is included
     '             here soley for that reason, and it is not required for the
     '             function to work.
     '---------------------------------------------------------------------------
     
     ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
     ' an array in which to store the results of the PositionsInString function
    Dim avarStringPositions As Variant 
     ' a counter that will be used to step through the values in the array
    Dim intCounter As Integer 
     ' a string to store a comma separated list of the locations
    Dim strStringPositions As String 
     ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
     
     ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
     ' Load the array by feeding the values defined in the constants above
     ' to the function called PositionsInString.
     '
     ' NB:  These constants could be replaced by any string enclosed by quotation
     '      marks.
    avarStringPositions = PositionsInString(mcstrSearchThisString, _ 
    mcstrSearchForThis) 
     ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
     
     ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
     ' Loop through the array and extract each value to a comma separated list
    For intCounter = LBound(avarStringPositions, 2) To _ 
        UBound(avarStringPositions, 2) 
        If intCounter = 0 Then 
            strStringPositions = avarStringPositions(0, intCounter) _ 
            & "-" & avarStringPositions(1, intCounter) 
        Else 
            strStringPositions = strStringPositions & ", " _ 
            & avarStringPositions(0, intCounter) & "-" _ 
            & avarStringPositions(1, intCounter) 
        End If 
    Next intCounter 
     ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
     
     ' Add a period to the end of the string (end of sentence)
    strStringPositions = strStringPositions & "." 
     
     ' output to immediate window for example
    Debug.Print "There are a total of " & intCounter _ 
    & " occurrence(s) of " & Chr(34) & mcstrSearchForThis & Chr(34) _ 
    & ":  " & strStringPositions 
     
End Sub 
 
Public Function PositionsInString(SearchThisString As String, _ 
    SearchForThis As String) As Variant 
     
     '---------------------------------------------------------------------------
     ' Desc      : When given a string to search and a string to search for,
     '             this function returns the starting and ending positions
     '             of each instance of the sought string within the searched
     '             string.  It returns these values in a two dimensional array
     '             where the first argument contains the number of occurences
     '             and the second argument contains the starting and ending
     '             positions, respectively.
     '
     ' Arguments : SearchThisString :  The string which will be searched
     '             SearchForThis    :  The string that will be sought
     '
     ' Remarks   : Since this functions doesn't use Split or Join, it will work
     '             work Office 97.
     '--------------------------------------------------------------------------
     
     ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
     ' Integer used to determine if string was found and where
    Dim intPositionOfSoughtString As Integer 
     ' Integer used to identify the starting point of each search
    Dim intStartPosition As Integer 
     ' Variant array to house the starting and ending positions of sought string
    Dim avarStringPositions As Variant 
     ' Integer used to redimension array
    Dim intCounter As Integer 
     ' Integer used to house the length of the sought string to calculate end
    Dim intStringLength As Integer 
     ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
     
    intStringLength = Len(SearchForThis) 
     
    ReDim avarStringPositions(0 To 1, 0 To 0) 
     
     ' The first search will start from this position
    intStartPosition = 1 
     
     ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
     ' Loop until all occurences have been located
    Do 
         ' Record the location of the sought string, if 0 then not found
        intPositionOfSoughtString = InStr(intStartPosition, _ 
        SearchThisString, SearchForThis, vbTextCompare) 
         ' If found, store the initial and final position of the sought string
        If intPositionOfSoughtString <> 0 Then 
             ' Redimension the array as necessary
            ReDim Preserve avarStringPositions(0 To 1, 0 To intCounter) 
             ' Store the initial position of the sought string
            avarStringPositions(0, intCounter) = intPositionOfSoughtString 
             ' Store the final position of the sought string
            avarStringPositions(1, intCounter) = intPositionOfSoughtString _ 
            + intStringLength - 1 
             ' Change the starting position for the next search
            intStartPosition = intPositionOfSoughtString + 1 
             ' Augment the counter so the array is properly redimensioned
            intCounter = intCounter + 1 
        End If 
    Loop Until intPositionOfSoughtString = 0 
     ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
     
     ' Pass the values from the array to the funciton
    PositionsInString = avarStringPositions 
     
End Function