String interpolation

Interpolation means 'inserting one thing into another'. In this case, inserting one (or more) 'sub' Strings into another 'main' String using specific 'targets'. VBA doesn't have native String interpolation functionality ... something which is built into many modern languages ... so I developed my own using the StrFmt() Function ... provided below.

Why bother with this? Especially with lengthy Strings, it makes writing the code to produce the desired output simpler and (normally) shorter.

StrFmt() uses braces (that is "{" and "}") to indicate where the targets are within the main String and these targets are replaced with sub Strings. There are several types of targets that StrFmt() supports:

• Simple targets using an integral number starting at 0 for the first sub String, then 1 for the second sub String etc - for example"{0}" or "{1}"

• Targets with alignment - again using an integral number starting at 0 combined with a comma and another integral number which can be positive (for right alignment) or negative (for left alignment) - for example "{0,5}"

• Fixed value targets - using letters - for example "{n}" is vbNewLine and "{b}" is a bullet point

• Pluralisation targets using an integral number starting at 0 for the first sub String combined with the pluralisation indicator, then 1 for the second sub String etc - for example"{0s}" or "{1has}"

Note that each target (and so each sub String) can be used multiple times in the main String.

This should become much clearer with a few examples. In each of these examples, I've created the final output String in two ways:

• First the typical way using ampersands to join parts of a String together, then

• Used interpolation (i.e. the StrFmt() function) to do the same

... the last line (or lines) shows the output.

1. Using 2 sub Strings

2. Demonstrating 'pluralisation'

... if lCount had a value of 1 then this would produce "Found 1 match."

3. Demonstrating using the same sub String multiple times (i.e. "{0}" is used twice), demonstrating 'pluralisation' and demonstrating fixed value targets (i.e. "{n}" is vbNewLine)

4. Demonstrating alignment.

As you'll notice from the above 4 examples, the longer the String to be produced, generally, the more benefit StrFmt() brings by way of simplifying the main String and improving code clarity.

So, here is StrFmt():

A point to acknowledge is that this is all very 'English orientated' and I appreciate not all readers of this post will use English as a first language ... or you might be writing code that needs to produce messages in languages other than English. If this is the case, I hope that StrFmt() is simple enough to update for the language you need ... likely you will need to update the single occurrence of "one" to the required language and then each of the 'pluralisations' with language-specific versions (though "s" and "es" will work in many).

Whether you are using English or not, you can also add other 'pluralisations' by just inserting an additional line of code at the relevant point within StrFmt(). For example, the plural of "city" is "cities" (not "citys"). To handle this scenario, you would add this line to StrFmt() ...

... and use it like this ...

I use StrFmt() a lot ... once you get used to the structure of the 'main' Strings then it becomes straight-forward to use and can significantly reduced the length of complex Strings.

It's also the reason that VBE_Extras includes brace characters in auto-closing pairs.