The VBA FORMATNUMBER function is listed under the data type conversion category of VBA functions. When you use it in a VBA code, it returns the supplied expression by applying a number format to it. In simple words, the result you get has a number format as a string data type.
Syntax
FormatNumber( Expression, [NumDigitsAfterDecimal], [IncludeLeadingDigit],[UseParensForNegativeNumbers], [GroupDigits] )
Arguments
- Expression: The expression that you want to format.
- [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 practically understand how to use VBA FORMATNUMBER function, you need to go through the below example where we have written a vba code by using it:
Sub example_FORMATNUMBER()
Range("B1").Value = FormatNumber(Range("A1"))
End Sub
In the above code, we have used the FORMATNUMBER to convert the value in cell A1 into a numeric value and returned the result in cell B1.
Notes
- 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
CBOOL | CBYTE | CCUR | CDATE | CDBL | CDEC | CHR | CINT | CLNG | CSNG | CSTR | CVAR | FORMATCURRENCY | FORMATDATETIME | FORMATPERCENT | HEX | OCT | STR | VAL