FormatNumber Function

Returns an expression formatted as a number.

Syntax

FormatNumber(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 number.

Remarks

The FormatNumber function provides a simple way to format numeric values using the system's locale settings. Unlike FormatCurrency, it does not add a currency symbol, making it ideal for general numeric display. Important Characteristics: - Uses system locale for formatting - Default: 2 decimal places (from regional settings) - Automatically adds thousand separators (if groupdigits=True) - Negative numbers can be displayed with parentheses or minus sign - Leading zeros controlled by regional settings or parameter - No currency symbol added - Returns empty string if expression is Null - More convenient than Format for simple number formatting - Less flexible than Format for custom patterns - Locale-aware (respects user's regional settings)

Typical Uses

Display numeric values in reports
Format percentages (without % symbol)
Show quantities and measurements
Display statistical values
Format calculated results
Show decimal precision in user interfaces
Display population or large numbers
Format scientific data

Examples

Basic Usage

Dim value As Double
value = 1234.567
' Default formatting (2 decimal places, system settings)
Debug.Print FormatNumber(value)              ' 1,234.57
' No decimal places
Debug.Print FormatNumber(value, 0)           ' 1,235
' Three decimal places
Debug.Print FormatNumber(value, 3)           ' 1,234.567
' Four decimal places
Debug.Print FormatNumber(value, 4)           ' 1,234.5670

Handling Negative Values

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

Control Leading Digits

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

Control Grouping

Dim largeNumber As Double
largeNumber = 1234567.89
' With grouping (thousands separators)
Debug.Print FormatNumber(largeNumber, 2, , , vbTrue)   ' 1,234,567.89
' Without grouping
Debug.Print FormatNumber(largeNumber, 2, , , vbFalse)  ' 1234567.89

Common Patterns

Display Statistical Results

Sub DisplayStatistics(data() As Double)
    Dim total As Double
    Dim average As Double
    Dim i As Long
    total = 0
    For i = LBound(data) To UBound(data)
        total = total + data(i)
    Next i
    average = total / (UBound(data) - LBound(data) + 1)
    Debug.Print "Count: " & FormatNumber(UBound(data) + 1, 0)
    Debug.Print "Total: " & FormatNumber(total, 2)
    Debug.Print "Average: " & FormatNumber(average, 2)
End Sub

Format Population Numbers

Function FormatPopulation(population As Long) As String
    FormatPopulation = FormatNumber(population, 0, , , vbTrue)
End Function
' Usage
Debug.Print "Population: " & FormatPopulation(8500000)  ' Population: 8,500,000

Display Measurement with Precision

Function FormatMeasurement(value As Double, decimals As Integer, _
                           unit As String) As String
    FormatMeasurement = FormatNumber(value, decimals) & " " & unit
End Function
' Usage
Debug.Print FormatMeasurement(12.5678, 2, "cm")  ' 12.57 cm
Debug.Print FormatMeasurement(98.6, 1, "°F")     ' 98.6 °F

Format Grid/Report Data

Sub PopulateDataGrid(grid As MSFlexGrid, values() As Double)
    Dim i As Long
    grid.Rows = UBound(values) + 2
    grid.TextMatrix(0, 0) = "Index"
    grid.TextMatrix(0, 1) = "Value"
    For i = LBound(values) To UBound(values)
        grid.TextMatrix(i + 1, 0) = FormatNumber(i, 0)
        grid.TextMatrix(i + 1, 1) = FormatNumber(values(i), 2)
    Next i
End Sub

Display Percentage (without symbol)

Function FormatPercentValue(value As Double, decimals As Integer) As String
    ' Convert to percentage value (multiply by 100)
    FormatPercentValue = FormatNumber(value * 100, decimals)
End Function
' Usage
Debug.Print FormatPercentValue(0.1234, 2)    ' 12.34
Debug.Print FormatPercentValue(0.5, 0)       ' 50

Format Score/Rating Display

Function FormatScore(score As Double, maxScore As Double) As String
    Dim percentage As Double
    percentage = (score / maxScore) * 100
    FormatScore = FormatNumber(score, 1) & " / " & _
                  FormatNumber(maxScore, 0) & " (" & _
                  FormatNumber(percentage, 1) & "%)"
End Function
' Usage
Debug.Print FormatScore(87.5, 100)  ' 87.5 / 100 (87.5%)

