'**********************************************************************************************************
' Name:                 formatString
' Author:               mielk | 2012-06-21
'
' Comment:              Function to create string from the given pattern and set of values.
'                       The exact places where the values should be inserted into pattern string are
'                       specified by numbers in brackets, i.e. {0}, {1} etc.
'
'
' Parameters:
'   pattern             Pattern to be modified by the function.
'   replacements        Set of values to be inserted into pattern.
'
'
' Returns:
'   String              Pattern string with values from [replacements] input array inserted at the
'                       specified position of this string.
'                       Values are inserted in the same order they are stored in [replacements] array, i.e.
'                       * first value replaces the tag {0} (this tag can be multiplied within pattern
'                         string; all occurrences of this tag will be replaced with the first value
'                         from source values array)
'                       * second value replaces the tag {1}, etc
'
'                       If there is more values defined in [replacements] array than tags in the pattern
'                       string, all the exceed values are ignored.
'
'                       If there is more tags in the pattern string than the values in [replacements]
'                       array, IndexOutOfBoundException is generated.
'
'
' Exceptions:
'   IndexOutOfBoundException        Thrown if there is more tags to be replaced in the pattern string
'                                   than values in input array [replacements].
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2012-06-21        mielk           Function created.
'**********************************************************************************************************
Public Function formatString(pattern As String, ParamArray replacements() As Variant) As String
    Const METHOD_NAME As String = "formatString"
    '------------------------------------------------------------------------------------------------------
    Dim parts() As String
    Dim varPart As Variant
    Dim strPart As String
    Dim strCode As String
    Dim intCode As Integer
    Dim value As Variant
    '------------------------------------------------------------------------------------------------------
    
    
    'Split the input pattern into parts by using VBA built-in function Split. ---------------------------|
    parts = VBA.Split(pattern, "{")                                                                     '|
    '----------------------------------------------------------------------------------------------------|
    
    
    'Iterate through all the parts obtained above by splitting the pattern string. ----------------------|
    For Each varPart In parts                                                                           '|
                                                                                                        '|
        'Get the current part as string and obtain its number code. ---------------------------------|  '|
        strPart = VBA.CStr(varPart)                                                                 '|  '|
        strCode = substring(strPart, "", "}", False)                                                '|  '|
        '--------------------------------------------------------------------------------------------|  '|
                                                                                                        '|
                                                                                                        '|

        'All the pattern parts without a number in curly brackets are ignored. ----------------------|  '|
        If VBA.IsNumeric(strCode) Then                                                              '|  '|
            intCode = VBA.CInt(strCode)                                                             '|  '|
                                                                                                    '|  '|
            'Get the value with the given index from [replacements] array. If there is no -------|  '|  '|
            'item with such index, IndexOutOfBoundException is thrown.                          '|  '|  '|
            On Error GoTo IndexOutOfBoundException                                              '|  '|  '|
            value = replacements(intCode)                                                       '|  '|  '|
            '------------------------------------------------------------------------------------|  '|  '|
                                                                                                    '|  '|
            'Replace the current pattern part with the value from [replacements] array.             '|  '|
            strPart = VBA.Replace(strPart, strCode & "}", replacements(intCode))                    '|  '|
                                                                                                    '|  '|
        End If                                                                                      '|  '|
        '--------------------------------------------------------------------------------------------|  '|
                                                                                                        '|
                                                                                                        '|
        'Append this part after modification to the result string.                                      '|
        formatString = formatString & strPart                                                           '|
                                                                                                        '|
                                                                                                        '|
    Next varPart                                                                                        '|
    '----------------------------------------------------------------------------------------------------|
    
    
'==========================================================================================================
ExitPoint:
    Exit Function

'----------------------------------------------------------------------------------------------------------
IndexOutOfBoundException:
    'Error handling for the case if the input array [replacements] has less values than tags in the
    'pattern string.
    GoTo ExitPoint

End Function




