FormatPercent Function

Returns an expression formatted as a percentage (multiplied by 100) with a trailing % character.

Syntax

FormatPercent(expression[, numdigitsafterdecimal[, includeleadingdigit[, useparensfornegativenumbers[, groupdigits]]]])

Parameters

expression - Required. Expression to be formatted as a percentage.
numdigitsafterdecimal - Optional. Numeric value indicating how many places to the right of the decimal are displayed. Default value is -1, which indicates that the computer's regional settings are used.
includeleadingdigit - Optional. Tristate constant that indicates whether or not a leading zero is displayed for fractional values. See Settings section for values.
useparensfornegativenumbers - Optional. Tristate constant that indicates whether or not to place negative values within parentheses. See Settings section for values.
groupdigits - Optional. Tristate constant that indicates whether or not numbers are grouped using the group delimiter specified in the computer's regional settings. See Settings section for values.

Settings

The includeleadingdigit, useparensfornegativenumbers, and groupdigits arguments have the following settings:

Constant	Value	Description
vbTrue	-1	True
vbFalse	0	False
vbUseDefault	-2	Use the setting from the computer's regional settings

Return Value

Returns a Variant of subtype String containing the expression formatted as a percentage with a trailing % character.

Remarks

The FormatPercent function multiplies the numeric value by 100 and appends a percent sign.
When one or more optional arguments are omitted, the computer's regional settings provide the default values.
The number is formatted according to the computer's locale settings.
If the expression is Null, FormatPercent returns an empty string.
The default number of decimal places is 2 (from regional settings).

Typical Uses

Displaying statistical percentages (success rates, completion rates)
Financial ratios (profit margins, growth rates, interest rates)
Survey results and poll data
Progress indicators and completion percentages
Conversion rates and efficiency metrics
Grade percentages and test scores

Basic Usage Examples

' Default formatting (2 decimal places from regional settings)
result = FormatPercent(0.75)           ' Returns: "75.00%"
result = FormatPercent(0.5)            ' Returns: "50.00%"
result = FormatPercent(1.25)           ' Returns: "125.00%"

' Handling negative percentages
result = FormatPercent(-0.15)          ' Returns: "-15.00%"
result = FormatPercent(-0.05, 2, , vbTrue)  ' Returns: "(5.00)%"

' Controlling decimal places
result = FormatPercent(0.3333, 0)      ' Returns: "33%"
result = FormatPercent(0.3333, 2)      ' Returns: "33.33%"
result = FormatPercent(0.3333, 4)      ' Returns: "33.3300%"

' Leading digit control
result = FormatPercent(0.005, 2, vbTrue)   ' Returns: "0.50%"
result = FormatPercent(0.005, 2, vbFalse)  ' Returns: ".50%"

Common Patterns

1. Success Rate Display

Dim successful As Long
Dim total As Long
Dim rate As Double

successful = 85
total = 100
rate = successful / total

MsgBox "Success Rate: " & FormatPercent(rate, 1)  ' "Success Rate: 85.0%"

2. Financial Ratios

Dim profit As Currency
Dim revenue As Currency
Dim margin As Double

profit = 25000
revenue = 100000
margin = profit / revenue

lblMargin.Caption = "Profit Margin: " & FormatPercent(margin, 2)  ' "Profit Margin: 25.00%"

3. Survey Results

Dim yesVotes As Long
Dim totalVotes As Long

yesVotes = 347
totalVotes = 500

txtResult.Text = FormatPercent(yesVotes / totalVotes, 1)  ' "69.4%"

4. Progress Indicator

Dim completed As Long
Dim total As Long
Dim progress As Double

completed = 45
total = 100
progress = completed / total

lblProgress.Caption = "Progress: " & FormatPercent(progress, 0)  ' "Progress: 45%"

5. Growth Rate Calculation

Dim currentValue As Double
Dim previousValue As Double
Dim growth As Double

currentValue = 125000
previousValue = 100000
growth = (currentValue - previousValue) / previousValue

MsgBox "Growth: " & FormatPercent(growth, 1)  ' "Growth: 25.0%"

6. Grade Percentage

Dim score As Long
Dim maxScore As Long
Dim percentage As Double

score = 87
maxScore = 100
percentage = score / maxScore

lblGrade.Caption = "Score: " & FormatPercent(percentage, 0)  ' "Score: 87%"

7. Comparison Display

Dim actual As Double
Dim target As Double
Dim achievement As Double

actual = 95000
target = 100000
achievement = actual / target

lblStatus.Caption = "Target Achievement: " & FormatPercent(achievement, 1)  ' "Target Achievement: 95.0%"

8. `ListBox` Population with Percentages

Dim i As Integer
Dim values() As Double
Dim total As Double

values = Array(15.5, 28.3, 42.1, 14.1)
total = 100

For i = 0 To UBound(values)
lstResults.AddItem FormatPercent(values(i) / total, 1)
Next i

9. Database Field Display

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Open "SELECT CompletionRate FROM Projects", conn

While Not rs.EOF
Debug.Print FormatPercent(rs("CompletionRate"), 0)
rs.MoveNext
Wend

10. Multiple Percentages in Report

