How to use FormatNumber function in VBA?

The FormatNumber function in VBA (Visual Basic for Applications) is used to format a numeric value as a string with a specified number of decimal places and includes proper formatting such as commas for thousands.

Basic syntax

VBA

FormatNumber(Expression, [NumDigitsAfterDecimal As Long = -1], [IncludeLeadingDigit], [UseParensForNegativeNumbers], [GroupDigits])

Expression: The numeric expression to be formatted.
NumDigitsAfterDecimal (optional): The number of digits to display after the decimal point. If this argument is omitted, it defaults to the value of -1, which means the computer’s regional settings are used. IncludeLeadingDigit (optional): Specifies whether or not to include a leading zero for numbers between 0 and 1. If omitted, the computer’s regional settings are used.
UseParensForNegativeNumbers (optional): Specifies whether to place negative values within parentheses. If omitted, the computer’s regional settings are used.
GroupDigits (optional): Specifies whether or not to group digits with commas (or other separators based on regional settings). If omitted, the computer’s regional settings are used. Here’s how you can use the

Example

VBA


Sub FormatNumberExample()
    Dim myNumber As Double
    myNumber = 123456.789
    
    ' Default formatting (based on regional settings).
    Debug.Print FormatNumber(myNumber)
    
    ' Two decimal places, include leading digit, use parentheses for negative numbers, and group digits.
    Debug.Print FormatNumber(myNumber, 2, True, True, True)
    
    ' No decimal places, include leading digit, do not use parentheses for negative numbers, and do not group digits.
    Debug.Print FormatNumber(myNumber, 0, vbTrue, vbFalse, vbFalse)
End Sub

When you run the FormatNumberExample subroutine, you will get output similar to the following, which will depend on your regional settings: “123,456.79” “123,456.79” “123457” Please note that the actual output can vary based on your system’s regional settings, as these settings determine the default behavior for digit grouping, decimal point characters, etc.

facebooklinkedin