Excel VBA 2010 Lesson 8: Formatting Functions

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

Formatting output is a very important part of Excel VBA 2010 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 offunctions like Int, Fix and Round. However, to have better control of the output format, we can use a number of formatting functions in Excel VBA 2010.

The Format function is a very powerful formatting function in Excel VBA 2010. 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 follows 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]

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn