What are .NumberFormat Options In Excel VBA?

Mastering .NumberFormat Options in Excel VBA

Explore the extensive capabilities of the .NumberFormat property in Excel VBA to precisely control how numbers, dates, and times are displayed in your spreadsheets.

The .NumberFormat property in Excel VBA is a powerful tool that allows you to define how values are displayed in cells without changing their underlying data type. Whether you're working with currencies, percentages, dates, times, or custom formats, understanding this property is crucial for creating professional and readable spreadsheets. This article will guide you through the various options and provide practical examples to help you master number formatting in your VBA projects.

Understanding the .NumberFormat Property

The .NumberFormat property of a Range object accepts a string that dictates the display format. This string can be one of Excel's built-in formats or a custom format string you define. It's important to remember that formatting only affects the appearance of the cell's value, not the value itself. For example, if a cell contains the number 0.5 and you apply a percentage format, it will display as 50%, but its underlying value remains 0.5 for calculations.

flowchart TD
    A[Start] --> B{Select Range}
    B --> C[Assign Format String to .NumberFormat]
    C --> D{Is it a Built-in Format?}
    D -->|Yes| E[Excel Applies Predefined Format]
    D -->|No| F[Excel Interprets Custom Format String]
    E --> G[End]
    F --> G[End]

Flowchart of applying .NumberFormat in VBA

Common Built-in Number Formats

Excel provides a wide array of built-in number formats that cover most common scenarios. These can be applied by using their corresponding string names or codes. While many formats are self-explanatory, some have specific nuances. Here are some frequently used built-in formats:

Sub ApplyBuiltInFormats()
    ' General format (default)
    Range("A1").Value = 12345.678
    Range("A1").NumberFormat = "General"
    
    ' Number with two decimal places and comma separator
    Range("A2").Value = 12345.678
    Range("A2").NumberFormat = "#,##0.00"
    
    ' Currency format (local currency symbol)
    Range("A3").Value = 12345.67
    Range("A3").NumberFormat = "Currency"
    
    ' Percentage format
    Range("A4").Value = 0.25
    Range("A4").NumberFormat = "0.00%"
    
    ' Date format (short date)
    Range("A5").Value = Date
    Range("A5").NumberFormat = "Short Date"
    
    ' Time format
    Range("A6").Value = Time
    Range("A6").NumberFormat = "h:mm:ss AM/PM"
    
    ' Text format (treats input as text)
    Range("A7").Value = "00123"
    Range("A7").NumberFormat = "@"
End Sub

Examples of applying common built-in number formats.

Crafting Custom Number Formats

When built-in formats aren't sufficient, custom number formats offer unparalleled flexibility. A custom format string can have up to four sections, separated by semicolons, defining formats for positive numbers, negative numbers, zero values, and text, respectively. If only one section is provided, it applies to all numbers. If two sections, the first is for positive and zero, the second for negative. If three, positive; negative; zero. If four, positive; negative; zero; text.

Structure of a custom number format string.

Here are some key characters used in custom number formats:

Numeric Placeholders

0    - Displays insignificant zeros if a number has fewer digits than the zeros in the format.
#    - Displays only significant digits. Does not display insignificant zeros.
?    - Adds spaces for insignificant zeros on either side of the decimal point so that decimal points align.
.    - Decimal point.
,    - Thousands separator.

Date and Time

d    - Day of the month (1-31)
dd   - Day of the month (01-31)
ddd  - Abbreviated day of the week (e.g., "Mon")
dddd - Full day of the week (e.g., "Monday")
m    - Month (1-12) or minute (0-59)
mm   - Month (01-12) or minute (00-59)
mmm  - Abbreviated month (e.g., "Jan")
mmmm - Full month (e.g., "January")
yy   - Year (last two digits)
yyyy - Full year (four digits)
h    - Hour (0-23 or 1-12 with AM/PM)
hh   - Hour (00-23 or 01-12 with AM/PM)
AM/PM - Displays AM or PM based on the time value.

Text and Special Characters

@    - Text placeholder. If the cell contains text, it's displayed where the @ appears.
"text" - Displays the literal text enclosed in double quotes.
\    - Escapes the next character, displaying it literally.
*    - Repeats the next character to fill the column width.
_    - Skips the width of the next character, useful for alignment.
[Color] - Applies a color to the formatted value (e.g., [Red]).
[Condition] - Applies format only if a condition is met (e.g., [>100]).

Sub ApplyCustomFormats()
    ' Positive; Negative; Zero; Text
    Range("B1").Value = 123.45
    Range("B2").Value = -67.89
    Range("B3").Value = 0
    Range("B4").Value = "Error"
    Range("B1:B4").NumberFormat = "$#,##0.00;[Red]-$#,##0.00;""Zero"";@""
    
    ' Date and Time
    Range("C1").Value = Now
    Range("C1").NumberFormat = "dddd, mmmm dd, yyyy h:mm AM/PM"
    
    ' Percentage with condition
    Range("D1").Value = 0.85
    Range("D2").Value = 1.15
    Range("D1:D2").NumberFormat = "[Green]0.0%;[Red]0.0%"
    
    ' Phone number format
    Range("E1").Value = 1234567890
    Range("E1").NumberFormat = "(000) 000-0000"
End Sub

Examples of applying various custom number formats.

Practical Application: Formatting a Report Column

Let's put this into practice by formatting a column of data in a hypothetical report. We'll assume column A contains product IDs (text), column B contains sales figures (currency), and column C contains dates (short date).

1. Set up Sample Data

Populate cells A1:C3 with sample data to demonstrate the formatting. For example: Range("A1").Value = "PROD001" Range("B1").Value = 1500.75 Range("C1").Value = #1/15/2023#

2. Format Product IDs (Text)

Apply the text format to column A to ensure product IDs are treated as text, even if they look like numbers. Columns("A").NumberFormat = "@"

3. Format Sales Figures (Currency)

Apply a currency format with two decimal places and a thousands separator to column B. Columns("B").NumberFormat = "$#,##0.00"

4. Format Dates (Custom Date)

Apply a custom date format to column C to display dates as 'Month Day, Year'. Columns("C").NumberFormat = "mmm dd, yyyy"

Sub FormatReportColumns()
    ' Clear existing formats for demonstration
    Cells.ClearFormats
    
    ' Sample Data
    Range("A1").Value = "PROD001": Range("B1").Value = 1500.75: Range("C1").Value = #1/15/2023#
    Range("A2").Value = "PROD002": Range("B2").Value = 250.5: Range("C2").Value = #2/28/2023#
    Range("A3").Value = "PROD003": Range("B3").Value = 3000: Range("C3").Value = #3/1/2024#
    
    ' Format Product IDs as Text
    Columns("A").NumberFormat = "@"
    
    ' Format Sales Figures as Currency
    Columns("B").NumberFormat = "$#,##0.00"
    
    ' Format Dates as 'Month Day, Year'
    Columns("C").NumberFormat = "mmm dd, yyyy"
    
    MsgBox "Columns A, B, and C have been formatted.", vbInformation
End Sub

VBA code to format report columns using different NumberFormat options.