The Format function in Excel VBA displays the numeric values in various formats . There are two types of Format function, one of them is the built-in Format function while another is the user-defined 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
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.2, "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 % |
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
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
Format | Description | Output |
---|---|---|
Format(781234.576,"0") | Rounds to whole number without separators between thousands | 781235 |
Format(781234.576,"0.0") | Rounds to 1 decimal place without separators between thousands | 781234.6 |
Format(781234.576,"0.00") | Rounds to 2 decimal place without separators between thousands | 781234.58 |
Format(781234.576,"#,##0.00") | Rounds to 2 decimal place with separators between thousands | 781,234.58 |
Format(781234.576,"$#,##0.00") | Displays dollar sign and Rounds to 2 decimal place with separators between thousands | $781,234.58 |
Format(0.576,"0%") | Converts to percentage form without decimal place | 58% |
Format(0.5768,"0%") | Converts to percentage form with two decimal places | 57.68% |
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
Copyright ® 2008 Dr.Liew Voon Kiong . All rights reserved [Privacy Policy]
Contact: Facebook Page