Entering the Code in the Visual Basic Editor
- Go to the Developer tab.
- Click Visual Basic.
- Click Insert and select Module.
Enter the code and press F5 to run it.
Method 1 – Using the VBA Format Function to Format a Number as Currency with Two Decimal Places
The syntax of the Format function is:
Format(expression, [format], [firstdayofweek],[firstweekofyear])
expression- any valid expression. You can insert a number or Range object as the argument.
format- a valid number format (predefined or user-defined)
To format the list of random numbers below as currency:
1. 1 Using Currency as the Number Format
- Copy the following VBA code:
Sub FormatCurrencyWithTwoDecimalPlaces()
Range("C6") = Format(Range("B6"), "Currency")
Range("C7") = Format(Range("B7"), "Currency")
Range("C8") = Format(Range("B8"), "Currency")
Range("C9") = Format(Range("B9"), "Currency")
End Sub
- Press F5 to run the code.
This is the output.
The currency formatted numbers have 2 decimal places (defined by default).
To check it:
- Select a currency-formatted cell.
- Press Ctrl + 1.
- See the value in “Decimal places”.
1.2 Apply a Custom Number Format
Use the following custom format code:
$#,###.00
Enter the following code in the Visual Basic Editor.
Sub FormatCurrencyWithTwoDecimalPlaces()
Range("C6") = Format(Range("B6"), "$#,###.00")
Range("C7") = Format(Range("B7"), "$#,###.00")
Range("C8") = Format(Range("B8"), "$#,###.00")
Range("C9") = Format(Range("B9"), "$#,###.00")
End Sub
Read More: Excel VBA: Number Format with No Decimal Places
Method 2 – Format a Number as Currency Using the Range.NumberFormat Property in Excel VBA
The syntax of this property is:
expression.NumberFormat = “pre-defined or user-defined number format”
- Enter the code:
Sub FormatCurrencyWithTwoDecimalPlaces()
Range("B6").NumberFormat = "$#,###.00"
Range("B7").NumberFormat = "$#,###.00"
Range("B8").NumberFormat = "£#,###.00 "
Range("B9").NumberFormat = "£#,###.00"
End Sub
- Run the code.
This is the output.
The British Pound (£) and the Dollar ($) signs were used in the custom format code.
Read More: How to Use Excel VBA to Format Number in Decimal Places
Method 3 – Using theVBA FormatCurrency Function to Convert a Number to Currency Format in Excel
The syntax of the function is:
FormatCurrency(Expression, [ NumDigitsAfterDecimal, [ IncludeLeadingDigit, [ UseParensForNegativeNumbers, [GroupDigits]]]])
expression- any valid expression. You can insert a number or Range object as the argument.
NumDigitsAfterDecimal- specifies the decimal places to display in the currency formatted number. It is optional. If you leave it blank, the default value (2 decimal places) is displayed. This value is set by the regional settings.
- Use the code:
Sub FormatCurrencyWithTwoDecimalPlaces()
Range("C6") = FormatCurrency(Range("B6"), 2)
Range("C7") = FormatCurrency(Range("B7"), 2)
Range("C8") = FormatCurrency(Range("B8"))
Range("C9") = FormatCurrency(Range("B9"))
End Sub
- Press F5 to run the code.
Notes
To change the regional settings:
- Go to the Control panel.
- Click Change date, time, or number formats.
- Select Additional settings.
- In the Numbers tab, the “No of digits after decimal” displays 2.
- In the Currency tab, the default currency symbol is dollar $.
Download Practice Workbook
Download the practice workbook.
