VBA FORMATCURRENCY Function

HomeVBA Functions LIST (Category Wise)How to use the VBA FORMATCURRENCY Function (Syntax + Example)

What is VBA FORMATCURRENCY Function

The VBA FORMATCURRENCY function is listed under the data type conversion category of VBA functions. When you use it in a VBA code, it returns a string by applying currency format to a number. In simple words, you can convert a number into a value in which currency format is applied.

how to use it

To use VBA's FORMATCURRENCY function you need to understand its syntax and arguments:

Syntax

FormatCurrency(Expression,[NumDigitsAfterDecimal],[IncludeLeadingDigit],[UseParensForNegativeNumbers],[GroupDigits])

Arguments

  • Expression: The numeric value which you want to format as currency.
  • [NumDigitsAfterDecimal]: A numeric value to specify the decimals [This is an optional argument and if omitted -1 by default].
  • [IncludeLeadingDigit]: An enumeration value to specify whether a leading zero should be displayed [This is an optional argument and if omitted vbUseDefault by default].
    • vbFalse: To not to a leading zero.
    • vbTrue: Display a leading zero.
    • vbUseDefault: Use the default settings.
  • [UseParensForNegativeNumbers]: An enumeration value to specify whether negative numbers should be encased in parentheses [This is an optional argument and if omitted vbUseDefault by default].
    • vbFalse: To not to encase negative numbers in parentheses.
    • vbTrue: Encase negative numbers in parentheses.
    • vbUseDefault: Use the default settings.
  • [GroupDigits]: n enumeration value to specify whether the number should be grouped (into thousands, etc.), using the group delimiter that is specified on the computer's regional settings [This is an optional argument and if omitted vbUseDefault by default].
    • vbFalse: To not to use group digits.
    • vbTrue: To use group digits.
    • vbUseDefault: Use the default computer settings.

Example to use FORMATCURRENCY Function in VBA

To practically understand how to use VBA FORMATCURRENCY function, you need to go through the below example where we have written a vba code by using it:

Sub example_FORMATCURRENCY()
Range("B1").Value = FormatCurrency(Range("A1"))
End Sub

In the above code, we have used FORMATCURRENCY to convert the value in the cell A1 into a currency value and it has returned it in the cell B1 with the dollar sign.

Notes

Below are some important points which you need to take care while using FORMATCURRENCY function in VBA.

  • The currency symbol used depends on system settings.
  • If the value specified is a value other than a number or a string that can't be recognized as a number, VBA will return the run-time 13 error.

Related Functions

About the Author

puneet one point one

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.