Excel VBA Format Command: How to Use It

VBA & macros · Updated November 22, 2026

The Excel VBA Format command converts a value into a string with a specified number or date format, and you use it like Format(expression, format) in a macro. For example, Format(1234.5, "#,##0.00") returns the string “1,234.50”, and Format(Date, "yyyy-mm-dd") returns something like “2025-04-14”. The command is a built-in VBA function, not a method of a Range object, so it works on values in variables, cells, or user input before you write them to a worksheet.

What the Format Command Actually Does

The Format function in VBA is part of the Visual Basic language (used across Office applications) and is similar to the TEXT function in Excel formulas. It takes a value (numeric, date, or string) and returns a string that is formatted according to a pattern you supply. The original value is not changed; the function only creates a formatted copy.

Key points:

  • It returns a String data type, even if the input was a number or date.
  • It is often used to display values in a specific way inside a message box, a user form, or a text file.
  • It does not change the number format of a cell. To format a cell’s display, you use the Range.NumberFormat property instead.

For example, if you run MsgBox Format(0.5, "0%"), the message box shows “50%”. The underlying value 0.5 remains unchanged.

How to Write the Format Command Syntax

The basic syntax is:

Format(expression, [format])

Where:

  • expression – the value you want to format (a number, date, or string).
  • format – a string that defines the format pattern. If omitted, the function uses the system’s general format.

Here is a simple macro that formats a number and writes it to a cell:

Sub FormatExample()
    Dim myValue As Double
    Dim formattedText As String
    
    myValue = 1234.567
    ' Format with two decimals and a thousands separator
    formattedText = Format(myValue, "#,##0.00")
    
    ' Put the formatted string into cell A1
    Range("A1").Value = formattedText
    
    ' Show the result in a message box
    MsgBox formattedText
End Sub

When you run this, cell A1 contains the text “1,234.57” (note that VBA rounds to two decimals). The macro does not change the number format of the cell; it writes a string.

Important: Before running any macro, save a copy of your workbook. Macros cannot be undone with Ctrl+Z.

Common Format Patterns for Numbers and Dates

You can use predefined named formats or custom patterns. Named formats are simpler but limited to what VBA knows.

Predefined named formats:

  • Format(1234.5, "General Number") → “1234.5”
  • Format(1234.5, "Currency") → “$1,234.50” (depends on system locale)
  • Format(0.25, "Percent") → “25.00%”
  • Format(12345, "Scientific") → “1.23E+04”
  • Format(1234.5, "Standard") → “1,234.50”

Custom number format patterns:

  • "0" – digit placeholder; shows at least one digit, e.g., Format(5, "00") → “05”
  • "#" – digit placeholder; optional, e.g., Format(5, "#") → “5”
  • "." – decimal point
  • "," – thousands separator
  • "%" – multiplies by 100 and adds percent sign
  • "0.0E+00" – scientific notation

Date and time patterns:

  • Format(Date, "yyyy-mm-dd") → “2025-04-14” (example)
  • Format(Now, "hh:mm AM/PM") → “03:45 PM”
  • Format(Date, "dddd, mmmm d, yyyy") → “Monday, April 14, 2025”
  • Format(Date, "mmm d") → “Apr 14”

Note: The actual output for dates depends on your system’s regional settings if you use named formats, but custom patterns like "yyyy-mm-dd" are consistent.

How the Format Command Differs from Cell NumberFormat

A common confusion is between the VBA Format function and the NumberFormat property of a Range object. They serve different purposes:

  • Format function – returns a string; used for display in VBA (message boxes, user forms, file output).
  • Range.NumberFormat – sets the display format of a cell, but the cell still contains the raw value. For example, Range("A1").NumberFormat = "0.00%" makes the cell show 50.00% if the value is 0.5, but the cell value remains 0.5.

If you want to write a formatted string to a cell, you can use Range.Value = Format(value, pattern) – but then the cell contains text, not a number. That can break calculations. Usually, you want to keep the numeric value and use NumberFormat for display.

Using Format with User Input and Concatenation

You often need to format values when building strings, like in a report or a message. Example:

Sub ShowFormatted()
    Dim sales As Double
    Dim region As String
    sales = 12345.678
    region = "West"
    
    ' Build a sentence with a formatted number
    MsgBox "Sales for " & region & ": " & Format(sales, "$#,##0.00")
End Sub

This shows:

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.