The VBA FormatNumber Function
Description
The VBA FormatNumber function applies a number format to a numeric expression and returns the result as a string.
The syntax of the function is:
FormatNumber( Expression,
[NumDigitsAfterDecimal],
[IncludeLeadingDigit],
[UseParensForNegativeNumbers], [GroupDigits] )
[UseParensForNegativeNumbers], [GroupDigits] )
Where the function arguments are:
| Expression | - |
The 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 FormatNumber Function Examples
The following example shows how the VBA FormatNumber function can be used to format numeric values. Each example uses different formatting rules.
|
' Format numeric values in different ways.
Dim num1 As String
Dim num2 As String Dim num3 As String Dim num4 As String
num1 = FormatNumber( 1000000 )
' num1 is now equal to the String "1,000,000.00".
num2 = FormatNumber( 1000000, , , , vbFalse )
' num2 is now equal to the String "1000000.00".
num3 = FormatNumber( 100.55, 0 )
num4 = FormatNumber( -500, , , vbTrue )' num3 is now equal to the String "101". ' num4 is now equal to the String "(500.00)". |
Note that in the above calls to the FormatNumber function:
- When only the Expression argument is provided, the function uses the default number format for the current system.
- When the [GroupDigits] argument is set to vbFalse, the number 1,000,000 is returned as the string "1000000.00" (with no commas separating the groups of numbers).
- When the [NumDigitsAfterDecimal] argument is set to 0, the supplied expression, 100.55 is rounded to zero decimal places before being returned as the string "101".
- When the [UseParensForNegativeNumbers] argument is set to vbTrue, the negative value -500 is returned as the string "(500.00)".
Note also, that in each case, the result that is returned from the FormatNumber function is a String data type.
VBA FormatNumber Function Error
If the Expression that is supplied to the FormatNumber function is a text string that cannot be converted into a numeric value, you will get the error:
Run-time error '13': Type mismatch
