VBA string interpolation syntax

Question

What is the VBA string interpolation syntax? Does it exist?

I would to to use Excel VBA to format a string. I have a variable foo that I want to put in a string for a range.

Dim row as Long
row = 1

myString = "$row:$row"

I would like the $row in the string to be interpolated as "1"

Answer 1

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.