# "String Interpolation in {language}", "VBA"

### "This is {0} cool!", "freaking"

I've always wanted an easy and intuitive way to inject variables into a string. So after about 10 variations, I finally came up with this function.

### How it works

The concept is that I can find every pattern such as {key} or {0} or whatever {taco} and get a unique list of these. If there are more keys than there are variables it will raise a custom error. (Keys are case sensitive.)

If it matches then it uses the index of each ParamArray variable and matches that to the index of the pattern list. For example "{bacon} {burrito}" bacon: 0, burrito: 1.

With that match, it simply replaces every instance of the match with the value of the variable.

I originally had the pattern start with a dollar sign ${0} to copy JavaScripts syntax but decided to keep it shorter for simplicity. It does use the escape character \. Example: \{test} would be print {test}. It also includes shortcuts for vbNewLine \n and vbTab \t. ### The formula Make sure to first set references to Microsoft Scripting Runtime and Microsoft VBScript Regular Expressions 5.5. I thought about doing this late binding but figured performance is probably better with these libraries referenced and they are common enough that it should not matter. ' Returns a new cloned string that replaced special {keys} with its associated pair value. ' Keys can be anything since it goes off of the index, so variables must be in proper order! ' Can't have whitespace in the key. ' Also Replaces "\t" with VbTab and "\n" with VbNewLine ' ' @author: Robert Todar <https://github.com/todar> ' @reference: Microsoft Scripting Runtime - [Dictionary] ' @reference: Microsoft VBScript Regular Expressions 5.5 - [RegExp, Match] ' @example: Inject("Hello, {name}!\nJS Object = {name: {name}, age: {age}}\n", "Robert", 31) Public Function Inject(ByVal source As String, ParamArray values() As Variant) As String ' Want to get a copy and not mutate original Inject = source Dim regEx As RegExp Set regEx = New RegExp ' Late Binding would be: CreateObject("vbscript.regexp") With regEx .Global = True .MultiLine = True .IgnoreCase = True ' This section is only when user passes in variables If Not IsMissing(values) Then ' Looking for pattern like: {key} ' First capture group is the full pattern: {key} ' Second capture group is just the name: key .Pattern = "(?:^|[^\\])(\{([\w\d\s]*)\})" ' Used to make sure there are even number of uniqueKeys and values. Dim keys As New Scripting.Dictionary Dim keyMatch As match For Each keyMatch In .Execute(Inject) ' Extract key name Dim key As Variant key = keyMatch.submatches(1) ' Only want to increment on unique keys. If Not keys.Exists(key) Then If (keys.Count) > UBound(values) Then Err.Raise 9, "Inject", "Inject expects an equal amount of keys to values. Keys found: " & Join(keys.keys, ", ") & ", " & key End If ' Replace {key} with the pairing value. Inject = Replace(Inject, keyMatch.submatches(0), values(keys.Count)) ' Add key to make sure it isn't looped again. keys.Add key, vbNullString End If Next End If ' Replace extra special characters. Must allow code above to run first! .Pattern = "(^|[^\\])\{" Inject = .Replace(Inject, "$1" & "{")

.Pattern = "(^|[^\\])\\t"
Inject = .Replace(Inject, "$1" & vbTab) .Pattern = "(^|[^\\])\\n" Inject = .Replace(Inject, "$1" & vbNewLine)

.Pattern = "(^|[^\\])\\"
Inject = .Replace(Inject, "\$1" & "")
End With

End Function


### The tests for it

My first test is using RegExr.com to see if my pattern would match. Just to note, I use the first capture group as the actual replacement so the characters before will not be replaced.

The next step was to try it in VBA. I just copied the same lines and printed them to the immediate window.

Private Sub testingInject()
Debug.Print Inject("{it} works with with words.", "It")
Debug.Print Inject("{0} works with digits.", "It")
Debug.Print Inject("{it } works with whitespace.", "It")
Debug.Print Inject("{ {it} } doesn't effect outer nestings.", "It")
Debug.Print Inject("\{it} should be escaped.", "It did not but")
Debug.Print Inject("Hello, {name}! {name}, \{(escaped) you} are {age} years old!.", "Robert", 31)
Debug.Print Inject("Hello, {name}!\n{\n\tname: {name},\n\t age: {age}\n}", "Robert", 31)

On Error Resume Next 'Expect this to fail
Debug.Print Inject("Hello, {name}! How are you {Name}", "Robert")
Debug.Print Err.Description
End Sub


Here are the results. They printed how I expected them to.

### What I would hope for in answers

• Performance I want to make sure I'm not missing anything that might be a big trade-off of using this.
• RegEx Check I am not the best at this and would love to get better at writing these. This is a good example I feel for learning.
• Improvements is there anything I'm missing? Could this become even cooler?
• Possible bugs really are there any tests I should be running more than what I have.
• Anything really I want to continue to learn and grow as a programmer. =)
• Related: String.Format implementation, for VBA/VB6 =) Jul 26, 2019 at 15:16
• @MathieuGuindon I should have known you have something out there already! =) I wish you had more of your code on Github. Jul 26, 2019 at 15:19
• lol, this comment is past-me essentially wishing I'd have uploaded it all to GitHub! Jul 26, 2019 at 15:33
• Well, it being on stack overflow is better than it being written on a napkin somewhere lol. Jul 26, 2019 at 15:36
• Why do you limit your key to English letters, whitespace and numbers only? (E.g. I'm Hungarian and might use letters with accent (áéíóö...) which are not accepted currenlty) Why not just go until closing bracket ([^}])? Jul 30, 2019 at 8:17

## DISCLAIMER ˆ_ˆ

I'm not a reviewer, will be commenting on your regular expression though.

• I guess you don't have to escape { or }.
• \w construct would already cover 0-9, the \d construct can be removed then (?:^|[^\\])(\{([\w\s]*)\}).
• Since we're using a character class ([]), maybe we'd just list our desired chars right in there without using any construct (e.g., [A-Za-z0-9 ]).
• My guess is that {} would be an undesired/invalid input, which if that'd be the case, we'd use + greedy quantifier instead of *.
• I see some capturing groups, maybe we could remove some of those also, if that'd be OK. Reducing the number of capturing groups would help in memory complexity, even though memory is not usually such a big thing (here).

(?:^|[^\\\r\n]){([A-Za-z0-9 ]+)}


## Demo

If you wish to simplify/update/explore the expression, it's been explained on the top right panel of regex101.com. You can watch the matching steps or modify them in this debugger link, if you'd be interested. The debugger demonstrates that how a RegEx engine might step by step consume some sample input strings and would perform the matching process.

### RegEx Circuit

jex.im visualizes regular expressions:

### Performance

This expression

^{([A-Za-z0-9 ]+)}|[^\\\r\n]{([A-Za-z0-9 ]+)}


has a better performance as compared to the first one

(?:^|[^\\\r\n]){([A-Za-z0-9 ]+)}


in terms of time complexity. You can look into the number of steps in the demo. Finding ways to bypass alternation (|) is usually a good thing, it would help in runtime.