VBA FORMATNUMBER Function (Syntax + Example)

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.