How to use FormatPercent function in VBA?

In VBA (Visual Basic for Applications), the FormatPercent function is used to format a number as a percentage with a specified number of decimal places.

Basic syntax

VBA


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

Parameters

Expression is the numeric value you want to format as a percentage.
NumDigitsAfterDecimal (optional) specifies the number of digits to be displayed after the decimal point.
IncludeLeadingDigit (optional) specifies whether or not a leading zero is displayed for fractional percentages.
UseParensForNegativeNumbers (optional) specifies whether or not to place negative numbers within parentheses.
GroupDigits (optional) specifies whether or not numbers are grouped using the group delimiter (e.g., “1,000” for thousand).

Here’s an example of how you can use the FormatPercent function in VBA:

VBA


Sub FormatPercentageExample()
    Dim myValue As Double
    myValue = 0.12345 ' 12.345%
    
    ' Format the percentage without any optional arguments
    Debug.Print FormatPercent(myValue) ' By default should print "12.35%"
    
    ' Format the percentage with 1 decimal place
    Debug.Print FormatPercent(myValue, 1) ' Should print "12.3%"
    
    ' Format the percentage with 2 decimal places and no leading digit for values less than 1
    Debug.Print FormatPercent(myValue, 2, True) ' Should print "12.35%"
    
    ' Format the percentage with 2 decimal places and parentheses for negative numbers (none in this example)
    Debug.Print FormatPercent(myValue, 2, True, True) ' Should print "12.35%"
    
    ' Format the percentage with grouping digits (not applicable in most percentage formats)
    Debug.Print FormatPercent(myValue, 2, True, True, True) ' Should print "12.35%"
End Sub

Remember, the result of FormatPercent is a string representation of the number formatted as a percentage. The actual numeric value does not change. This is useful for displaying percentages in user interfaces, reports, or concatenations with other strings where the visual format is important.

facebooklinkedin