Excel VBA Lesson 9: Formatting Functions

<<Lesson 8>> [Contents] <<Lesson 10>>

We need to format the output of an Excel VBA code so the users can understand it easily. In the previous lesson, we have learned a few basic formatting functions like Int, Fix and Round. However,  in order to have better control of the output format, we can the format function in Excel VBA. The Format function is a very useful function as 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.

9.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 9.1

Table 9.1

Example 9.1

Private Sub CommandButton1_Click()
Cells(1, 1) = Format(8972.234, “General Number”)
Cells(2, 1) = Format(8972.2, “Fixed”)
Cells(3, 1) = Format(6648972.265, “Standard”)
Cells(4, 1) = Format(6648972.265, “Currency”)
Cells(5, 1) = Format(0.56324, “Percent”)
End Sub

The Output is shown in Figure 9.1

                       Figure 9.1

9.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 9.2

Table 9.2

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

 Figure 9.2

<<Lesson 8>> [Contents] <<Lesson 10>>