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