Applying Number Formats in Excel VBA

The following VBA procedure shows how to apply custom number formats to cells, including formats for currency, dates, and percentages, using the NumberFormatLocal property.

VBA Example: Formatting Numbers, Dates, and Percentages

Sub CellFormatNumbers()
    ThisWorkbook.Worksheets("Sheet1").Activate
    ' Number with thousands separator, 2 decimals, and currency symbol
    Range("A1:A3").NumberFormat = "#,##0.00 €"
    ' Date: full weekday, "the", day, month, year
    Range("A4:A5").NumberFormat = "dddd, ""the"" dd. mmmm yy"
    ' Custom format: value followed by the word "days"
    Range("A6").NumberFormat = "0 ""days"""
    ' Percentage with 2 decimal places
    Range("A7").NumberFormat = "0.00 %"
End Sub

Explanation of the Procedure:

ThisWorkbook.Worksheets(« Sheet1 »).Activate
➡ Activates the worksheet named « Sheet1 » that belongs to the same workbook where the macro is saved (ThisWorkbook).
Make sure the sheet name is spelled exactly as it appears in Excel, or this line will trigger Run-time error ‘9’.

Range(« A1:A3 »).NumberFormat = « #,##0.00 € »
➡ Formats cells A1 to A3 as numbers:

• Thousands separator (,),
• Two decimal places,
• Euro currency symbol (€),

Example output: 1,629.90 €.

Range(« A4:A5 »).NumberFormat = « dddd, «  »the » » dd. mmmm yy »
➡ Formats dates in A4 and A5 to:

• Full weekday name (e.g., « Tuesday »),
• The word « the » (in quotation marks),
• Day number,
• Full month name,
• Two-digit year.

Example: Tuesday, the 31. March 20.

Range(« A6 »).NumberFormat = « 0 «  »days » » »
➡ Formats the value in cell A6 to display as a number followed by the word days:

Example: 142 days.

Range(« A7 »).NumberFormat = « 0.00 % »
➡ Formats A7 as a percentage with two decimal places:

• Example: 0.125 → 12.50 %.

Summary
This macro:

• Assumes data is in « Sheet1 »,
• Applies professional formatting for currency, dates, durations, and percentages,
• Helps make spreadsheet values easier to read and present.