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")
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.
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.
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.
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.
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", ">")