Format Function

The Format 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 .

The 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 displayed result 1,234,567.9.

In the format string, each # represents a digit place holder. The comma indicates that commas are used every three numeric placeholders. Only one numeric placeholder is shown after the decimal 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 uses the format "Currency":

 MsgBox Format(1234567.89, "Currency") 

Table 5-2: Predefined Formats

Format Name	Description
General Number	Displays the number as is
Currency	Displays the number with currency symbol, uses thousand separator, encloses in brackets if negative, displays to two decimal places
Fixed	Displays at least 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 multiplied by 100 with a percent sign (%) appended after, displays to two decimal places
Scientific	Uses standard scientific notation
Yes/No	Displays No if number is 0; otherwise displays Yes
True/False	Displays False if number is 0; otherwise displays True
On/Off	Displays Off if number is 0; otherwise 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 are a number of characters that can be used to define a user-defined format, as shown in Table 5-3.

Table 5-3: User-Defined Formats

Character	Description
Null string	No formatting.
	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 separator. Formats a time to split hours, minutes, and seconds.
/	Date separator. Specifies a format for a date.
‚ + ‚ $ ( ‚  )	Displays a literal character. To display 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.

Section	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 are also predefined date and time formats, as shown in Table 5-4. These are controlled by the time and date settings in the Windows Control Panel.

Table 5-4: Predefined Date and Time Formats

Format Name	Description
General Date	Displays a date and/or time. For real numbers, displays date and time. Integer numbers display time only. If there is no integer part, it displays only time.
Long Date	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.
Long Time	Displays a long time as defined in the International settings of the Windows Control Panel.
Medium Time	Displays time in 12- hour format using hours, minutes, and seconds and the a.m./p.m. format.
Short Time	Displays a time using 24-hour format (for example, 18:10).

There are a number of characters that you can use to create user-defined date and time formats, as listed in Table 5-5.

Table 5-5: Date/Time Formats

Character	Meaning
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 the day as an abbreviation (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	Displays the day of the week as a number (1 = Sunday)
ww	Displays the week of the year as a number (1 ‚ 53)
m	Displays the month as a number without leading 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 of the year 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 leading 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 time serial number 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	Uses 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 hours, minutes, and seconds:

 MsgBox Format(Now(), "hh:mm:ss AM/PM") 

Date formats, like number formats, can use sections. One section only applies to all data; two sections means that the first 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
hh:mm	a.m. / p.m.

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

Table 5-6: Formatting Characters

Character	Definition
@	Character placeholder. Displays a character or a space. If there is a character, it is displayed; otherwise 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 Format function on numbers and strings are shown here:

 MsgBox "This is " & Format("1000", "@@@,@@@") 
 MsgBox "This is " & Format("1000", "&&&,&&&") 
 MsgBox Format("richard", ">")