VBA FormatCurrency Function

The FormatCurrency function is a powerful VBA tool used to format a numeric value into a currency format. This function is particularly useful when working with financial data or creating reports that require displaying monetary values. It allows for the customization of the currency format based on the user’s preferences, including the currency symbol, decimal places, thousands separator, and more. This function is available in all versions of VBA and can be used in any VBA-supported application, such as Microsoft Excel, Access, or Word.

VBA FormatCurrency Function – Purpose, Syntax and Arguments

Purpose:

The main purpose of the FormatCurrency function is to convert a numeric value into a formatted currency string. This allows for a more professional and user-friendly display of monetary values. It also enables the user to easily change the currency format without having to modify the underlying data. This function is commonly used in financial and accounting applications, but it can also be applied in various other scenarios such as creating invoices, sales reports, or budget analysis.

Syntax:

The syntax for the FormatCurrency function is as follows:

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

Arguments:

• Expression: This is the required argument and represents the numeric value that we want to format into currency. It can be a number, a string that contains a numeric value, or a cell reference within the VBA code.
• NumDigitsAfterDecimal: This is an optional argument that specifies the number of decimal places to be displayed in the formatted currency. The default value is -1, which means that the currency format follows the regional settings on the user’s computer. If this argument is set to 0, the currency will be rounded to the nearest integer.
• IncludeLeadingDigit: This is an optional argument that determines whether to include the leading digit of the currency value. By default, it is set to False, which means that the leading digit will not be included. If set to True, the currency value will be prefixed with the leading digit, even if it is zero.
• UseParensForNegativeNumbers: This is an optional argument that specifies whether to enclose negative currency values in parentheses. By default, it is set to False, which means that negative values will be formatted with a leading minus sign. If set to True, negative values will be enclosed in parentheses.
• GroupDigits: This is an optional argument that determines whether to use a thousands separator for the currency value. The default value is True, which means that the thousands separator will be added. If set to False, no thousands separator will be used.

Example:

Let’s say we have a spreadsheet with a list of product prices in column A and we want to format them as US currency with two decimal places and a thousands separator. We can use the FormatCurrency function in VBA to achieve this. Below is an example of the VBA code and the result:

Sub FormatCurrencyExample()
  Dim i As Integer
  
  'loop through each cell in column A
  For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
  
    'format the cell value as currency using FormatCurrency function
    Range("A" & i).Value = FormatCurrency(Range("A" & i).Value, , , , True)
  
  Next i
End Sub

The above code will convert all numeric values in column A into a formatted currency string with two decimal places and a thousands separator. The result will look like this:
Before: A1 = 2050, A2 = 500, A3 = 753467, A4 = 85.50
After: A1 = $2,050.00, A2 = $500.00, A3 = $753,467.00, A4 = $85.50

Remarks:

• The FormatCurrency function uses the regional settings of the user’s computer to determine the currency format. This means that it will display the currency symbol, decimal places, and thousands separator according to the user’s regional settings.
• If the user wants to change the default currency settings, they can do so by changing the regional settings on their computer.
• The FormatCurrency function is also compatible with non-western currency symbols, such as the Japanese yen or Chinese yuan, as long as the regional settings on the user’s computer are set accordingly.
• This function can also be used to format negative values, in which case the default format is to display the negative sign before the currency symbol. However, by setting the UseParensForNegativeNumbers argument to True, negative values can be displayed within parentheses.

Important Notes:

• The FormatCurrency function only works with numeric values. If the expression argument contains non-numeric data, an error will occur.
• This function is not limited to formatting currencies only. It can also be used to format other types of data, such as percentages or scientific notations, by adjusting the optional arguments accordingly.
• When dealing with large datasets, the use of FormatCurrency function can have a significant impact on the performance of the VBA code. It is recommended to use other methods, such as custom number formatting, if performance is a concern.

VBA FormatCurrency function is a versatile tool that offers a simple and efficient way to format numeric values into currency strings. It provides a customizable solution for displaying monetary values, which is essential for creating professional-looking reports and financial documents. With its wide range of optional arguments, users have the flexibility to customize the currency format according to their needs. This function is an excellent addition to any VBA programmer’s toolkit and is a valuable asset when dealing with financial data.

Understanding VBA FormatCurrency Function with Examples

Example 1: Basic Usage of FormatCurrency Function

The FormatCurrency function is used to convert a numeric value into a string representing the currency format. This format can be specified by the user to match the currency used in their region.

Dim num as Double
Dim strCurrency as String
num = 1234.56
strCurrency = FormatCurrency(num)
MsgBox strCurrency