Display Large Numbers with Suffixes

Function FormatLargeNumber(value As Double) As String
    Const Million = 1000000
    Const Billion = 1000000000
    If Abs(value) >= Billion Then
        FormatLargeNumber = FormatNumber(value / Billion, 2) & "B"
    ElseIf Abs(value) >= Million Then
        FormatLargeNumber = FormatNumber(value / Million, 2) & "M"
    ElseIf Abs(value) >= 1000 Then
        FormatLargeNumber = FormatNumber(value / 1000, 2) & "K"
    Else
        FormatLargeNumber = FormatNumber(value, 2)
    End If
End Function
' Usage
Debug.Print FormatLargeNumber(1500000)       ' 1.50M
Debug.Print FormatLargeNumber(2500000000)    ' 2.50B

Format Comparison Display

Function FormatComparison(actual As Double, expected As Double) As String
    Dim difference As Double
    Dim percentDiff As Double
    difference = actual - expected
    If expected <> 0 Then
        percentDiff = (difference / expected) * 100
    End If
    FormatComparison = "Actual: " & FormatNumber(actual, 2) & vbCrLf & _
                       "Expected: " & FormatNumber(expected, 2) & vbCrLf & _
                       "Difference: " & FormatNumber(difference, 2, , vbTrue) & vbCrLf & _
                       "% Difference: " & FormatNumber(percentDiff, 2, , vbTrue)
End Function

`ListBox` Population with Numbers

Sub PopulateNumberList(lst As ListBox, values() As Double, decimals As Integer)
    Dim i As Long
    lst.Clear
    For i = LBound(values) To UBound(values)
        lst.AddItem FormatNumber(values(i), decimals)
    Next i
End Sub

Format Database Numeric Display

Function GetFormattedNumber(rs As ADODB.Recordset, fieldName As String, _
                            Optional decimals As Integer = 2) As String
    If IsNull(rs.Fields(fieldName).Value) Then
        GetFormattedNumber = "N/A"
    Else
        GetFormattedNumber = FormatNumber(rs.Fields(fieldName).Value, decimals)
    End If
End Function

Display Summary Totals

Sub DisplaySummary(items As Collection)
    Dim item As Variant
    Dim count As Long
    Dim total As Double
    Dim average As Double
    Dim maxVal As Double
    Dim minVal As Double
    count = items.Count
    total = 0
    maxVal = -1E+308  ' Smallest possible Double
    minVal = 1E+308   ' Largest possible Double
    For Each item In items
        total = total + item.Value
        If item.Value > maxVal Then maxVal = item.Value
        If item.Value < minVal Then minVal = item.Value
    Next item
    average = total / count
    Debug.Print "Summary Statistics"
    Debug.Print String(50, "=")
    Debug.Print "Count:   ", FormatNumber(count, 0)
    Debug.Print "Total:   ", FormatNumber(total, 2)
    Debug.Print "Average: ", FormatNumber(average, 2)
    Debug.Print "Maximum: ", FormatNumber(maxVal, 2)
    Debug.Print "Minimum: ", FormatNumber(minVal, 2)
End Sub

Advanced Usage

Flexible Number Formatter

Function FormatNumberEx(value As Double, _
                        Optional decimals As Integer = 2, _
                        Optional useParens As Boolean = False, _
                        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)
    FormatNumberEx = FormatNumber(value, decimals, leadingDigit, parens, groups)
End Function

Accounting-Style Formatter

Function FormatAccounting(value As Double, Optional decimals As Integer = 2) As String
    ' Always show parentheses for negatives, use grouping
    FormatAccounting = FormatNumber(value, decimals, vbTrue, vbTrue, vbTrue)
End Function
' Usage
Debug.Print FormatAccounting(1234.56)    ' 1,234.56
Debug.Print FormatAccounting(-789.12)    ' (789.12)

Dynamic Precision Formatter

Function FormatNumberDynamic(value As Double) As String
    ' Adjust precision based on magnitude
    If Abs(value) >= 1000 Then
        ' Large numbers: no decimals
        FormatNumberDynamic = FormatNumber(value, 0)
    ElseIf Abs(value) >= 1 Then
        ' Regular: 2 decimals
        FormatNumberDynamic = FormatNumber(value, 2)
    ElseIf Abs(value) >= 0.01 Then
        ' Small: 4 decimals
        FormatNumberDynamic = FormatNumber(value, 4)
    Else
        ' Very small: 6 decimals
        FormatNumberDynamic = FormatNumber(value, 6)
    End If
