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 Access 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 placeholder. 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 one decimal place.
| Format Name | Description |
|---|---|
| General Number | Display the number as is. |
| Currency | Display the number with currency symbol. Use thousand separator. Enclose in brackets if negative. Display to two decimal places. |
| Fixed | Display at least one digit to the left and two digits to the right of the decimal point. |
| Standard | Display number with thousand separator. Display to two decimal places. |
| Percent | Display number multiplied by 100 with a percent sign (%) appended after. Display to two decimal places. |
| Scientific | Use standard scientific notation. |
| Yes/No | Display No if number is 0; otherwise, display Yes. |
| True/False | Display False if number is 0; otherwise, display True. |
| On/Off | Display Off if number is 0; otherwise, display On. |
You can also use the predefined format names as the format string, as shown in Table-2. This example uses the format "Currency":
MsgBox Format(1234567.89, "Currency")
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.
A number of characters can be used to define a user-defined format, as shown in Table-3. The format string can have up to four sections separated by semicolons (;). This is so different formats can be applied to different values, such as to positive and negative numbers. For example, you may wish to show brackets/parentheses around a negative value:
MsgBox Format(-12345.67,"$#,##0;($#,##0)")
The following table provides section details depending on the number of sections included.
| Section | Detail |
|---|---|
| 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 |
Predefined date and time formats can also be used, as shown in Table-4. These are controlled by the time and date settings in the Windows Control Panel.
Table-3 User-Defined Formats| Character | Description |
|---|---|
| Null String | No formatting. |
| 0 | Digit placeholder. Displays a digit or a zero. If there is a digit for that position, then 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, then the number is 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 preceding zero placeholder, except that leading and trailing zeros are not displayed. For example, 0.75 would be displayed using zero placeholders, but this would be .75 using # placeholders. |
| .Decimal point. | Only one permitted per format string. 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. |
| , | Thousand 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 means to round to the nearest thousand (e.g., ##0,). Two adjacent commas to the left of the thousand separator indicate rounding to the nearest million (e.g., ##0,,). |
| E- E+ | Scientific format. This displays the number exponentially. |
| : | Time separator-used when formatting a time to split hours, minutes, and seconds. |
| / | Date separator-this is used when specifying a format for a date. |
| - + £ $ ( ) | Displays a literal character. To display a character other than listed here, precede it with a backslash (\). |
| Format Name | Description |
|---|---|
| General Date | Display a date and/or time. For real numbers, display date and time. Integer numbers display time only. If there is no integer part, then display only time. |
| Long Date | Displays a long date as defined in the international settings of the Windows Control Panel. |
| Medium Date | Displays a date as defined in the short date settings of the 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 a 12-hour format using hours, minutes, and seconds and the AM/PM format. |
| Short Time | Displays a time using 24-hour format (e.g., 18:10). |
You can use a number of characters to create user-defined date and time formats, as listed in Table-5. The following is an example of formatting the current time to hours, minutes, and seconds:
MsgBox Format(Now(), "hh:mm:ss AM/PM")
Table-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-52). |
| m | Displays the month as a number without a 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. |
| yyy | Displays the year as a 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 AM or PM to indicate before or after noon. |
| am/pm | Uses a 12-hour clock and uses am or pm 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. |
| 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. Display a character or nothing. If there is a character, display it; otherwise, display nothing. |
| < | Force lowercase. |
| > | Force uppercase. |
| ! | Force placeholders to fill from left to right. |
A number of characters can be used to create a user-defined format
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 certain characters within your format string to create formatting, as shown in Table-6.
Some examples of using the Format function on numbers and strings are shown here:
MsgBox "This is " & Format("1000", "@@@,@@@")
MsgBox "This is " & Format("1000", "&&&,&&&")
MsgBox Format("martin", ">")