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
- Excel Staff Schedule Template: Free Options & How to Build One
- Excel Spreadsheet Templates for Tracking Time
- Excel Spreadsheet Timesheet Template: How to Make One
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Find a Character in a StringIn VBA, use the InStr function to find a character in a string: it returns the character’s position,…
- How to Concatenate Strings in VBA Using & and JoinIn VBA, concatenate strings by joining them with the ampersand operator (&), such as fullName = firstName &…
- Excel VBA: Format a Range as a DateTo format a range as dates with Excel VBA, assign a date format to its NumberFormat property, for…
- Excel VBA Paste Values: Methods, Code, and Common FixesTo paste values with Excel VBA, copy a source range and use PasteSpecial Paste:=xlPasteValues, or assign the source…
- VBA Code to Consolidate Excel Files into One WorkbookTo consolidate matching Excel files into one worksheet, place the macro below in the workbook you want to…
- VBA Range.Delete: Remove Cells, Rows, or ColumnsIn Excel VBA, use the Delete method to remove cells, rows, or columns: for example, Range("B2:C5").Delete Shift:=xlShiftUp deletes…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.