Excecl VBA Classic Excel VBA 2010 Excel VBA 365 Excel VBA Examples About Us

Lesson 8 Formatting Functions


There are two types of Format function, one of them is the predefined(built-in) format function and the other one is the user-defined function.

8.1 The Predefined Function

The syntax of the predefined Format function is:

Format (n, "style argument")

The list of style arguments are listed in Table 8.1

Table 8.1 Style Arguments
Style Argument Explanation Example
General Number Display numbers without separators between thousands Format(8972.234,"General Number")=8972.234
Fixed Display numbers without separators between thousands and rounds it up to 2 decimal places Format(8972.234,"Fixed")=8972.23
Standard Display numbers with separators between thousands and rounds it up to 2 decimal places Format(6648972.265, "Standard")=6,648,972.27
Currency Display numbers with dollar sign in front, has separators between thousands and rounds it up to 2 decimal places Format(6648972.265, "Currency")=$6,648,972.27
Percent Converts numbers to the percentage form and display a % sign and rounds it up to 2 decimal places Format(0.56324, "Percent")=56.32%

8.2 User-defined Format function

The syntax of the user-defined Format function is

Format (n, "user’s format")

Although it is known as user-defined format, we still need to follow certain formatting styles. Examples of user-defined formatting style are listed in Table 8.2

Table 8.2 User-defined Format function
Example Explanation Output
Format(781234.57,"0") Round to a whole number without separators between thousands 781235
Format(781234.57,"0.0") Round to 1 decimal place without separators between thousands 781234.6
Format(781234.576,"0.00") Round to 2 decimal place without separators between thousands 781234.58
Format(781234.576,"#,##0.00" Round to 2 decimal place with separators between thousands 781,234.58
Format(781234.576,"$#,##0.00" Shows dollar sign and round to 2 decimal place with separators between thousands Format(0.56324, "Percent")=56.32%
Format(0.576,"0%" Convert to percentage form without decimal places 58%
Format(0.5768,"0.00%" Convert to percentage form with 2 decimal places 57.68%

Example 8.2

Private Sub CommandButton1_Click()
 Cells(1, 1) = Format(781234.57, “0″)
 Cells(2, 1) = Format(781234.57, “0.0″)
 Cells(3, 1) = Format(781234.576, “0.00″)
 Cells(4, 1) = Format(781234.576, “#,##0.00″)
 Cells(5, 1) = Format(781234.576, “$#,##0.00″)
 Cells(6, 1) = Format(0.576, “0%”)
 Cells(7, 1) = Format(0.5768, “0.00%”)
End Sub
Figure 8.1 The Output





Copyright ® 2008 Dr.Liew Voon Kiong . All rights reserved   [Privacy Policy]

Contact: Facebook Page