'**********************************************************************************************************
' Name:                 substring
' Author:               mielk | 2011-07-20
'
' Comment:              Function to return the substring of a source string between two given
'                       substring delimiters.
'
'                       Technically, the function looks for the position of the first occurrence of the
'                       given left delimiter. Then, it looks for the first occurrence of the given right
'                       delimiter after that position. Finally it returns substring between those two
'                       positions.
'
' Parameters:
'   sourceText          Base string.
'
'   leftSubstr          Left delimiter. The result string will start after the first occurrence of this
'                       substring in the source text.
'                       If this substring is empty, the result string will start from the beginning of the
'                       source text.
'
'   rightSubstr         Right delimiter. The result string will end before first occurence of this string
'                       after the position of the leftSubstr.
'                       If this substring is empty, the result string will contain all the characters to
'                       the end of the source text.

'   isCaseSensitive     Optional parameter of Boolean type.
'                       It determines if text searching 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:
'   String              Substring between first occurencies of [leftSubstr] and [rightSubstr].
'
'                       If there is either no [leftSubstr] in the source text or no [rightSubstr] after
'                       this [leftSubstr], then empty string is returned.
'
'                       If [leftSubstr] is empty string, the result substring starts from the beginning of
'                       the base string.
'
'                       If [rightSubstr] is empty string, the result substring will contain all the
'                       characters between the first occurrence of the [leftSubstr] and the end of
'                       the source text.
'
'                       Examples:
'                       ---------------------------------------------------------------------------------
'                                   Function with parameters                |     Result
'                               -------------------------------------------------------------
'                               substring("abc", "a", "c", True)            |       b
'                               substring("abC", "a", "c", True)            |
'                               substring("abC", "a", "c", False)           |       b
'                               substring("abc", "", "c", True)             |       ab
'                               substring("abc", "a", "", True)             |       bc
'                               substring("abC", "", "c", False)            |       ab
'                               substring("abC", "", "c", True)             |
'                               substring("[value]", "[", "]", True)        |       value
'                               substring("[value]", "[", "]", False)       |       value
'                               substring("aaabbb", "a", "b", True)         |       aa
'                               substring("aaabbb", "a", "a", True)         |
'                               substring("aaabbb", "", "ab", True)         |       aa
'                               substring("aaabbb", "", "a", True)          |
'                               substring("aaabbb", "b", "", True)          |       bb
'                               substring("one two three", " ", " ", True)  |       two
'                               substring("one two three", " ", "", True)   |       two three
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2011-07-20    mielk       Function created.
'**********************************************************************************************************
Public Function substring(sourceText As String, leftSubstr As String, rightSubstr As String, _
                                                                Optional isCaseSensitive As Boolean = True)
    Const METHOD_NAME As String = "substring"
    '------------------------------------------------------------------------------------------------------
    Dim lStart As Long
    Dim lEnd As Long
    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                                                                                              '|
    '----------------------------------------------------------------------------------------------------|


    'The function searches for the position of the given delimiters. ------------------------------------|
    If VBA.Len(leftSubstr) Then                                                                         '|
        lStart = VBA.InStr(1, sourceText, leftSubstr, uCompareMethod)                                   '|
        lEnd = VBA.InStr(lStart + VBA.Len(leftSubstr), sourceText, rightSubstr, uCompareMethod)         '|
    Else                                                                                                '|
        lStart = 1                                                                                      '|
        lEnd = VBA.InStr(1, sourceText, rightSubstr, uCompareMethod)                                    '|
    End If                                                                                              '|
                                                                                                        '|
    If VBA.Len(rightSubstr) = 0 Then lEnd = VBA.Len(sourceText) + 1                                     '|
                                                                                                        '|
    '----------------------------------------------------------------------------------------------------|
    

    

    'If both delimiters have been found, substring can be returned. -------------------------------------|
    If lStart > 0 And lEnd > 0 Then                                                                     '|
        substring = VBA.Mid$(sourceText, lStart + VBA.Len(leftSubstr), _
                                                                lEnd - lStart - VBA.Len(leftSubstr))    '|
    End If                                                                                              '|
    '----------------------------------------------------------------------------------------------------|
    

End Function