Format string


Since function formatString uses some non-built VBA functions, they also must be included in your code for the function to work properly.

Otherwise the following error will occur: Compile error: Sub or Function not defined.

Required functions are listed below. You can get to each function's source code by clicking its name:

When adding the functions above to your VBA project, make sure you haven't done it before. If there are two different public functions with the same name in a single VBA project, the following compilation error is thrown: Compile error: Ambiguous name detected: function_name.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
'**********************************************************************************************************
' 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