Excel VBA Format Cell as Currency: Full Guide

VBA & macros · Updated November 22, 2026

To format a cell as currency in Excel VBA, set the cell’s NumberFormat property to a currency format string, such as Range("A1").NumberFormat = "$#,##0.00". This applies the same currency formatting you see in the Format Cells dialog, and you can also use NumberFormatLocal for locale-specific symbols.

Understanding NumberFormat in VBA

Every cell in Excel has a NumberFormat property that controls how its value is displayed. When you change it via VBA, you’re writing a format code string that Excel interprets. The default is "General". For currency, the typical pattern is a currency symbol, a thousands separator, and decimal places. VBA uses the same format codes as the Excel interface, so anything you type in the Format Cells > Custom box works in code.

Basic Currency Formatting Code

The simplest way to format a single cell or a range is to assign the format string directly. Here’s a minimal example:

Sub FormatCurrency()
    Range("A1").NumberFormat = "$#,##0.00"
End Sub

This sets cell A1 to display numbers like $1,234.56. The # symbols show optional digits, 0 forces a digit even if zero, and the comma separates thousands. You can apply the same to a whole range:

Range("B2:D10").NumberFormat = "$#,##0.00"

Choosing the Right Format String

The format string controls the symbol, decimals, and negative number handling. Common variations:

  • "$#,##0.00" – US dollar, two decimals, thousands separator.
  • "€#,##0.00" – Euro symbol (use the actual character).
  • "$#,##0" – No decimals.
  • "$#,##0.00;[Red]-$#,##0.00" – Negative numbers in red with a minus sign.
  • "0.00" – No currency symbol, just numbers.

You can also use NumberFormatLocal to get the format based on the user’s regional settings. For example, in many European locales, the format might be "#.##0,00 €". Using NumberFormatLocal with that string will display correctly on that system, but it’s safer to use NumberFormat with a hardcoded symbol for consistency.

Using the Built-in Currency Format via VBA

If you want to match Excel’s built-in Currency style exactly, you can use the Style object. The built-in style is named “Currency”. You can apply it to a range like this:

Range("A1:A10").Style = "Currency"

This applies the default currency format from the current workbook’s theme, including the symbol and negative format. It’s equivalent to selecting the range and pressing Ctrl+Shift+$ (or Cmd+Shift+$ on Mac). This approach is often easier because it respects the user’s system settings.

Formatting Cells with Currency in a Loop

Sometimes you need to format cells conditionally, such as only positive values. You can loop through a range and check the value:

Sub FormatConditionalCurrency()
    Dim cell As Range
    For Each cell In Range("A1:A10")
        If cell.Value > 0 Then
            cell.NumberFormat = "$#,##0.00"
        Else
            cell.NumberFormat = "$#,##0.00;[Red]-$#,##0.00"
        End If
    Next cell
End Sub

This shows how to handle different cases. Always declare variables and save a copy of your workbook before running macros, because formatting changes cannot be undone with Ctrl+Z.

Common Problems and Solutions

One frequent issue is that the format appears but the value still shows as text. If the cell contains a number stored as text, formatting won’t change the display. Convert text to numbers first using CDbl() or the Value property. Another problem is using the wrong decimal separator on non-English systems. For example, in Germany, the format string should use a comma for decimals and a period for thousands: "#.##0,00 €". Also, remember that NumberFormat is case-sensitive for some codes, like using 0 vs #.

Alternatives to VBA for Currency Formatting

If you don’t need automation, you can format cells manually: select the range, go to Home > Number > Format Cells (or press Ctrl+1), choose Currency, and set options. For repeated tasks, you can use Excel’s built-in styles or conditional formatting. VBA is best when you need to format many cells dynamically or as part of a larger macro. Another alternative is to use the Text function in a formula, but that changes the cell value to text, which is usually not desirable.

Frequently Asked Questions

How do I format a cell as currency without a symbol? Use NumberFormat = "#,##0.00" – this gives thousands separators and decimals but no currency symbol.

Can I format a cell as currency with negative numbers in parentheses? Yes, use a format like "$#,##0.00;($#,##0.00)". The semicolon separates positive and negative formats.

Why does my VBA currency format show as text? If the cell’s value is a text string, formatting won’t change it. Convert it to a number with CDbl(cell.Value) or set cell.Value = cell.Value * 1 before formatting.

How do I format a cell as currency in Excel for Mac? The VBA code is identical, but the default currency symbol may differ. Use NumberFormatLocal with a locale-specific string if needed.

Does NumberFormat affect the underlying value? No, it only changes the display. The cell’s underlying value remains a number, so calculations are unaffected.

Can I use a custom currency symbol like £? Yes, just replace the dollar sign with the pound sign: "£#,##0.00". Ensure the character is typed correctly in the code.

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.