C#, Python, Java and all the other “fancy” languages have a built-in method, which writes variables into a string. VBA does not have one. Depending on why do we need it, and if we can guarantee that no injection would be implemented to break something, there is an easy way to get what we need – simply replace the {i} with the next coming variable in the parameter array. Or with other words, if the idea is from the following mask:
“The quick {0} fox {1} over the lazy {2}.”
to get:
The quick brown fox jumps over the lazy dog.
using:
StringFormat(sentence, "brown", "jumps", "dog")
then the function is actually quite a basic one (presuming no injections are going to be used):
|
1 2 3 4 5 6 7 8 9 |
Public Function StringFormat(ByVal mask As String, ParamArray tokens()) As String Dim i As Long For i = LBound(tokens) To UBound(tokens) mask = Replace(mask, "{" & i & "}", tokens(i)) Next StringFormat = mask End Function |
This is a small sample, which illustrates the usage of the function:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
Sub Main() Dim condition As String Dim a As Long, b As Long, c As Long a = 10 b = 20 c = 22 condition = "{0} >= {1}" Debug.Print StringFormat(condition, a, b - c) Debug.Print Evaluate(StringFormat(condition, a, b - c)) Debug.Print StringFormat(condition, a, b) Debug.Print Evaluate(StringFormat(condition, a, b)) Dim sentence As String sentence = "The quick {0} fox {1} over the lazy {2}." Debug.Print sentence Debug.Print StringFormat(sentence, "brown", "jumps", "dog") End Sub |
At the end this is what we get in the immediate window:
