'**********************************************************************************************************
' Name:                 countOccurrences
' Author:               mielk | 2012-01-17
'
' Comment:              Function to count how many times a string appears within another string.
'
' Parameters:
'   sourceText          The source text to be searched for occurrences of the given substring.
'                       It has to be of the String type or of a type that can be converted to string.
'
'   substr              String whose occurrences are to be counted.
'                       It has to be of the String type or of a type that can be converted to string.
'
'   isCaseSensitive     Optional parameter of Boolean type.
'                       It determines if text matching is case sensitive.
'
'                       If this value is set to True, searching is case sensitive - a letter in lowercase
'                       is treated as different than the same letter in uppercase (i.e. a ? A).
'
'                       If this value is set to False, it doesn't matter if a letter is in lowercase or in
'                       uppercase, since both of them are considered as the same character (i.e. a = A).
'
'                       Default value of this parameter is True.
'
'
' Returns:
'   Integer             Number of occurrences of a given character or string [substr] within another string
'                       [sourceText].
'                       The occurrences of a substring can overlap. For example, if you want to find
'                       occurrences of substring 'aa' within text 'baaa', the function will find two of
'                       them:
'                           - the first one consisting of characters at index 2 and 3 of the source string,
'                           - the second one consisting of characters at index 3 and 4 of the source string
'                       As you can see, character [a] at index 3 of the source text is a part of
'                       two occurrences at the same time.
'
'                       If the [substr] parameter is an empty String, 0 is returned.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2012-01-17    mielk       Method created.
'**********************************************************************************************************
Public Function countOccurrences(sourceText As String, substr As String, _
                                                     Optional isCaseSensitive As Boolean = True) As Integer
    Const METHOD_NAME As String = "countOccurrences"
    '------------------------------------------------------------------------------------------------------
    Dim iPosition As Integer
    Dim uCompareMethod As VBA.VbCompareMethod
    '------------------------------------------------------------------------------------------------------


    'Convert [isCaseSensitive] parameter of Boolean type to the [VbCompareMethod] enumeration. ----------|
    If isCaseSensitive Then                                                                             '|
        uCompareMethod = VBA.vbBinaryCompare                                                            '|
    Else                                                                                                '|
        uCompareMethod = VBA.vbTextCompare                                                              '|
    End If                                                                                              '|
    '----------------------------------------------------------------------------------------------------|


    'Check if both parameters - sourceText and substr - have at least one character. --------------------|
    'If not 0 should be returned, so there is no point to make any operation.                           '|
    If VBA.Len(sourceText) > 0 And VBA.Len(substr) > 0 Then                                             '|
                                                                                                        '|
        '--------------------------------------------------------------------------------------------|  '|
        Do                                                                                          '|  '|
            'In each iteration of this loop the first occurence of [substr] within the source       '|  '|
            'text is retrieved (using VBA built-in function InStr). Searching starts from the       '|  '|
            'character after the last found occurence.                                              '|  '|
            iPosition = VBA.InStr(iPosition + 1, sourceText, substr, uCompareMethod)                '|  '|
                                                                                                    '|  '|
            'If substring has been found within the source text, the number of occurences is        '|  '|
            'increased by 1 and the next searching will start from the position of this             '|  '|
            'occurence. Otherwise the loop is skipped.                                              '|  '|
            If iPosition Then countOccurrences = countOccurrences + 1 Else Exit Do                  '|  '|
                                                                                                    '|  '|
        Loop                                                                                        '|  '|
        '--------------------------------------------------------------------------------------------|  '|
                                                                                                        '|
    End If                                                                                              '|
    '----------------------------------------------------------------------------------------------------|
    
    
End Function