The VBA FormatCurrency Function

Description

The VBA FormatCurrency function applies a currency format to a numeric expression and returns the result as a string.

The syntax of the function is:

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

Where the function arguments are:

Expression-

The numeric expression that you want to format.

[NumDigitsAfterDecimal]-

An optional numeric value specifying the number of digits that should be displayed after the decimal.

If [NumDigitsAfterDecimal] is omitted, it defaults to the value -1, denoting that the computer's regional settings should be used.

[IncludeLeadingDigit]-

An optional vbTriState enumeration value, specifying whether a leading zero should be displayed for fractional values.

This can have any of the following values:

vbFalse-Do not display a leading zero.
vbTrue-Display a leading zero.
vbUseDefault-Use the default computer settings.

If omitted, the [IncludeLeadingDigit] argument is set to vbUseDefault.

[UseParensForNegativeNumbers]-

An optional vbTriState enumeration value, specifying whether negative numbers should be encased in parentheses.

This can have any of the following values:

vbFalse-Do not encase negative numbers in parentheses.
vbTrue-Encase negative numbers in parentheses.
vbUseDefault-Use the default computer settings.

If omitted, the [UseParensForNegativeNumbers] argument is set to vbUseDefault.

[GroupDigits]-

An optional vbTriState enumeration value, specifying whether the number should be grouped (into thousands, etc.), using the group delimiter that is specified on the computer's regional settings.

This can have any of the following values:

vbFalse-Do not group digits.
vbTrue-Group digits.
vbUseDefault-Use the default computer settings.

If omitted, the [GroupDigits] argument is set to vbUseDefault.



VBA FormatCurrency Function Examples

The following example shows how the VBA FormatCurrency function can be used to format numeric values as currencies. Each example uses different formatting rules.

' Format different numeric values as currencies.
Dim cur1 As String
Dim cur2 As String
Dim cur3 As String
Dim cur4 As String
cur1 = FormatCurrency( 1000000.00 )
' cur1 is now equal to the String "$1,000,000.00".
cur2 = FormatCurrency( 1000000.00,   ,   ,   , vbFalse )
' cur2 is now equal to the String "$1000000.00".
cur3 = FormatCurrency( 100.55, 0 )
' cur3 is now equal to the String "$101".
cur4 = FormatCurrency( -500, 2,   , vbTrue )
' cur4 is now equal to the String "($500.00)".

Note that in the above examples:


Note also, that in each case, the result that is returned from the FormatCurrency function is a String data type.


VBA FormatCurrency Function Error

If the Expression that is supplied to the FormatCurrency function is a text string that cannot be converted into a numeric value, you will get the error:

Run-time error '13': Type mismatch

VBA Run Time Error 13 Message Box