Search notes:

VBA function: format

Left and right aligning text

Using a number of at-signs (@) left padds (that is: right aligns) text with the number of characters being the same as the number of at-signs:

print debug.print format("foo", string(10, "@")) & "<"

In order to left-align the text, the format string must be prepended with an exclamation mark (!):

print debug.print format("bar", "!" & string(10, "@")) & "<"

Formatting numbers with leading and trailing zeros

debug.print( format(42, "0000") & "<" ) 
0042<

debug.print( format(15.3, "0000.00") )
0015.30

Aligning numbers on their decimal separators

The following example shows how format can be used to align a number on its decimal separator:

option explicit

sub alignDecimalSeperator(num as variant)

  '
  ' In order to align a number on the decimal seperator, two
  ' format expressions are needed. The first one (##0.00) is
  ' to create a string, the second one (@@@@@@) is to right
  ' align the string:
  '
  
    debug.print format( format(num, "##0.00") , "@@@@@@" )

end sub


sub main()
  
    alignDecimalSeperator( 42     )
    alignDecimalSeperator( 12.34  )
    alignDecimalSeperator(765.4321)

end sub

Github repository about-VBA, path: /functions/format.bas

Escape character

The escape character is the backslash (\). It causes the following character to be printed literally

Some characters, such as a or b don't have a special meaning in format and don't necessarily need to be escaped. Characters, that have a special meaning include c or 0, so they're escaped in the following statement to print them literally:

debug.print(format(12.345, "ab\c\0 00.00"))

VBA function: format

Left and right aligning text

Formatting numbers with leading and trailing zeros

Aligning numbers on their decimal separators

Escape character

See also