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
%11do not get messed up by
%1placeholders. 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).
ParamArrayfor passing a variable number of arguments to a function
LBound()for safely looping through an array (especially one where the bounds won't be known until runtime)
Step -1for looping backwards through the
- Using the Unicode Private Use Area (PUA) to provide a temporary replacement character that should never occur in a normal string
To use the function, enter placeholders numbered
%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
%10in 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
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