Excel 2010 VBA Lesson 8: Formatting Functions

[Lesson 7]<<[Table of Contents]>>[Lesson 9]

Data in the previous lesson were presented fairly systematically through the use of functions like Int, Fix, and Round. However, the Format function improves the presentation of the numeric values. There are two types of Format function, one of them is the built-in format while another one is the user-defined function.

8.1 Predefined Format function

The syntax of the predefined Format function is

Format (n, “style argument”)

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

Table 8.1


The output is shown in Figure 8.1

Figure 8.1

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

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

The output is shown in Figure 8.2

Figure 8.2

[Lesson 7]<<[Table of Contents]>>[Lesson 9]