Excel VBA: Format Currency to Two Decimal Places (3 Methods)

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 userdefined 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

Excel VBA Format Currency to Two Decimals

Now press F5 to run the code and the output is here in the following screenshot.

Excel VBA Format Currency to Two Decimals

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

Excel VBA Format Currency to Two Decimals


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
Excel VBA Format Currency to Two Decimals

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

Excel VBA Format Currency to Two Decimals

Now run the code to see the currency formatted numbers as output.

Excel VBA Format Currency to Two Decimals

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

Excel VBA Format Currency to Two Decimals

Press F5 to run the code.

Excel VBA Format Currency to Two Decimals

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.

Excel VBA Format Currency to Two Decimals

  • 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.


Related Articles

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo