Excel VBA FormatCurrency Function

VBA FormatCurrency Function: The FormatCurrency function in VBA is used to apply a currency format to a numeric expression and returns the result as a string.

Syntax

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

Parameter

Expression (required) – This parameter represents the numeric expression that you want to format.

NumDigitsAfterDecimal (optional) – This parameter represents the number of digits that should be shown after the decimal. The default value is -1.

IncludeLeadingDigit (optional) – This parameter represents the vbTriState enumeration value, stating whether a leading zero should be shown for fractional values. The default value is set to vbUseDefault.

It can take the following values:

• vbFalse – It does not display a leading zero.
• vbTrue – It displays a leading zero.
• vbUseDefault (default)– It uses the default computer settings.

UseParensForNegativeNumbers (optional) – This parameter represents the vbTriState enumeration value, stating whether negative numbers should be encased within parentheses. The default value is set to vbUseDefault.

It can take the following values:

• vbFalse – It does not encase negative numbers in parentheses.
• vbTrue – It encases the negative numbers in parentheses.
• vbUseDefault (default)– It uses the default computer settings.

GroupDigits (optional) – This parameter specifies whether the number should be grouped (into thousands, etc.), using the group delimiter that is specified on the computer's regional settings. The default value is set to vbUseDefault.

It can take the following values:

• vbFalse – It does not group digits.
• vbTrue – It groups the digits.
• vbUseDefault (default)– It uses the default computer settings.

Return

This function returns the result as a string after applying the currency format to the given numeric expression.

Example 1

Sub FormatCurrencyFunction_Example1()
 ' Formating numeric values as currencies.
 Dim forCar As String
 forCar = FormatCurrency(500000)
 ' The variable forCar is now equal to the String "$500,000.00".
 Cells(1, 1).Value = forCar
 End Sub 

Output

$500,000.00

Example 2

Sub FormatCurrencyFunction_Example2()
 ' Formating numeric values as currencies.
 Dim forCar As String
 'will denote the value within parenthesis
 forCar = FormatCurrency(-500000, 2, , vbTrue, vbFalse)
 ' The variable forCar is now equal to the String "($500,000.00)".
 Cells(1, 1).Value = forCar
 End Sub 

Output

($500,000.00)

Example 3

Sub FormatCurrencyFunction_Example3()
 ' Formating numeric values as currencies.
 Dim forCar As String
 'passing a string
 forCar = FormatCurrency(50.89, 0)
 ' The variable forCar is now equal to the String "$51.00".
 Cells(1, 1).Value = forCar
 End Sub 

Output

$51.00

Example 4

Sub FormatCurrencyFunction_Example4()
 ' Formating numeric values as currencies.
 Dim forCar As String
 'passing a string
 forCar = FormatCurrency("50.89, 0")
 ' The variable forCar will return a type mismatch run-time error.
 Cells(1, 1).Value = forCar 

End Sub

Output