VBA string interpolation syntax

You could also build a custom Format function.

Public Function Format(ParamArray arr() As Variant) As String

    Dim i As Long
    Dim temp As String

    temp = CStr(arr(0))
    For i = 1 To UBound(arr)
        temp = Replace(temp, "{" & i - 1 & "}", CStr(arr(i)))
    Next

    Format = temp
End Function

The usage is similar to C# except that you can't directly reference variables in the string. E.g. Format("This will {not} work") but Format("This {0} work", "will").

Public Sub Test()

    Dim s As String

    s = "Hello"
    Debug.Print Format("{0}, {1}!", s, "World")
End Sub

Prints out Hello, World! to the Immediate Window.


This works well enough, I believe.

Dim row as Long
Dim s as String

row = 1
s = "$" & row & ":$" & row

Unless you want something similar to Python's or C#'s {} notation, this is the standard way of doing it.


Using Key\Value Pairs

Another alternative to mimic String interpolation is to pass in key\value pairs as a ParamArray and replace the keys accordingly.

One note is that an error should be raised if there are not an even number of elements.

' Returns a string that replaced special keys with its associated pair value.
Public Function Inject(ByVal source As String, ParamArray keyValuePairs() As Variant) As String

    If (UBound(keyValuePairs) - LBound(keyValuePairs) + 1) Mod 2 <> 0 Then
        Err.Raise 5, "Inject", "Invalid parameters: expecting key/value pairs, but received an odd number of arguments."
    End If

    Inject = source

    ' Replace {key} with the pairing value.
    Dim index As Long
    For index = LBound(keyValuePairs) To UBound(keyValuePairs) Step 2
        Inject = Replace(Inject, "{" & keyValuePairs(index) & "}", keyValuePairs(index + 1), , , vbTextCompare)
    Next index

End Function

Simple Example

Here is a simple example that shows how to implement it.

Private Sub testingInject()
    Const name As String = "Robert"
    Const age As String = 31
    Debug.Print Inject("Hello, {name}! You are {age} years old!", "name", name, "age", age)
    '~> Hello, Robert! You are 31 years old!
End Sub

Although this may add a few extra strings, in my opinion, this makes it much easier to read long strings.

See the same simple example using concatenation:

Debug.Print "Hello, " & name & "! You are " & age & " years old!"

Using Scripting.Dicitionary

Really, a Scripting.Dictionary would be perfect for this since they are nothing but key/value pairs. It would be a simple adjustment to my code above, just take in a Dictionary as the parameter and make sure the keys match.

Public Function Inject(ByVal source As String, ByVal data As Scripting.Dictionary) As String
    Inject = source

    Dim key As Variant
    For Each key In data.Keys
        Inject = Replace(Inject, "{" & key & "}", data(key))
    Next key
End Function

Dictionary example

And the example of using it for dictionaries:

Private Sub testingInject()
    Dim person As New Scripting.Dictionary
    person("name") = "Robert"
    person("age") = 31
    Debug.Print Inject("Hello, {name}! You are {age} years old!", person)
    '~> Hello, Robert! You are 31 years old!
End Sub

Additional Considerations

Collections sound like they would be nice as well, but there is no way of accessing the keys. It would probably get messier that way.

If using the Dictionary method you might create a simple factory function for easily creating Dictionaries. You can find an example of that on my Github Library Page.


To mimic function overloading to give you all the different ways you could create a main Inject function and run a select statement within that.

Here is all the code needed to do that if need be:

Public Function Inject(ByVal source As String, ParamArray data() As Variant) As String

    Dim firstElement As Variant
    assign firstElement, data(LBound(data))

    Inject = InjectCharacters(source)

    Select Case True
        Case TypeName(firstElement) = "Dictionary"
            Inject = InjectDictionary(Inject, firstElement)

        Case InStr(source, "{0}") > 0
            Inject = injectIndexes(Inject, CVar(data))

        Case (UBound(data) - LBound(data) + 1) Mod 2 = 0
            Inject = InjectKeyValuePairs(Inject, CVar(data))

        Case Else
            Err.Raise 5, "Inject", "Invalid parameters: expecting key/value pairs or Dictionary or an {0} element."
    End Select

End Function

Private Function injectIndexes(ByVal source As String, ByVal data As Variant)
    injectIndexes = source

    Dim index As Long
    For index = LBound(data) To UBound(data)
        injectIndexes = Replace(injectIndexes, "{" & index & "}", data(index))
    Next index
End Function


Private Function InjectKeyValuePairs(ByVal source As String, ByVal keyValuePairs As Variant)
    InjectKeyValuePairs = source

    Dim index As Long
    For index = LBound(keyValuePairs) To UBound(keyValuePairs) Step 2
        InjectKeyValuePairs = Replace(InjectKeyValuePairs, "{" & keyValuePairs(index) & "}", keyValuePairs(index + 1))
    Next index
End Function

Private Function InjectDictionary(ByVal source As String, ByVal data As Scripting.Dictionary) As String
    InjectDictionary = source

    Dim key As Variant
    For Each key In data.Keys
        InjectDictionary = Replace(InjectDictionary, "{" & key & "}", data(key))
    Next key
End Function

' QUICK TOOL TO EITHER SET OR LET DEPENDING ON IF ELEMENT IS AN OBJECT
Private Function assign(ByRef variable As Variant, ByVal value As Variant)
    If IsObject(value) Then
        Set variable = value
    Else
        Let variable = value
    End If
End Function

End Function

Private Function InjectCharacters(ByVal source As String) As String

    InjectCharacters = source

    Dim keyValuePairs As Variant
    keyValuePairs = Array("n", vbNewLine, "t", vbTab, "r", vbCr, "f", vbLf)

    If (UBound(keyValuePairs) - LBound(keyValuePairs) + 1) Mod 2 <> 0 Then
        Err.Raise 5, "Inject", "Invalid variable: expecting key/value pairs, but received an odd number of arguments."
    End If

    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    RegEx.Global = True

    ' Replace is ran twice since it is possible for back to back patterns.
    Dim index As Long
    For index = LBound(keyValuePairs) To UBound(keyValuePairs) Step 2
        RegEx.Pattern = "((?:^|[^\\])(?:\\{2})*)(?:\\" & keyValuePairs(index) & ")+"
        InjectCharacters = RegEx.Replace(InjectCharacters, "$1" & keyValuePairs(index + 1))
        InjectCharacters = RegEx.Replace(InjectCharacters, "$1" & keyValuePairs(index + 1))
    Next index

End Function

Tags:

Vba