End Function

Table/Report Alignment

Function FormatNumberAligned(value As Double, width As Integer, _
                             Optional decimals As Integer = 2) As String
    Dim formatted As String
    formatted = FormatNumber(value, decimals, vbTrue, vbTrue, vbTrue)
    ' Right-align in field
    If Len(formatted) < width Then
        FormatNumberAligned = Space(width - Len(formatted)) & formatted
    Else
        FormatNumberAligned = formatted
    End If
End Function

Scientific Data Formatter

Function FormatScientificValue(value As Double, _
                               significantDigits As Integer) As String
    If Abs(value) >= 1000 Or Abs(value) < 0.01 Then
        ' Use scientific notation for very large/small
        FormatScientificValue = Format(value, "0." & String(significantDigits - 1, "0") & "E+00")
    Else
        ' Use regular formatting
        FormatScientificValue = FormatNumber(value, significantDigits)
    End If
End Function

Conditional Formatting

Function FormatNumberConditional(value As Double) As String
    If value > 0 Then
        FormatNumberConditional = "+" & FormatNumber(value, 2)
    ElseIf value < 0 Then
        FormatNumberConditional = FormatNumber(value, 2, , vbTrue)
    Else
        FormatNumberConditional = FormatNumber(0, 2)
    End If
End Function

Error Handling

Function SafeFormatNumber(value As Variant, _
                          Optional decimals As Integer = 2) As String
    On Error GoTo ErrorHandler
    If IsNull(value) Then
        SafeFormatNumber = "N/A"
    ElseIf Not IsNumeric(value) Then
        SafeFormatNumber = "Invalid"
    Else
        SafeFormatNumber = FormatNumber(CDbl(value), decimals)
    End If
    Exit Function
ErrorHandler:
    Select Case Err.Number
        Case 13  ' Type mismatch
            SafeFormatNumber = "Type Error"
        Case 6   ' Overflow
            SafeFormatNumber = "Overflow"
        Case 5   ' Invalid procedure call
            SafeFormatNumber = "Invalid"
        Case Else
            SafeFormatNumber = "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

FormatNumber 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 `FormatNumber` for General Numeric Display

' Good - Locale-aware, user-friendly
lblValue.Caption = FormatNumber(total, 2)
' Less portable - Hard-coded format
lblValue.Caption = Format(total, "0.00")

Handle Null Values

' Good - Check for Null
If Not IsNull(value) Then
    formatted = FormatNumber(value, 2)
Else
    formatted = "N/A"
End If

Be Consistent with Decimal Places

' Good - Use constants for consistency
Const DECIMAL_PLACES = 2
result1 = FormatNumber(value1, DECIMAL_PLACES)
result2 = FormatNumber(value2, DECIMAL_PLACES)

Comparison with Other Functions

`FormatNumber` vs `FormatCurrency`

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

`FormatNumber` vs `Format`

' FormatNumber - Simple, predefined
result = FormatNumber(1234.56, 2)
' Format - More control, custom patterns
result = Format(1234.56, "#,##0.00")

`FormatNumber` vs `FormatPercent`

' FormatNumber - No percent symbol, no multiplication
result = FormatNumber(0.1234 * 100, 2)  ' 12.34
' FormatPercent - Multiplies by 100, adds %
result = FormatPercent(0.1234)          ' 12.34%

`FormatNumber` vs `Str`/`CStr`

' FormatNumber - Full formatting
result = FormatNumber(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)
All parameters optional, making errors less obvious
Tristate parameters can be confusing
No built-in rounding mode control
Cannot format with custom patterns
No control over decimal/thousand separators
Limited to numeric types

Regional Settings Impact

The FormatNumber function behavior varies by locale: - United States: 1,234.56 - European Union: 1.234,56 (note decimal/thousand separators swapped) - Switzerland: 1'234.56 (apostrophe as separator) - India: 12,34,567.89 (different grouping pattern)

Format: More flexible formatting with custom patterns
FormatCurrency: Format numbers as currency
FormatPercent: Format numbers as percentages
FormatDateTime: Format date/time values
Round: Round numbers to specified decimal places
Int: Return integer portion of a number
CDbl: Convert expression to Double
IsNumeric: Check if expression can be converted to numeric