VBA FORMATPERCENT Function

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

What is VBA FORMATPERCENT Function

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.

how to use it

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

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 to use group digits.
    • vbTrue: To use Group digits.
    • vbUseDefault: Use the default computer settings.

Example to use FORMATPERCENT Function in VBA

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

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

Notes

Below are some important points which you need to take care while using FORMATPERCENT 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.