VBA FORMATPERCENT Function (Syntax + Example)

puneet-gogia-excel-champs

- Written by Puneet

The VBA FORMATPERCENT 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 percentage format to it. In simple words, it returns the result with with a percentage format as a string data type.

Syntax

FormatPercent(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 VBA will use 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 VBA will use vbUseDefault by default].
    • vbFalse: To not to encase negative numbers in parentheses.
    • vbTrue: Encase negative numbers in parentheses.
    • vbUseDefault: Use the default settings.
  • [GroupDigits]: An 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 VBA will take vbUseDefault by default].
    • vbFalse: To not use group digits.
    • vbTrue: To use Group digits.
    • vbUseDefault: Use the default computer settings.

Example

To practically understand how to use the VBA FORMATPERCENT function, you need to go through the below example where we have written a vba code by using it:

example-vba-formatpercent-function
Sub example_FORMATPERCENT()
Range("B1").Value = FormatPercent(Range("A1"))
End Sub

In the above code, we have used the FORMATPERCENT to convert the value in cell A1 into a percentage 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.
Last Updated: June 22, 2023