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] )
[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:
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:
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:
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 )
cur4 = FormatCurrency( -500, 2, , vbTrue )' cur3 is now equal to the String "$101". ' cur4 is now equal to the String "($500.00)". |
Note that in the above examples:
- In the first call to the FormatCurrency function, only the Expression argument has been provided. Therefore the returned string "$1,000,000.00" has used the default currency format for the current computer.
- In the second call to the FormatCurrency function, the [GroupDigits] argument is set to vbFalse. Therefore, the number 1,000,000.00 is returned as the string "$1000000.00" (with no commas separating the groups of numbers).
- In the third call to the FormatCurrency function, the [NumDigitsAfterDecimal] argument is set to 0. Therefore, the value 100.55 is rounded to zero decimal places before being returned as the currency string "$101".
- In the fourth call to the FormatCurrency function, the [UseParensForNegativeNumbers] argument is set to vbTrue. Therefore, the value -500 is returned as the string ($500.00).
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
