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

Get FREE Advanced Excel Exercises with Solutions!

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.

## 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`````` 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``` ### 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 (\$).

### 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.  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 Advanced Excel Exercises with Solutions PDF  