Excel 2010 VBA Lesson 8: Formatting Functions

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

Formatting the output is a very important for Excel 2010 VBA  macro programming so that the data can be presented systematically and clearly to the users. Data in the previous lesson were presented fairly systematically through the use of functions like Int, Fix, and Round.The Format function is a very powerful formatting function in Excel  2010 VBA. It can display the numeric values in various forms. There are two types of Format function, one of them is the built-in or predefined format while another one can be defined by the users.

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]