Excel VBA FormatNumber Function

VBA FormatNumber Function: The FormatNumber function in VBA is used to apply a number format to a numeric expression, and it returns the result as a string.

Syntax

FormatNumber (Expression, [NumDigitsAfterDecimal], [IncludeLeadingDigit],
[UseParensForNegativeNumbers], [GroupDigits])

Parameter

Expression (required) – This parameter represents the numeric expression that you want to format.

NumDigitsAfterDecimal (optional) – This parameter represents the number of digits that should be shown after the decimal. The default value is -1.

IncludeLeadingDigit (optional) – This parameter represents the vbTriState enumeration value, stating whether a leading zero should be shown for fractional values. The default value is set to vbUseDefault.

It can take the following values:

  • vbFalse – It does not display a leading zero.
  • vbTrue – It displays a leading zero.
  • vbUseDefault (default)– It uses the default computer settings.

UseParensForNegativeNumbers (optional) – This parameter represents the vbTriState enumeration value, stating whether negative numbers should be encased within parentheses. The default value is set to vbUseDefault.

It can take the following values:

  • vbFalse – It does not encase negative numbers in parentheses.
  • vbTrue – It encases the negative numbers in parentheses.
  • vbUseDefault (default)– It uses the default computer settings.

GroupDigits (optional) – This parameter specifies whether the number should be grouped (into thousands, etc.), using the group delimiter that is specified on the computer's regional settings. The default value is set to vbUseDefault.

It can take the following values:

  • vbFalse – It does not group digits.
  • vbTrue – It groups the digits.
  • vbUseDefault (default)– It uses the default computer settings.

Return

This function returns a string value after applying the number format to the supplied numeric expression.

Example 1

Sub ValFunction_Example1()
 ' Converting the strings into numeric values.
 Dim num_val As Double, num_val2 As Double
 num_val = FormatNumber(1000.987, 3)
 ' num1 is now equal to the String "1000.99".
 Cells(1, 1).Value = num_val
 num_val2 = FormatNumber(1000.987, 2)
 ' num1_val2 will return the String "1000.99".
 Cells(2, 1).Value = num_val2
 End Sub 

Output

1000.987
1,000.99

Example 2

Sub ValFunction_Example2()
 ' Converting the strings into numeric values.
 Dim num_val As Double, num_val2 As Double
 num_val = FormatNumber(-500, 2, , vbTrue)
 ' num_val is now equal to the String "-500".
 Cells(1, 1).Value = num_val
 End Sub 

Output

-500