VBA – String.Format or how to put variables in a string

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):

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:

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:

VBA – String.Format or how to put variables in a string

Related posts: