sPrinter

The sprintf() family is popular across many programming languages for conveniently displaying information in an attractive way. Despite vocal demand, neither VBA nor Excel support such features—until now.

Introducing the sPrinter module for Excel VBA! Simply write a template for your message, and use curly braces {…} to embed data inside.

vPrint "You have a meeting with {1} {2} at {3} on {4}.", "John", "Doe", Time(), Date()
'       ˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄˄    ˄˄˄˄˄˄  ˄˄˄˄˄  ˄˄˄˄˄˄  ˄˄˄˄˄˄
'                      Message Template                               Data

You have a meeting with John Doe at 1:30:00_PM on 1/1/26.

You can make this look even nicer, by applying format codes to your data!

vPrint "You have a meeting with {1} {2} at {3:h:MM AM/PM} on {4:dddd, mmmm d}.", "John", "Doe", Time(), Date()
'                                             ˄˄˄˄˄˄˄˄˄˄        ˄˄˄˄˄˄˄˄˄˄˄˄˄
'                                             Time Format        Date Format

You have a meeting with John Doe at 1:30 PM on Thursday, January 1.

Syntax

See here for a detailed guide to syntax for message templates. You may construct a template from these components:

• Field: Use curly braces {…} to embed a data field in your message.
• Plaintext: Everything outside a field is displayed verbatim as regular text.

Tip

To display a regular curly brace as plaintext, simply neutralize it with a backslash: \{

You may fine-tune a field by specifying these things:

• Index: Identify the value in the data, either by position or by name.
• Format: Adjust how the value is displayed, using a format code like m/d/yyyy that is native¹ to Excel/VBA.

'  Index              Name
'    ˅             ˅˅˅˅˅˅˅˅˅˅
    {2:m/d/yyyy}  {"birthday":m/d/yyyy}
'      ˄˄˄˄˄˄˄˄               ˄˄˄˄˄˄˄˄
'       Format                 Format

You may specify the index in several ways:

• Position: The (numeric) location of the value within the data. So 1 is the first value, and 2 is the second.

  {1} and {2}
  

  Use a negative number to count from the end. So -1 is the last value, and -2 is the second-to-last.

  {-2} and {-1}
  

• Name: The (textual) name of the value² within the data. You must wrap this in quotes like "birthday" or in further braces like {birthday}.

  {"birthday"} or {{birthday}}
  

• Auto: Simply omit the index altogether, and it uses the next available value.

  {} and {}
  

Important

If you use braces inside your index or format, without \ to neutralize them, then you must keep them balanced. Every active { must (eventually) be followed by }.

API

Here are the features provided by sPrinter, which are useful in VBA and (mostly) in Excel. If you are a developer, and wish to hide these functions from your users in Excel, then look here to activate Option Private for the sPrinter.bas module.

Metadata

Describe the module itself.

• MOD_NAME: The name of the module.
• MOD_VERSION: Its current version.
• MOD_REPO: The URL to its repository.

Messaging

Generate messages by embedding data in a template…

• xMessage(): Source flexible data from any structure, like an array or Collection or Dictionary.
• vMessage(): Supply anonymous values as single arguments…
• iMessage(): …or identify them with name-value pairs.

…and print them to the console.

• xPrint(): Print the output from xMessage()…
• vPrint(): …or from vMessage()…
• iPrint(): …or from iMessage().

Parsing

Break down a template into an array of its components.

• Parse(): Translate the textual template into a ParserElement array.

Utilities

Perform broadly useful tasks.

• Assign(): Assign any value (scalar or objective) to a variable by reference.

• Enum_Has(): Test if an Enumeration combo³ contains one of multiple options.

• Num_Cardinal(): Represent a number as a cardinal like 1,234…
• Num_Ordinal(): …and as an ordinal like 1,234th.

• Arr_Rank(): Count⁴ the dimensions of an array.
• Arr_Length(): Get the length of an array.

• ChrX(): Safely get the character⁵ for a code, regardless of platform.
• Txt_Crop(): Remove a fixed number of characters from the end(s) of a String.
• Txt_List(): Format a set of Strings as a bulleted list.

Footnotes

1. You may choose between the codes used by Format() in VBA, or those used by TEXT() in Excel. ↩

2. This may be a key in a Collection or Dictionary supplied to xMessage() in VBA; or a name paired with a value and supplied to iMessage(). ↩

3. With a bitwise Enumeration like VbMsgBoxStyle, you may layer multiple options using the + and Or operators. ↩

4. In VBA an array may have at most 60 dimensions. ↩

5. The native Chr*() family does not reliably support Unicode on Mac. ↩