Dim passed As Long, failed As Long, total As Long
Dim report As String

passed = 85
failed = 15
total = passed + failed

report = "Passed: " & FormatPercent(passed / total, 1) & vbCrLf & _
"Failed: " & FormatPercent(failed / total, 1)
' "Passed: 85.0%
'  Failed: 15.0%"

Advanced Usage

1. Flexible Percentage Formatter

Function DisplayPercentage(value As Double, Optional precision As Integer = 1) As String
If value < 0.01 Then
DisplayPercentage = FormatPercent(value, 3)  ' More precision for small values
ElseIf value > 1 Then
DisplayPercentage = FormatPercent(value, 0)  ' No decimals for large percentages
Else
DisplayPercentage = FormatPercent(value, precision)
End If
End Function

2. Comparison with Color Coding

Function FormatVariance(actual As Double, target As Double) As String
Dim variance As Double
variance = (actual - target) / target

FormatVariance = FormatPercent(variance, 1)

If variance > 0 Then
lblVariance.ForeColor = vbGreen  ' Positive variance
ElseIf variance < 0 Then
lblVariance.ForeColor = vbRed    ' Negative variance
End If
End Function

3. Dynamic Precision Based on Value

Function SmartFormatPercent(value As Double) As String
Dim absValue As Double
absValue = Abs(value)

Select Case absValue
Case Is < 0.0001
SmartFormatPercent = FormatPercent(value, 4)
Case Is < 0.01
SmartFormatPercent = FormatPercent(value, 3)
Case Is < 1
SmartFormatPercent = FormatPercent(value, 2)
Case Else
SmartFormatPercent = FormatPercent(value, 1)
End Select
End Function

4. Table Alignment with Percentages

Function AlignedPercentage(value As Double, width As Integer) As String
Dim formatted As String
formatted = FormatPercent(value, 2)
AlignedPercentage = Space(width - Len(formatted)) & formatted
End Function

' Usage in grid or report
For i = 1 To 10
Debug.Print AlignedPercentage(data(i), 12)
Next i

5. Statistical Analysis Display

Type StatisticsResult
Mean As Double
StdDev As Double
Confidence As Double
End Type

Function FormatStatistics(stats As StatisticsResult) As String
FormatStatistics = "Mean: " & FormatPercent(stats.Mean, 2) & vbCrLf & _
"Std Dev: " & FormatPercent(stats.StdDev, 2) & vbCrLf & _
"Confidence: " & FormatPercent(stats.Confidence, 1)
End Function

6. Conditional Formatting for Thresholds

Sub DisplayMetricWithThreshold(metric As Double, threshold As Double, lbl As Label)
lbl.Caption = FormatPercent(metric, 1)

If metric >= threshold Then
lbl.BackColor = vbGreen
lbl.ForeColor = vbWhite
ElseIf metric >= threshold * 0.8 Then
lbl.BackColor = vbYellow
lbl.ForeColor = vbBlack
Else
lbl.BackColor = vbRed
lbl.ForeColor = vbWhite
End If
End Sub

Error Handling

Function SafeFormatPercent(value As Variant) As String
On Error GoTo ErrorHandler

If IsNull(value) Then
SafeFormatPercent = "N/A"
Exit Function
End If

If Not IsNumeric(value) Then
SafeFormatPercent = "Invalid"
Exit Function
End If

SafeFormatPercent = FormatPercent(CDbl(value), 2)
Exit Function

ErrorHandler:
SafeFormatPercent = "Error"
End Function

Common errors: - Error 13 (Type mismatch): Occurs when expression cannot be converted to a numeric value. - Error 6 (Overflow): Can occur with extremely large or small values. - Error 5 (Invalid procedure call): May occur with invalid argument values.

Performance Considerations

FormatPercent is relatively fast for single values but can be optimized in loops
For large datasets, consider caching formatted values
The multiplication by 100 is automatic and efficient
Regional settings lookup may have slight overhead

Best Practices

Use appropriate decimal places for your context (financial: 2, statistics: 1, progress: 0)
Consider the audience when choosing precision
Handle division by zero before calling FormatPercent
Use consistent formatting throughout your application
Remember that the input is a decimal (0.5 = 50%)
Consider using parentheses for negative values in financial contexts

Comparison with Other Functions

FormatNumber: Does not multiply by 100 or add %, gives more control over formatting
FormatCurrency: Adds currency symbol instead of %, doesn't multiply by 100
Format with "%": More flexible but requires manual multiplication by 100
Str/CStr: No automatic multiplication or % symbol, no locale support

Limitations

Always multiplies by 100 (cannot be disabled)
Always adds trailing % character
Cannot customize the position of the % symbol
Limited control over negative number formatting compared to custom Format strings
Depends on regional settings which may vary across systems

Regional Settings Impact

The appearance of formatted percentages varies by locale:

US (English): 75.50%
European (many): 75,50%
Switzerland: 75.50%

FormatNumber - Formats numbers without percentage conversion
FormatCurrency - Formats as currency with symbol
Format - General-purpose formatting function
CDbl - Converts to Double for percentage calculations
Round - Rounds numbers before percentage formatting