# Lesson 9: The Format Function

The Format function in Excel VBA can display the numeric values in various formats . There are two types of Format functions, one of them is the built-in Format function while another is the user-defined Format function.

### 9.1 Built-in Format function

The syntax of the built-in Format function is

Format (n, "style argument")

where n is a number and the list of style arguments are listed in Table 9.1

#### Table 9.1

Style argument Explanation Example
General Number To display the number without having separators between thousands. Format(8972.234, "General Number")=8972.234
Fixed To display the number without having separators between thousands and rounds it up to two decimal places. Format(8972.2, "Fixed")=8972.23
Standard To display the number with separators or separators between thousands and rounds it up to two decimal places. Format(6648972.265, "Standard")= 6,648,972.27
Currency To display the number with the dollar sign in front has separators between thousands as well as rounding it up to two decimal places. Format(6648972.265, "Currency")= \$6,648,972.27
Percent Converts the number to the percentage form and displays a % sign and rounds it up to two decimal places. Format(0.56324, "Percent")=56.32 %

#### Example 9.1

Private Sub CommandButton1_Click()

Cells(1, 1) = Format(8972.234, "General Number")

Cells(2, 1) = Format(8972.234, "Fixed")

Cells(3, 1) = Format(6648972.265, "Standard")

Cells(4, 1) = Format(6648972.265, "Currency")

Cells(5, 1) = Format(0.56324, "Percent")

End Sub

### 9.2 The 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 follows certain formatting styles. Examples of user-defined formatting style are listed in Table 9.2

#### Table 9.2

Format Description Output
Format(781234.576,"0") Rounds to whole number without separators between thousands  781235
Format(781234.576,"0.0") Rounds to 1 decimal place without separators between thousands  781234.6
Format(781234.576,"0.00") Rounds to 2 decimal place without separators between thousands  781234.58
Format(781234.576,"#,##0.00") Rounds to 2 decimal place with separators between thousands  781,234.58
Format(781234.576,"\$#,##0.00") Displays dollar sign and Rounds to 2 decimal place with separators between thousands  \$781,234.58
Format(0.576,"0%") Converts to percentage form without decimal place  58%
Format(0.5768,"0%") Converts to percentage form with two decimal places  57.68%

#### Example 9.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

The output is shown in Figure 9.2