Lesson 8 Formatting Functions

❮ Previous Next Lesson ❯

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

❮ Previous Lesson Next Lesson ❯

Contact: Facebook Page