In this example, the FormatCurrency function is used to convert the numeric value 1234.56 into a string representing the default currency format used in the region. The resulting string, “£1,234.56” (assuming the currency is set to British pounds), is then displayed in a message box.

• The first line declares a variable num as a Double data type. This will be used to store the numeric value that we want to convert into a currency format.
• The second line declares a variable strCurrency as a String data type. This will be used to store the converted currency value.
• Next, we assign a value of 1234.56 to the variable num.
• The FormatCurrency function is then used to convert the numeric value in num into a string representing the currency format. This converted value is then stored in the variable strCurrency.
• Finally, the converted currency value is displayed in a message box using the MsgBox function.

Example 2: Specifying Currency Format

The FormatCurrency function also allows the user to specify the currency format they want to use. This is done by adding a second argument to the function, specifying the desired currency code.

Dim num as Double
Dim strCurrency as String
num = 1234.56
strCurrency = FormatCurrency(num, "EUR")
MsgBox strCurrency

In this example, the FormatCurrency function is used to convert the numeric value 1234.56 into a string representing the Euro currency format. The resulting string, “€1,234.56”, is then displayed in a message box.

• The first three lines are similar to the previous example, declaring the num and strCurrency variables and assigning a value to num.
• The FormatCurrency function now has a second argument, “EUR”, which specifies the currency code for Euro.
• The converted currency value is then stored in the strCurrency variable and displayed in a message box.

Example 3: Formatting Negative Numbers with Parentheses

The FormatCurrency function also allows the user to specify how negative numbers should be displayed by using a third argument called IncludeLeadingNeg. When set to True, negative numbers will be displayed within parentheses.

Dim num1 as Double, num2 as Double
Dim strCurrency1 as String, strCurrency2 as String
num1 = 1234.56
num2 = -1234.56
strCurrency1 = FormatCurrency(num1, , True)
strCurrency2 = FormatCurrency(num2, , True)
MsgBox "Positive: " & strCurrency1 & vbNewLine & "Negative: " & strCurrency2

In this example, we have two numeric values, one positive and one negative. The IncludeLeadingNeg argument is set to True in both cases, resulting in the negative number being displayed within parentheses.

• The first three lines are similar to the previous examples.
• The IncludeLeadingNeg argument is set to True in both cases.
• The converted currency values are then stored in the corresponding strCurrency variables.
• The message box displays both converted values, one positive and one negative, with the negative value shown in parentheses.

Example 4: Specifying Leading and Trailing Characters

The FormatCurrency function also allows the user to specify characters that should be displayed before and after the converted currency value. This is done by using the CurrencySymbol and IncludeTrailingText arguments.

Dim num as Double
Dim strCurrency as String
num = 1234.56
strCurrency = FormatCurrency(num, , , , "€", True)
MsgBox strCurrency

In this example, the FormatCurrency function is used to convert the numeric value 1234.56 into a string representing the Euro currency format. The resulting string, “€1,234.56 euro”, is then displayed in a message box.

• The first three lines are similar to the previous examples.
• The CurrencySymbol argument is set to “€”, adding the Euro symbol before the currency value.
• The IncludeTrailingText argument is set to True, adding the word “euro” after the currency value.
• The converted currency value is then stored in the strCurrency variable and displayed in a message box.

Example 5: Using FormatCurrency with Arrays

The FormatCurrency function can also be used with arrays. This makes it possible to easily convert multiple numeric values into currency formats.

Dim arrNum(1 to 3) as Double
Dim arrCurrency(1 to 3) as String
Dim i as Integer
arrNum(1) = 1234.56
arrNum(2) = 3456.78
arrNum(3) = 9876.54
For i = 1 to 3
  arrCurrency(i) = FormatCurrency(arrNum(i))
Next i
For i = 1 to 3
  MsgBox "Value " & i & " is: " & arrCurrency(i)
Next i

In this example, an array of numeric values is converted into strings representing the default currency format. The resulting strings are then stored in another array, which can be displayed in a message box using a loop.

• The first three lines declare an array to store the numeric values and another array to store the converted currency values.
• The For loop is used to iterate through the values in the arrNum array.
• The FormatCurrency function is used to convert each numeric value into a string representing the currency format, which is then stored in the arrCurrency array.
• The For loop is used again to display each converted currency value in a message box, along with its corresponding index.

Conclusion

In conclusion, the FormatCurrency function is a very useful tool in VBA for converting numeric values into currency formats. By using its various arguments, users can easily customize the format and display of converted currency values. This simplifies data presentation and makes it easier to work with financial data in VBA.