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.NumberFormatproperty 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
- Excel Template for Time Sheet: How to Make One
- Excel Template for Tracking Monthly Expenses
- Excel Template for Timesheet Job Tracking
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Delete Row Based on Cell Value in ExcelTo delete Excel rows when a cell contains a chosen value, loop through the worksheet from the bottom…
- VBA Add Border to a Range: Code, Styles, and FixesTo add borders to a range in Excel VBA, set the range’s Borders properties. For example, Range("B2:F10").Borders.LineStyle =…
- Excel VBA List Worksheet Names: Methods and ExamplesTo list worksheet names with Excel VBA, loop through the workbook’s Worksheets collection and read each sheet’s Name…
- Range vs Cell in VBA: Differences, Syntax, and ExamplesIn Excel VBA, a cell is represented by a Range object: use Range to refer to an address…
- VBA Macro to Send Email From Excel: Setup and CodeTo send email from Excel with VBA, automate classic Outlook for Windows: place the recipient, subject, message, and…
- Excel VBA Date Subtraction: Get the Number of DaysIn Excel VBA, subtract one date from another with days = endDate - startDate; for dates without times,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.