FormatCurrency Function

Returns an expression formatted as a currency value using the currency symbol defined in the system control panel.

Syntax

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

Parameters

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

Settings

The includeleadingdigit, useparensfornegativenumbers, and groupdigits arguments have the following settings: - 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 currency value.

Remarks

The FormatCurrency function provides a simple way to format numbers as currency values using the system's locale settings. It automatically applies the currency symbol, decimal separator, thousand separator, and negative number formatting according to regional settings. Important Characteristics: - Uses system locale for currency symbol and formatting - Default: 2 decimal places (from regional settings) - Automatically adds thousand separators - Negative numbers can be displayed with parentheses or minus sign - Leading zeros controlled by regional settings or parameter - Currency symbol position depends on locale (before or after amount) - Returns empty string if expression is Null - More convenient than Format for simple currency formatting - Less flexible than Format for custom patterns - Locale-aware (respects user's regional settings)

Typical Uses

• Display prices and monetary amounts
• Format financial reports
• Show invoice totals
• Display account balances
• Format transaction amounts
• Create currency-formatted exports
• Display budget figures
• Show cost calculations

Examples

Basic Usage

Dim amount As Double
amount = 1234.567
' Default formatting (2 decimal places, system settings)
Debug.Print FormatCurrency(amount)           ' $1,234.57
' No decimal places
Debug.Print FormatCurrency(amount, 0)        ' $1,235
' Three decimal places
Debug.Print FormatCurrency(amount, 3)        ' $1,234.567
' Negative with parentheses
Debug.Print FormatCurrency(-500, , , vbTrue) ' ($500.00)

Handling Negative Values

Dim balance As Double
balance = -1250.50
' Default negative (with minus sign)
Debug.Print FormatCurrency(balance)          ' -$1,250.50
' Parentheses for negative
Debug.Print FormatCurrency(balance, 2, , vbTrue)  ' ($1,250.50)
' No parentheses (explicit)
Debug.Print FormatCurrency(balance, 2, , vbFalse) ' -$1,250.50

Control Leading Digits

Dim fraction As Double
fraction = 0.75
' With leading zero (default)
Debug.Print FormatCurrency(fraction)         ' $0.75
' No leading zero
Debug.Print FormatCurrency(fraction, 2, vbFalse)  ' $.75
' Explicit leading zero
Debug.Print FormatCurrency(fraction, 2, vbTrue)   ' $0.75

Common Patterns

Format Invoice Line Items

Sub DisplayInvoiceLines(items As Collection)
    Dim item As Variant
    Dim total As Double
    Debug.Print "Item", "Quantity", "Price", "Amount"
    Debug.Print String(60, "-")
    total = 0
    For Each item In items
        Debug.Print item.Name, _
                    item.Quantity, _
                    FormatCurrency(item.Price), _
                    FormatCurrency(item.Quantity * item.Price)
        total = total + (item.Quantity * item.Price)
    Next item
    Debug.Print String(60, "-")
    Debug.Print "Total:", , , FormatCurrency(total)
End Sub

Format Account Balance with Parentheses

Function FormatAccountBalance(balance As Double) As String
    ' Show negative balances in parentheses (accounting style)
    FormatAccountBalance = FormatCurrency(balance, 2, vbTrue, vbTrue, vbTrue)
End Function
' Usage
Debug.Print FormatAccountBalance(1500)       ' $1,500.00
Debug.Print FormatAccountBalance(-250.50)    ' ($250.50)

Create Price Display

Function FormatPrice(price As Double, showCents As Boolean) As String
    If showCents Then
        FormatPrice = FormatCurrency(price, 2)
    Else
        FormatPrice = FormatCurrency(price, 0)
    End If
End Function
' Usage
Debug.Print FormatPrice(19.99, True)         ' $19.99
Debug.Print FormatPrice(19.99, False)        ' $20

Display Transaction Summary

Sub ShowTransactionSummary(credits As Double, debits As Double)
    Dim balance As Double
    Debug.Print "Transaction Summary"
    Debug.Print String(40, "=")
    Debug.Print "Credits:  ", FormatCurrency(credits)
    Debug.Print "Debits:   ", FormatCurrency(debits, 2, , vbTrue)
    Debug.Print String(40, "-")
    balance = credits - debits
    Debug.Print "Balance:  ", FormatCurrency(balance, 2, , vbTrue)
End Sub

Format Budget Report

Sub PrintBudgetReport()
    Dim budgeted As Double
    Dim actual As Double
    Dim variance As Double
    budgeted = 50000
    actual = 48500
    variance = actual - budgeted
    Debug.Print "Budget Report"
    Debug.Print String(50, "=")
    Debug.Print "Budgeted:", FormatCurrency(budgeted, 0)
    Debug.Print "Actual:  ", FormatCurrency(actual, 0)
    Debug.Print "Variance:", FormatCurrency(variance, 0, , vbTrue)
    If variance < 0 Then
        Debug.Print "Status: Under budget"
    Else
        Debug.Print "Status: Over budget"
    End If
End Sub

ListBox/ComboBox Population

