VBA FORMATNUMBER Function

HomeVBA Functions LIST (Category Wise)How to use the VBA FORMATNUMBER Function (Syntax + Example)

What is VBA FORMATNUMBER Function

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.

how to use it

To use VBA's FORMATNUMBER function you need to understand its syntax and arguments:

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 use FORMATNUMBER Function in VBA

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 the cell A1 into a numeric value and returned the result in cell B1.

Notes

Below are some important points which you need to take care while using FORMATNUMBER function in VBA.

  • 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

About the Author

puneet one point one

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.