|
Formatting Functions
The Format function will format a number with any amount of decimal places, leading zeros, trailing zeros, and round it at the same time. Another big use of Format is to convert numbers into date and time expressions. The function takes two arguments, the number to format and a string, which indicates instructions on what kind of formatting, is to be done. These instructions can be built-in named formats or any combination of special characters. The only way to really learn what this function can do is to look at a few examples and experiment on your own.
Formatting numbers with named formats |
Expression |
Result |
Format(35988.3708, "general number") |
35988.3708 |
Format(35988.3708, "currency") |
$35,988.37 |
Format(-35988.3708, "currency") |
($35,988.37) |
Format(35988.3708, "fixed") |
35988.37 |
Format(1, "fixed") |
1.00 |
Format(35988.3708, "standard") |
35,988.37 |
Format(1, "standard") |
1.00 |
Format(0.35988, "percent") |
35.99% |
Format(0, "Yes/No") |
No |
Format(0.35988, "Yes/No") |
Yes |
Format(0, "True/False") |
False |
Format(342, "True/False") |
True |
Format(0, "On/Off") |
Off |
Format(-1, "On/Off") |
On |
Formatting numbers with special characters |
Expression |
Result |
Format(35988.3708, "00000.0") |
35988.4 |
Format(35988.3708, "0000000.0") |
0035988.4 |
Format(35988.3708, "00,000.00000") |
35,988.37080) |
Format(6.07, "0.###") |
6.07 |
Format(6.07, "0.000##") |
6.070 |
Format(143879, "#,###,###.00") |
143,879.00 |
A "0" placeholder in your format string will force a character to appear in that position regardless of the value, or in other words, give you leading and/or trailing zeros. This is very useful for formatting numbers to a certain number of decimal places, for instance a baseball batting average.
Dim vAverage As Variant
Dim iAtBats As Integer
Dim iHits As Integer
iAtBats% = 128: iHits% = 37
'Returns .289
vAverage = Format(iHits% / iAtBats%, ".000")
A "#" placeholder will only display a number in that position if it isn't a leading or trailing zero. We might change the format string of a baseball average to...
vAverage = Format(iHits% / iAtBats%, "#.000")
...to account for the rare occasion when a player is batting 1.000
Put commas and decimal points in the appropriate places within the format string.
To display any literal character(s) within the formatted output, precede the character(s) with a backslash within the format string. The backslash is not displayed.
Formatting numbers with embedded characters |
Expression |
Result |
Format(45, "\[00\]") |
[45] |
Format(642, "\£000.00") |
£642.00 |
Format(99, "00\¢") |
99¢ |
Format(8, "#0\).") |
8). |
The Format function can even make If-Then-Else type decisions. You can send up to four different format strings, separated by semi-colons, to the function, where your number will be formatted according what kind of value it is- positive, negative, zero, or null. You cannot use named formats with this method.
When using a two-section format string, the first section applies to positive values (including zero) and the second section to negative values. In a three-section format string, the third section is for zero values, thereby releasing the first section from handling zero. With a four-section format string, the fourth section is for null values.
iResult% = Format(iNumber%, "##.00;\(##.00\);0;\E\r\r\o\r")
Dialog Boxes
Formatting Date and Time
Procedures
|
