Printf Function

VBA may not have native support for string interpolation, but this deceptively clever function from Vladimir Vissoultchev will get you many of the same benefits.

"In computer programming, string interpolation is the process of evaluating a string literal containing one or more placeholders, yielding a result in which the placeholders are replaced with their corresponding values."  -Wikipedia

Over on the twinBASIC GitHub issues project, someone requested string interpolation as a language feature.  Vladimir (@wqweto) offered the following function–very loosely based on the C printf function–as a workaround to provide similar functionality:

Public Function Printf(ByVal sText As String, ParamArray A() As Variant) As String
    Const LNG_PRIVATE   As Long = &HE1B6 '-- U+E000 to U+F8FF - Private Use Area (PUA)
    Dim lIdx            As Long
    
    For lIdx = UBound(A) To LBound(A) Step -1
        sText = Replace(sText, "%" & (lIdx - LBound(A) + 1), Replace(A(lIdx), "%", ChrW$(LNG_PRIVATE)))
    Next
    Printf = Replace(sText, ChrW$(LNG_PRIVATE), "%")
End Function

Vladimir included the following brief explanation with his code:

The loop is backwards so that %10 and %11 do not get messed up by %1 placeholders. This allows values to contain "%1" strings too that do not get replaced (are not treated as placeholders).

Some Neat Tricks

One of the reasons I asked Vladimir if I could re-post his code is that he had several relatively advanced techniques packed into a small space, one of which that was completely new to me (the last one on the list below).

• ParamArray for passing a variable number of arguments to a function
• UBound()/LBound() for safely looping through an array (especially one where the bounds won't be known until runtime)
• Step -1 for looping backwards through the For loop
• Using the Unicode Private Use Area (PUA) to provide a temporary replacement character that should never occur in a normal string

Usage

To use the function, enter placeholders numbered %1, %2, %3, etc. inside your literal string.  Then pass values that correspond with the numbered placeholders as additional arguments to the function.  Some notes:

• There is no practical limit to the number of items you can replace (e.g., the function supports %1 and %10 in the same string)
• You can reuse a placeholder if it refers to the same value (e.g., Printf("x: %1; y: %2; z: %3; x: %1", x, y, z))
• You can remove a placeholder from the literal string if you don't need it (e.g., Printf("x: %1; z: %3", x, y, z))
• Your replacement values may contain percent signs

Caveats and Edge Cases

While the function uses some nice techniques, it's not foolproof.

Cristian Buse pointed out one edge case that the Printf function does not handle correctly:

This would be an unusual situation, but that's why they call them edge cases.  

"The only way to be sure there are no unexpected edge cases is to parse the mask. It can be done character by character or as a split." -Cristian Buse

A Better Printf Function?

Here's Cristian's refactored Printf function that handles the above edge case correctly:

Public Function Printf(ByVal mask As String, ParamArray tokens() As Variant) As String
    Dim parts() As String: parts = Split(mask, "%")
    Dim i As Long
    Dim j As Long
    Dim isFound As Boolean
    Dim s As String
    '
    'Always ignore first part - covers if mask started or not with %
    For i = LBound(parts) + 1 To UBound(parts)
        If LenB(parts(i)) = 0 Then
            parts(i) = "%"
        Else
            isFound = False
            For j = UBound(tokens) To LBound(tokens) Step -1
                s = CStr(j + 1)
                If Left$(parts(i), Len(s)) = s Then
                    parts(i) = tokens(j) & Right$(parts(i), Len(parts(i)) - Len(s))
                    isFound = True
                    Exit For
                End If
            Next j
            If Not isFound Then
                parts(i) = "%" & parts(i)
            End If
        End If
    Next i
    Printf = Join(parts, vbNullString)
End Function

External references

Image by PublicDomainPictures from Pixabay