VBA FormatPercent Function

The VBA FormatPercent function is a useful tool for converting decimal numbers into a percentage format. This function takes a numeric value and converts it into a string representation of a percentage with the specified number of decimal places, using either the standard percent symbol (%) or the customized symbol of your choice. It can be used in a variety of scenarios, such as financial reports, data analysis, and data visualization.

VBA FormatPercent Function – Purpose, Syntax and Arguments

Purpose:

The main purpose of the FormatPercent function is to display decimal numbers in percentage format for easy understanding and readability. This function can also be used to perform calculations on data in percentage format without having to manually convert the numbers back to decimal form.

Syntax:

The syntax for the FormatPercent function is as follows:

FormatPercent(expression, [NumDigitsAfterDecimal], [UseParensForNegativeNumbers], [GroupDigits], [UseThousandSeparator])

Arguments:

• expression: The numeric value or expression to be formatted into a percentage.
• NumDigitsAfterDecimal: Optional. Specifies the number of digits to display after the decimal point. If omitted, the default value of 2 is used.
• UseParensForNegativeNumbers: Optional. Determines whether negative numbers should be enclosed in parentheses or displayed with a minus sign. Set to True for parentheses or False for a minus sign. If omitted, the default value of False is used.
• GroupDigits: Optional. Determines whether numbers should be grouped using the system separators for digit grouping. Set to True for grouping or False for no grouping. If omitted, the default value of True is used.
• UseThousandSeparator: Optional. Determines whether the system’s default thousand separator should be used. Set to True for a separator or False for no separator. If omitted, the default value of False is used.

Example:

Let’s take a simple example to demonstrate how the FormatPercent function works. Suppose we want to format the number 0.75 into a percentage with one decimal place, displaying negative numbers with parentheses and using a comma as the thousand separator. We could use the following code:

Dim result As String
result = FormatPercent(0.75, 1, True, True, False)
Debug.Print result

The output of this code would be (75.0%), with parentheses enclosing the negative number and a comma used for digit grouping.

Remarks:

• The NumDigitsAfterDecimal argument can also be a negative number, which would result in the specified number of digits before the decimal point being displayed.
• If the NumDigitsAfterDecimal argument is greater than the actual number of decimal places in the expression, the remaining digits will be filled with zeros.
• If the UseParensForNegativeNumbers argument is set to True, positive numbers will also be displayed with parentheses around them.
• The default number of decimal places and the use of parentheses can be changed in the system’s regional settings.

Important Notes:

• The FormatPercent function is only available in VBA, and not in the Excel worksheet as a formula.
• The FormatPercent function is a part of the VBA.Strings library, so the code must include a reference to this library in order to use the function.
• The VBA.FormatPercent function is essentially the same as the VBA.Format function with the Format argument set to “Percent”. The Format function can also be used to format numbers as percentages, but the FormatPercent function is more specific and easier to use for this purpose.

Understanding VBA FormatPercent Function with Examples

Visual Basic for Applications (VBA) is a programming language used in Microsoft Office applications to automate tasks and perform various operations. One of the most frequently used functions in VBA is the FormatPercent function, which allows users to convert a decimal number into a percentage value. In this blog post, we will discuss the various ways in which the FormatPercent function can be used in VBA, along with examples.

Basic Use of FormatPercent Function

Sub formatPercentExample1()
Dim num As Double
Dim percentValue As String
num = 0.852
percentValue = FormatPercent(num)
MsgBox "The percentage value of " & num & " is " & percentValue
End Sub

• The first line of code declares two variables, ‘num’ and ‘percentValue’ as double and string type respectively.
• The next line assigns a value of 0.852 to the ‘num’ variable.
• The third line uses the FormatPercent function to convert the decimal number into a percentage value and assigns it to the ‘percentValue’ variable.
• The last line displays a message box that shows the original decimal number and its corresponding percentage value.

Explanation: In this example, we have used the basic syntax of the FormatPercent function, which takes the decimal number as its argument and returns the percentage value with two decimal places by default. The returned value is stored in a string variable and can be used further in the code. The result of this example will be a message box that displays “The percentage value of 0.852 is 85.20%”.

Changing Number of Decimal Places

Sub formatPercentExample2()
Dim num As Double
Dim percentValue As String
num = 0.28
percentValue = FormatPercent(num, 5)
MsgBox "The percentage value of " & num & " is " & percentValue
End Sub

• In this example, we have used the same code as in example 1, with the only difference being the second argument in the FormatPercent function.
• The second argument specifies the number of decimal places in the percentage value.
• By default, the FormatPercent function returns the percentage value with two decimal places, but in this example, we have specified five decimal places.
• The message box will display “The percentage value of 0.28 is 28.00000%”.

Explanation: The FormatPercent function allows users to specify the number of decimal places in the percentage value. In this example, we have used five decimal places, but users can change it according to their preference.

Displaying Negative Values with Parentheses

Sub formatPercentExample3()
Dim num As Double
Dim percentValue As String
num = -0.352
percentValue = FormatPercent(num, , , , vbTrue)
MsgBox "The percentage value of " & num & " is " & percentValue
End Sub

• We have used the same code as in the previous examples, but with the addition of a fourth argument ‘vbTrue’.
• The FormatPercent function allows users to specify whether they want negative values to be displayed with parentheses or the minus sign (-).
• By default, negative values are displayed with a minus sign, but in this example, we have used ‘vbTrue’ to display negative values with parentheses.
• The message box will display “The percentage value of -0.352 is (35.20%)”.

Explanation: The fourth argument in the FormatPercent function specifies the display type for negative values. Using ‘vbTrue’ will display negative values with parentheses, and omitting this argument will display them with a minus sign.

Formatting Currency with FormatPercent Function

Sub formatPercentExample4()
Dim currency As Long
Dim percentValue As String
currency = 250000
percentValue = FormatPercent(currency / 1000000, 2, , , vbFalse) & "/M"
MsgBox "The percentage value of " & currency & " is " & percentValue
End Sub

• In this example, we have used the FormatPercent function to format the currency amount.
• The first argument in the function is the calculated value of the currency amount divided by one million, which will give us the percentage value.
• We have used ‘vbFalse’ to omit the parentheses and display the negative value with a minus sign, and then concatenated it with “/M” to indicate millions.
• The message box will display “The percentage value of 250000 is 25.00/M”.

Explanation: The FormatPercent function can be used to format any numerical value, not just decimal numbers. In this example, we have used the function to format a currency amount and added “/M” to indicate millions. Users can use this function in various scenarios where they need to format numerical values in a particular way.

Conclusion

The FormatPercent function is a powerful and versatile tool in VBA, which can help users convert decimal numbers into percentage values and also allows them to format the output according to their preference. It offers various functionalities, such as changing the number of decimal places, displaying negative values, and formatting currency values. With the examples discussed in this blog post, users can understand the syntax and usage of the FormatPercent function to incorporate it into their VBA projects.