Language: Deutsch English

Last Update: 2019 - 02 - 27

A printf or String.Format Function for VBA

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

String.Format / printf in VBA - article header

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.

Screenshot of the date of my original printf function

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

Dim trainName As String = "RE20" Dim destination As String = "Limburg" Dim departurePlatform As Integer = 21 Dim departureTime As Date = #4:45:00 PM# Debug.Print(String.Format("Your train {0} to {1} will leave from platform {2} at {3:t}", _ trainName, destination, departurePlatform, departureTime))

Output from String.Format in

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.

Debug.Print printf("Your train {0} to {1} will leave from platform {2} at {3:hh:nn}", _ trainName, destination, departurePlatform, departureTime) Debug.Print "Your train " & trainName & " to " & destination & " will leave from platform " _ & departurePlatform & " at " & Format(departureTime, "hh:nn")

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.

Public Function printf(ByVal strText As String, ParamArray Args()) As String ' © - You may use, modify, copy, distribute this code as long as this line remains Dim i As Integer Dim strRetVal As String Dim startPos As Integer Dim endPos As Integer Dim formatString As String Dim argValueLen As Integer strRetVal = strText For i = LBound(Args) To UBound(Args) argValueLen = Len(CStr(i)) startPos = InStr(strRetVal, "{" & CStr(i) & ":") If startPos > 0 Then endPos = InStr(startPos + 1, strRetVal, "}") formatString = Mid(strRetVal, startPos + 2 + argValueLen, endPos - (startPos + 2 + argValueLen)) strRetVal = Mid(strRetVal, 1, startPos - 1) & Format(Nz(Args(i), ""), formatString) & Mid(strRetVal, endPos + 1) Else strRetVal = Replace(strRetVal, "{" & CStr(i) & "}", Nz(Args(i), "")) End If Next i printf = strRetVal End

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: Share on Facebook Tweet Share on LinkedIn Share on XING

Subscribe to my newsletter


I will never share your email with anyone. You can unsubscribe any time.
This email list is hosted at Mailchimp in the United States. The data is covered by the EU-U.S. Privacy Shield Framework. See our privacy policy for further details.

© 1999 - 2019 by Philipp Stiefel - Privacy Policiy