How to use FormatNumber function in VBA?

In VBA (Visual Basic for Applications), the `FormatNumber` function 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.

Here is the syntax for the `FormatNumber` function:

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 `FormatNumber` function in 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 in the Immediate Window (press `Ctrl + G` to view the Immediate Window in the VBA editor) 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