Lesson 9: Format Functions

กก[Lesson 8] [Home] [Lesson 10]


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.234, “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

Figure 9.1


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

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

vba2010_fig8.2

Figure 9.2





  Bookmark and Share



กก[Lesson 8] [Home] [Lesson 10]

Copyright ® 2008 Dr.Liew Voon Kiong . All rights reserved 

Contact: admin@excelvbatutor.com [Privacy Policy]