Excel VBA Lesson 9: Mastering the Format Function

❮ Previous Lesson Next Lesson ❯

The Format function in Excel VBA is essential for displaying numeric values in various professional formats. This lesson covers both built-in and custom formatting options to enhance your data presentation.

9.1 Built-in Format Function

The syntax for the built-in Format function is:

Format(expression, "style argument")

Where expression is the numeric value and style argument specifies the display format (see Table 9.1 below).

Table 9.1: Built-in Format Styles
Style Argument	Description	Example	Best Used For
General Number	Displays number without thousand separators	Format(8972.234, "General Number") → 8972.234	Raw numeric data display
Fixed	Rounds to 2 decimal places without thousand separators	Format(8972.2, "Fixed") → 8972.23	Precise decimal values
Standard	Includes thousand separators and rounds to 2 decimal places	Format(6648972.265, "Standard") → 6,648,972.27	Financial reports
Currency	Adds dollar sign, thousand separators, and 2 decimal places	Format(6648972.265, "Currency") → $6,648,972.27	Monetary values
Percent	Converts to percentage with % sign and 2 decimal places	Format(0.56324, "Percent") → 56.32%	Ratios and proportions

Example 9.1: Built-in Format Demonstration

Private Sub CommandButton1_Click()
    ' Display different built-in formats in column A
    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

Excel VBA Built-in Format Function Results — Figure 9.1: Output of Built-in Format Examples

9.2 User-Defined Format Function

For custom formatting needs, use the syntax:

Format(expression, "custom format")

Table 9.2 shows common custom format patterns:

Table 9.2: Custom Format Patterns
Format Example	Description	Output	When to Use
Format(781234.576,"0")	Rounds to whole number	781235	Counting whole items
Format(781234.576,"0.0")	1 decimal place	781234.6	Approximate measurements
Format(781234.576,"0.00")	2 decimal places	781234.58	Precise measurements
Format(781234.576,"#,##0.00")	Thousand separators with 2 decimals	781,234.58	Large financial numbers
Format(781234.576,"$#,##0.00")	Currency with thousand separators	$781,234.58	Exact monetary values
Format(0.576,"0%")	Percentage without decimals	58%	Quick ratio display
Format(0.5768,"0.00%")	Percentage with 2 decimals	57.68%	Precise percentage values

Example 9.2: Custom Format Demonstration

Private Sub CommandButton1_Click()
    ' Display various custom formats in column A
    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

Excel VBA Custom Format Results — Figure 9.2: Output of Custom Format Examples

Summary

✅ In This Lesson, You Learned:

Built-in formats provide quick, standardized number formatting
Custom formats offer precise control over number display
Use "0" for whole numbers, "0.00" for decimals
"#,##0.00" adds thousand separators with 2 decimals
Prefix with "$" for currency values
Append "%" to display percentages

🔗 Related Resources

❮ Previous Lesson Next Lesson ❯

Contact: Facebook Page