A printf or String.Format Function for VBA

by Philipp Stiefel, originally published March 16th, 2019, last updated March 16th, 2019

Picture by Rebecca Moninghoff. Used here under CC0 licensing

I very recently wrote a text and recorded a video about the ParamArray keyword / argument type. In that article/video I used the Coalesce function as an example to show where that would be useful. However, I was asked if I could name another useful example for the use of the ParamArray.

There is one example, I had in mind right away. And while I cannot remember using the Coalesce function in VBA and Microsoft Access before writing that article on ParamArray, this example is actually a function I used (almost) every day the last 16 years. – Yes, 16 years, I was stunned when I looked at the functions implementation.

Did you ever work with the C-programming language? If yes you most likely know the printf-Function. If you worked with the Microsoft .Net Framework, you will know the String.Format method.

Both functions will take a string argument, which includes placeholders, and a variable number of other arguments which will be put into the position of the placeholders in that string.

Example in VB.net:

This function/method is very useful, because:

1. You do not need to write the code for the tedious and error prone string concatenation each time you want to create such a string from various variables.
2. The input string is more readable in the code this way. So, you will spot errors faster and it is easier to change it.
3. If you need to apply a formatting to the values inserted into the string the two reasons above each get even more weight.
4. You can store the string in a table or file for easy localization and have it filled with the required values at run time.

Let’s compare the above with the code that produces the same result using string concatenation in VBA.

So, if you would ask me, which of these two approaches I prefer in my code, I would not need a split second to decide.

For that reason, I wrote my own implementation of String.Format/printf in VBA. The implementation itself is nothing out of the ordinary. However, handling that variable number of arguments is once again what makes this depend on the ParamArray keyword.

After writing or copying (your welcome, but please keep the © line), this function into your own project, you can simply use the example I showed for .net String.Format in your VBA code. Except, you need to use format instructions which Access/VBA understands.

This function is called printf here because I used it under that name for so long in many projects. – Feel free to come up with a better a name!

I use this function in my projects for a variety of scenarios. E.g. writing log messages, formatting the text of a message box, formatting text in labels and so on.

As this text mainly is an answer to the question of a viewer on my YouTube channel, this topic is available as a video as well.

Share this article: