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.

Here is the syntax for the `FormatCurrency` function:

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:

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