The VBA FormatPercent Function
Description
The VBA FormatPercent function applies a percentage format to a numeric expression and returns the result as a string.
The syntax of the function is:
FormatPercent( Expression,
[NumDigitsAfterDecimal],
[IncludeLeadingDigit],
[UseParensForNegativeNumbers], [GroupDigits] )
[UseParensForNegativeNumbers], [GroupDigits] )
Where the function arguments are:
| Expression | - |
The expression that you want to format. |
|||||||||
| [NumDigitsAfterDecimal] | - |
An optional numeric value specifying the number of digits that should be displayed after the decimal. If [NumDigitsAfterDecimal] is omitted, it defaults to the value -1, denoting that the computer's regional settings should be used. |
|||||||||
| [IncludeLeadingDigit] | - |
An optional vbTriState enumeration value, specifying whether a leading zero should be displayed for fractional values. This can have any of the following values:
If omitted, the [IncludeLeadingDigit] argument is set to vbUseDefault. |
|||||||||
| [UseParensForNegativeNumbers] | - |
An optional vbTriState enumeration value, specifying whether negative numbers should be encased in parentheses. This can have any of the following values:
If omitted, the [UseParensForNegativeNumbers] argument is set to vbUseDefault. |
|||||||||
| [GroupDigits] | - |
An optional vbTriState enumeration value, specifying whether the number should be grouped (into thousands, etc.), using the group delimiter that is specified on the computer's regional settings. This can have any of the following values:
If omitted, the [GroupDigits] argument is set to vbUseDefault. |
VBA FormatPercent Function Examples
The following example shows how the VBA FormatPercent function can be used to format numeric values into percentages. Each example uses different formatting rules.
|
' Format numeric values in different percentage formats.
Dim pc1 As String
Dim pc2 As String Dim pc3 As String Dim pc4 As String
pc1 = FormatPercent( 10 )
' pc1 is now equal to the String "1,000.00%".
pc2 = FormatPercent( 10, , , , vbFalse )
' pc2 is now equal to the String "1000.00%".
pc3 = FormatPercent( 0.559, 0 )
pc4 = FormatPercent( -0.5, , , vbTrue )' pc3 is now equal to the String "56%". ' pc4 is now equal to the String "(50.00%)". |
Note that in the above calls to the FormatPercent function:
- When only the Expression argument is provided, the function uses the default percentage format for the current system.
- When the [GroupDigits] argument is set to vbFalse, the number 10 is returned as the percentage string "1000.00%" (with no commas separating the groups of numbers).
- When the [NumDigitsAfterDecimal] argument is set to 0, the supplied expression, 0.559 is rounded to the percentage 56% (zero decimal places), before being returned as the string "56%".
- When the [UseParensForNegativeNumbers] argument is set to vbTrue, the negative value -0.5 is returned as the string "(50.00%)".
Note also, that in each case, the result that is returned from the FormatPercent function is a String data type.
VBA FormatPercent Function Error
If the Expression that is supplied to the FormatPercent function is a text string that cannot be converted into a numeric value, you will get the error:
Run-time error '13': Type mismatch