Sub PopulatePriceList(lstPrices As ListBox, prices() As Double)
    Dim i As Long
    lstPrices.Clear
    For i = LBound(prices) To UBound(prices)
        lstPrices.AddItem FormatCurrency(prices(i))
    Next i
End Sub

Format for Database Display

Function GetFormattedPrice(rs As ADODB.Recordset, fieldName As String) As String
    If IsNull(rs.Fields(fieldName).Value) Then
        GetFormattedPrice = "N/A"
    Else
        GetFormattedPrice = FormatCurrency(rs.Fields(fieldName).Value)
    End If
End Function

Calculate and Display Tax

Function DisplayPriceWithTax(basePrice As Double, taxRate As Double) As String
    Dim tax As Double
    Dim total As Double
    tax = basePrice * taxRate
    total = basePrice + tax
    DisplayPriceWithTax = "Price: " & FormatCurrency(basePrice) & vbCrLf & _
                          "Tax: " & FormatCurrency(tax) & vbCrLf & _
                          "Total: " & FormatCurrency(total)
End Function
' Usage
MsgBox DisplayPriceWithTax(100, 0.08)

Format Payment Schedule

Sub ShowPaymentSchedule(loanAmount As Double, months As Integer, rate As Double)
    Dim payment As Double
    Dim i As Integer
    Dim balance As Double
    payment = loanAmount / months
    balance = loanAmount
    Debug.Print "Payment Schedule"
    Debug.Print String(50, "=")
    Debug.Print "Month", "Payment", "Balance"
    Debug.Print String(50, "-")
    For i = 1 To months
        Debug.Print i, FormatCurrency(payment), FormatCurrency(balance)
        balance = balance - payment
    Next i
End Sub

Compare Values

Function ComparePrices(price1 As Double, price2 As Double) As String
    Dim difference As Double
    difference = price1 - price2
    ComparePrices = FormatCurrency(price1) & " vs " & FormatCurrency(price2) & _
                    " (Difference: " & FormatCurrency(difference, 2, , vbTrue) & ")"
End Function

Shopping Cart Total

Function GetCartSummary(items As Collection) As String
    Dim item As Variant
    Dim subtotal As Double
    Dim tax As Double
    Dim shipping As Double
    Dim total As Double
    subtotal = 0
    For Each item In items
        subtotal = subtotal + (item.Price * item.Quantity)
    Next item
    tax = subtotal * 0.08
    shipping = 5.99
    total = subtotal + tax + shipping
    GetCartSummary = "Subtotal: " & FormatCurrency(subtotal) & vbCrLf & _
                     "Tax:      " & FormatCurrency(tax) & vbCrLf & _
                     "Shipping: " & FormatCurrency(shipping) & vbCrLf & _
                     "Total:    " & FormatCurrency(total)
End Function

Advanced Usage

Flexible Currency Formatter

Function FormatCurrencyEx(amount As Double, _
                          Optional decimals As Integer = 2, _
                          Optional useParens As Boolean = True, _
                          Optional useGroups As Boolean = True) As String
    Dim leadingDigit As VbTriState
    Dim parens As VbTriState
    Dim groups As VbTriState
    leadingDigit = vbTrue
    parens = IIf(useParens, vbTrue, vbFalse)
    groups = IIf(useGroups, vbTrue, vbFalse)
    FormatCurrencyEx = FormatCurrency(amount, decimals, leadingDigit, parens, groups)
End Function

Multi-Currency Support

Function FormatMultiCurrency(amount As Double, currencyCode As String) As String
    ' Simple multi-currency display (uses FormatCurrency then replaces symbol)
    Dim formatted As String
    formatted = FormatCurrency(amount)
    Select Case UCase(currencyCode)
        Case "USD"
            ' Keep default $ symbol
            FormatMultiCurrency = formatted
        Case "EUR"
            FormatMultiCurrency = Replace(formatted, "$", "€")
        Case "GBP"
            FormatMultiCurrency = Replace(formatted, "$", "£")
        Case "JPY"
            FormatMultiCurrency = Replace(formatted, "$", "¥")
            FormatMultiCurrency = Replace(FormatMultiCurrency, ".00", "")
        Case Else
            FormatMultiCurrency = currencyCode & " " & Format(amount, "#,##0.00")
    End Select
End Function

Financial Statement Formatter

Type FinancialLine
    Description As String
    Amount As Double
    IsSubtotal As Boolean
End Type
Function FormatFinancialStatement(lines() As FinancialLine) As String
    Dim result As String
    Dim i As Long
    Dim line As String
    result = "Financial Statement" & vbCrLf
    result = result & String(60, "=") & vbCrLf
    For i = LBound(lines) To UBound(lines)
        line = lines(i).Description
        ' Right-align amounts
        line = line & Space(40 - Len(line))
        line = line & FormatCurrency(lines(i).Amount, 2, vbTrue, vbTrue, vbTrue)
        If lines(i).IsSubtotal Then
            result = result & String(60, "-") & vbCrLf
        End If
        result = result & line & vbCrLf
    Next i
    FormatFinancialStatement = result
