Al Arafat Siddique
This article illustrates how to format a number as currency with two decimal places using VBA code in Excel. This helps to display numbers in our dataset as monetary values. The currency number format is one of the built-in number formats in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Methods to Format a Number as Currency with Two Decimal Places Using VBA in Excel
Applying the default currency format adds a currency symbol that is defined by the computer’s regional settings. We can change the currency symbol by changing the settings or using a custom number format in our code. It is also possible to set the number of decimal places while formatting a number to the currency format.
We’ll apply Excel’s built-in functions to format a number as currency with two decimal places using VBA Code. for this, we need to open and write VBA code in the visual basic editor.
Write Code in Visual Basic Editor
Follow the steps to open the visual basic editor and write some code there.
- Go to the Developer tab from the Excel Ribbon.
- Click the Visual Basic option.
- In the Visual Basic for Applications window, click the Insert dropdown to select the New Module option.
Now put your code inside the visual code editor and press F5 to run it.
1. Use of VBA Format Function to Format a Number as Currency with Two Decimal Places
The Format function in VBA code converts numbers into strings that are formatted according to predefined or user–defined format codes. The syntax of the function is-
Format(expression, [format], [firstdayofweek],[firstweekofyear])
Here,
expression- any valid expression. We can insert a number or Range object i.e., cell reference as the argument.
format- a valid number format (predefined or user-defined)
Let’s say, we have a list of random numbers that we want to format as currency. We can apply the currency format by using either the built-in currency format or a custom number code. We need to put this as the 2nd argument of the Format function.
1. 1 Using Currency as the Number Format
In this example, we want to apply the Currency format to numbers in cells B6:B9 and output them in cells C6:C9. Let’s copy and paste the following VBA code into the visual basic editor.
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
Now press F5 to run the code and the output is here in the following screenshot.
The converted currency formatted numbers have got 2 decimal places which are defined by the system by default. To can check that-
- Select a currency formatted cell
- Press Ctrl + 1 on the keyboard
- Check the value in the “Decimal places” input box
1.2 Apply a Custom Number Format
We can also use a custom number format code to apply the currency format by specifying the decimal places to display. Here in this example, we’re going to use the following custom format code–
$#,###.00
To apply the custom number code to cells B6:B9, run 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: Format Percentage to 2 Decimal Places
2. Format a Number as Currency by Using Range.NumberFormat Property in Excel VBA
Another way to apply the Currency format to numbers is to use Excel’s Range.NumberFormat property. The syntax of this property is-
expression.NumberFormat = “pre-defined or user-defined number format”
Now let’s use this property in our code to format the numbers in B6:B9 as currency with two decimal places.
Sub FormatCurrencyWithTwoDecimalPlaces()
Range("B6").NumberFormat = "$#,###.00"
Range("B7").NumberFormat = "$#,###.00"
Range("B8").NumberFormat = "£#,###.00 "
Range("B9").NumberFormat = "£#,###.00"
End Sub
Now run the code to see the currency formatted numbers as output.
Here we used British Pound (£) sign in the custom format code along with the Dollar sign ($).
Read More: Excel VBA: Round to 2 Decimal Places (5 Ways)
3. Apply VBA FormatCurrency Function to Convert a Number to Currency Format in Excel
The FormatCurrency function in Excel converts an expression into a currency formatted value. The syntax of the function is-
FormatCurrency(Expression, [ NumDigitsAfterDecimal, [ IncludeLeadingDigit, [ UseParensForNegativeNumbers, [GroupDigits]]]])
Here,
expression- any valid expression. We can insert a number or Range object i.e., cell reference as the argument. NumDigitsAfterDecimal- with this argument we can specify the decimal places to display in the currency formatted number. It is optional and if we leave it blank, it will apply the default value -1. The default value shows the default decimal places (in this case, 2). This value is set by the computer’s regional settings.
Let’s apply this function to cells B6:B9 and output them in cells C6:C9. The code is here-
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.
Read More: How to Use Excel VBA to Format Number in Decimal Places (6 Ways)
Notes
To change the computer’s regional settings,
- Go to the Control panel.
- Click on the Change date, time, or number formats option.
- Open the Additional settings from the Region window.
- In the Numbers tab, we see the “No of digits after decimal” as 2.
- Again in the Currency tab, we can see the default currency symbol as dollar $.
From these settings, we can modify the default regional settings for numbers and currency.
Conclusion
Now, we know how to format a number as currency with two decimal places using Excel’s built functions with examples. Hopefully, it would help you to use the functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.
