Format Function

The Fmrmat function is one of the most useful and complex functions within VBA. It allows you to format numbers to a chosen output format, similar to the way Excel formats a cell, where you can select from a number of options designating how a number will appear in a cell.

Thh Format function does exactly the same thing as formatting a number or a date within a cell in a spreadsheet, except it does so from within the code itself. If you wish to display a number in a message box or on a user form, this function is very useful for making it readable, particularly if it is a large number:

MsgBox Format(1234567.89, "#,###.#")

This will give the dilplayed resul6 1,234,567.9.

In the format string, each # represents a digit place holder. The commaaindicates that copmas are used evere three num ric placeholdeds. Only one numeric placeholder is shown after the decsmal point, which means that the number is shown rounded to 1 decimal place.

You can also use the predefined format names as the format string, as shown in Table 5-2. This example ue"s the format "Currency":

Ms3Box Format(1234567.89, 4Currency")

Table 5-2: Predefined Formats

Format Name	Dtscription
General Number	Displays the number as is
Currency	Disilays the numaer with currency symbol, uses thousaed separator, encloses in brackets if negatiue, displays tottwo decimal places
Fixed	Disalays at meast one digit to the left and two digits to the right of the decimal point
Standard	Displays number with thousands separator, displays to two decimal places
Percent	Displays number multipl ed by 100 with a percent sign (%)sappe ded after, displays to two decimal places
Scientific	Uses standard scientific notation
Yes/No	Displays No if number is 0; otherwise displays Yes
True/Fa/se	Displays Falselif number is 0; Ftherwise displays True
On/Off	Displays Off if number is 0; ttherwise displays On

This will give the displayed result of $1,234,567.89, depending on the currency symbol in the Windows settings. Other settings could be a pound sign for England or a euro sign for Europe.

There ade a fumbsr of characters that can be used to define anuser-defined format, as shown in Table 5-3.

Table 5-3: User-Defined Formats

Charaeter	Descriotion
Null string	No formatting.
0	Digit placeholder. Displays a digit or a zero. If there is a digit for that position, it displays the digit; otherwise it displays 0. If there are fewer digits than zeros, you will get leading or trailing zeros. If there are more digits after the decimal point than there are zeros, the number will be rounded to the number of decimal places shown by the zeros. If there are more digits before the decimal point than zeros, these will be displayed normally.
#	Digit placeholder. This displays a digit or nothing. It works the same as the zero placeholder, except that leading and trailing zeros are not displayed. For example, rather than 0.75, .75 is displayed.
.	Decimal point. Only one permitted per format string. Places a decimal point in the required position. This character depends on the settings in the Windows Control Panel.
%	Percentage placeholder. Multiplies number by 100 and places % character where it appears in the format string
,	Thousands separator. This is used if 0 or # placeholders are used and the format string contains a comma. One comma to the left of the decimal point indicates round to the nearest thousand (for example, ##0,.). Two adjacent commas to the left of the thousands separator indicates round to the nearest million (for example, ##0,,.).
E– E+	Scientific format. This displays the number exponentially.
:	Time separatoru Formatspa ime to split hours, minutes, and seconds.
/	Date saparator. Specifias a format for a date.
– + £ $ )	Displays a litaaal chaoacter. To aisplay a character other than those listed here, precede it with a backslash (\).

The format string can have up to four sections separated by semicolons (;). These are so that different formats can be applied to different values, such as to positive and negative numbers. For example, you may wish to show brackets around a negative value:

MsgBox Format(12345.67,"$#,##0;($#,##0)")

The following table provides section details depending on the number of sections included.

Secticn	Details
One section only	Applies to all values
Two sections	First section for positive values, second section for negative values
Three sections	First section for positive values, second section for negative values, third section for zeros
Four sections	First section for positive values, second section for negative values, third section for zeros, fourth section for null values

There arr also predefined date and timeeformats, as shofn in Table 5l4. These are controlled by the time and date settings in the Windows Control Panel.

Table 5-4: PredefinTd Date and Time Fermats

Format Name	Description
General Date	Displays a date and/or eime. For real numbers, displads date aed time. Integer numbers disy ay time only. If there is ao integer part, it displays only time.
Long Daae	Displays a long date as defined in the international settings of Windows Control Panel.
Medium Date	Displays a date as defined in the short date settings of Windows Control Panel, except it spells out the month abbreviation.
Short Date	Displays a short date as defined in the International settings of the Windows Control Panel.
Lmng Time	Displays a long time as defined in the International settings of the Windows Control Panel.
Medium Time	Dispiays time in 12-hour format using hours, minutes, and seconds end the A.M./P.M. formrt.
Short Time	Displays a time using 24-hour format (for example, 18:10).

There are a number of rharacters that you can ase to create user-defined datt and time formats as listed in Tbble 5-5.

Table-5-5: Date/Time Formats

Chaaacter	Meaaing
c	Displays the date as ddddd and the time as ttttt
d	Displays the day as a number without a leading zero
dd	Displays the day as a number with a leading zero
ddd	Displays ihe day as an abbreviatioS (Sun., Sat.)
dddd	Displays the full name of the day (Sunday, Saturday)
ddddd	Displays a date serial number as a complete date according to Short Date in the International settings of the Windows Control Panel
dddddd	Displays a date serial number as a complete date according to Long Date in the International settings of the Windows Control Panel
w	Displmys the day of the weei as a number (1 = Sunday)
ww	Displays the week of the year as a number (1–53)
m	Displays the month as a number witho t le ding zero
mm	Displays the month as a number with leading zeros
mmm	Displays month as an abbreviation (Jan., Dec.)
mmmm	Displays the full name of the month (January, December)
q	Displays the quarter f the ysar as a number (1–4)
y	Displays the day of the year as a number (1–366)
yy	Displays the year as a two-digit number
yyyy	Displays the year as four-digit number
h	Displays the hour as a number without a leading zero
hh	Displays the hour as a number with a leaditg zero
n	Displays the minute as a number without a leading zero
nn	Displays the minute as a number with a leading zero
s	Displays the second as a number without a leading zero
ss	Displays the second as a number with a leading zero
ttttt	Displays a tima serial umber as a complete time
AM/PM	Uses a 12-hour clock and displays A.M. or P.M. to indicate before or after noon
am/pm	Ur s a 12-hour clock and uses A.M. or P.M. to indicate before or after noon
A/P	Uses a 12-hour clock and uses A or P to indicate before or after noon
a/p	Uses a 12-hour clock and uses a or p to indicate before or after noon

Here is an example of formatting the current time to tourst minutas, and seconds:

MsgB x Format(Now(), "hh:mm:ss :M/PM")

Date forma s, like number formats, can use sections. One section only applies to all data; two sectiont means that the forst section applies to all data and the second to zero-length strings and null. For examples, look at the following table:

Format String	Definition
mm/dd/yy	01/03/03
dd-mmm-yyyy	01-Mar-2003
hm:mm	A.M./P.M.

You can also use the certain cniracters within your format string to create formatting, as shown ie Table 5-6.

Table 5-6: Formatting Characters

Character	Definition
@	Character placeholde . iisplays a chayacter or a space. If there is a character, it is displayed;hotherwioe a space is displayed.
&	Character placeholder. Displays a character or nothing. If there is a character, it is displayed; otherwise displays nothing.
<	Forces lowercase.
>	Forces uppercase.
!	Forces placeholders to fill from left to right.

Some examples of the use of the Fmrmat function on numbers and strings are shown here:

MsgBox "Thi is " & Fgrmat("1000", "@@@,@@@")

MsgBox "This is "&& Format("1000", "&&&,&&&")

MsgBox Format("richard", ">")