How to use FormatCurrency function in VBA?

In VBA (Visual Basic for Applications), the FormatCurrency function is used to convert a numeric expression to a string representing a currency value. The function includes parameters for controlling the number of decimal places, whether or not to use the thousands separator, and what kind of parentheses or currency symbols to use.

Basic Syntax

VBA


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

Parameters

Expression: The numeric expression that you want to format as currency.
NumDigitsAfterDecimal: Optional. A numeric value specifying the number of digits to display after the decimal point. The default is -1, which means it uses the system default (usually 2).
IncludeLeadingDigit: Optional. Tristate constant that indicates whether or not a leading zero is displayed for fractional values. Can be True, False, or vbUseDefault.
UseParensForNegativeNumbers: Optional. Tristate constant that indicates whether or not to place negative values within parentheses. Can be True, False, or vbUseDefault.
GroupDigits: Optional. Tristate constant that indicates whether or not numbers are grouped using the group delimiter specified in the system’s regional settings. Can be True, False, or vbUseDefault.

Here is an example of how to use the FormatCurrency function

VBA


Sub ExampleFormatCurrency()

    Dim myValue As Double
    myValue = 1234.567
    
    ' Default formatting with system settings
    Debug.Print FormatCurrency(myValue)
    
    ' Specifying digits after the decimal point and use of thousands separator
    Debug.Print FormatCurrency(myValue, 2, True, True, True)

    ' Display with no decimals and no thousands separator
    Debug.Print FormatCurrency(myValue, 0, vbUseDefault, vbFalse, vbFalse)
    
    ' Negative number example
    myValue = -1234.567
    ' Parentheses for negative values, with two decimal places
    Debug.Print FormatCurrency(myValue, 2, vbUseDefault, True, vbTrue)
    
End Sub

When you run the ExampleFormatCurrency subroutine, it will output formatted currency strings to the Immediate Window (accessible in the VBA editor by pressing Ctrl+G). Keep in mind that FormatCurrency uses the regional currency settings configured on the host system where the VBA code is running. If you want to control the currency symbol, you might need to use the more general Format function where you can specify a custom format string.

facebooklinkedin