End Function

Dynamic Precision Formatter

Function FormatCurrencyDynamic(amount As Double) As String
    ' Use different precision based on amount magnitude
    If Abs(amount) >= 1000000 Then
        ' Millions: no decimals
        FormatCurrencyDynamic = FormatCurrency(amount, 0) & "M"
    ElseIf Abs(amount) >= 1000 Then
        ' Thousands: no decimals
        FormatCurrencyDynamic = FormatCurrency(amount, 0)
    ElseIf Abs(amount) >= 1 Then
        ' Regular: 2 decimals
        FormatCurrencyDynamic = FormatCurrency(amount, 2)
    Else
        ' Small amounts: 4 decimals
        FormatCurrencyDynamic = FormatCurrency(amount, 4)
    End If
End Function

Conditional Formatting

Function FormatProfitLoss(amount As Double) As String
    ' Format with color indicators for profit/loss
    If amount > 0 Then
        FormatProfitLoss = "[GREEN]+" & FormatCurrency(amount)
    ElseIf amount < 0 Then
        FormatProfitLoss = "[RED]" & FormatCurrency(amount, 2, , vbTrue)
    Else
        FormatProfitLoss = "[BLACK]" & FormatCurrency(0)
    End If
End Function

Grid/Report Alignment

Function FormatCurrencyAligned(amount As Double, width As Integer) As String
    Dim formatted As String
    formatted = FormatCurrency(amount, 2, vbTrue, vbTrue, vbTrue)
    ' Right-align in field
    If Len(formatted) < width Then
        FormatCurrencyAligned = Space(width - Len(formatted)) & formatted
    Else
        FormatCurrencyAligned = formatted
    End If
End Function

Error Handling

Function SafeFormatCurrency(value As Variant, _
                            Optional decimals As Integer = 2) As String
    On Error GoTo ErrorHandler
    If IsNull(value) Then
        SafeFormatCurrency = "N/A"
    ElseIf Not IsNumeric(value) Then
        SafeFormatCurrency = "Invalid"
    Else
        SafeFormatCurrency = FormatCurrency(CDbl(value), decimals)
    End If
    Exit Function
ErrorHandler:
    Select Case Err.Number
        Case 13  ' Type mismatch
            SafeFormatCurrency = "Type Error"
        Case 6   ' Overflow
            SafeFormatCurrency = "Overflow"
        Case Else
            SafeFormatCurrency = "Error"
    End Select
End Function

Common Errors

• Error 13 (Type Mismatch): Expression cannot be converted to numeric
• Error 6 (Overflow): Value too large for Double
• Error 5 (Invalid procedure call): Invalid decimal places parameter

Performance Considerations

• FormatCurrency is fast for simple formatting
• Slightly slower than Format for custom patterns
• Faster than building format strings manually
• Locale lookups cached by system
• Avoid repeated calls in tight loops if possible
• Consider caching formatted values for display

Best Practices

Use FormatCurrency for User-Facing Amounts

' Good - Locale-aware, user-friendly
lblPrice.Caption = FormatCurrency(price)
' Less portable - Hard-coded format
lblPrice.Caption = "$" & Format(price, "0.00")

Handle Null Values

' Good - Check for Null
If Not IsNull(amount) Then
    formatted = FormatCurrency(amount)
Else
    formatted = "N/A"
End If

Be Consistent with Negative Formatting

' Good - Use same style throughout application
Const USE_PARENS = vbTrue
formatted = FormatCurrency(balance, 2, , USE_PARENS)

Comparison with Other Functions

FormatCurrency vs Format

' FormatCurrency - Simple, locale-aware
result = FormatCurrency(1234.56)
' Format - More control, custom patterns
result = Format(1234.56, "$#,##0.00")

FormatCurrency vs FormatNumber

' FormatCurrency - Adds currency symbol
result = FormatCurrency(1234.56)        ' $1,234.56
' FormatNumber - No currency symbol
result = FormatNumber(1234.56)          ' 1,234.56

FormatCurrency vs Str/CStr

' FormatCurrency - Full formatting
result = FormatCurrency(1234.56)        ' $1,234.56
' Str - Basic conversion, no formatting
result = Str(1234.56)                   ' " 1234.56"
' CStr - Basic conversion
result = CStr(1234.56)                  ' "1234.56"

Limitations

• Uses system locale (cannot specify different locale)
• Limited to system's currency symbol
• Cannot customize symbol position
• All parameters optional, making errors less obvious
• Tristate parameters can be confusing
• No built-in rounding mode control
• Cannot format multiple currencies in same session

Regional Settings Impact

The FormatCurrency function behavior varies by locale: - United States: $1,234.56 - United Kingdom: £1,234.56 - European Union: €1.234,56 (note decimal/thousand separators) - Japan: ¥1,235 (typically no decimal places)

Related Functions

• Format: More flexible formatting with custom patterns
• FormatNumber: Format numbers without currency symbol
• FormatPercent: Format numbers as percentages
• FormatDateTime: Format date/time values
• CCur: Convert expression to Currency type
• CDbl: Convert expression to Double type