FormatNumber - VBScript

Overview

The FormatNumber command in VBScript is used to convert a numeric value into a string representation with the specified number formatting options. It provides control over the decimal separator, thousand separator, currency symbol, and negative number display.

Syntax

FormatNumber(expression[, UseLeadingZeroes][, GroupDigits][, DecimalSeparator][, ThousandsSeparator][, CurrencySymbol][, NegativeSign])

Parameters:

  • expression: The numeric value to be formatted.
  • UseLeadingZeroes: (Optional) Boolean value indicating whether to use leading zeroes for fractional values (default: False).
  • GroupDigits: (Optional) Boolean value indicating whether to group digits (default: True).
  • DecimalSeparator: (Optional) Character used as the decimal separator (default: “.”).
  • ThousandsSeparator: (Optional) Character used as the thousands separator (default: “,”).
  • CurrencySymbol: (Optional) Currency symbol to prefix the number (default: None).
  • NegativeSign: (Optional) Character used to indicate negative numbers (default: “-“).

Options/Flags

  • UseLeadingZeroes: Adds leading zeroes to fractional values shorter than the specified number of decimal places.
  • GroupDigits: Groups digits into sets of three, separating them with the thousands separator.
  • DecimalSeparator: Specifies the character to use as the decimal separator, such as a period (.) or comma (,).
  • ThousandsSeparator: Specifies the character to use as the thousands separator, such as a comma (,) or a space.
  • CurrencySymbol: Prefixes the number with the specified currency symbol.
  • NegativeSign: Specifies the character to use to indicate negative numbers, such as a hyphen (-) or parentheses ().

Examples

Simple Formatting:

Dim price = 12345.67
Dim formattedPrice = FormatNumber(price)

With Thousands Separator:

Dim revenue = 123456789
Dim formattedRevenue = FormatNumber(revenue, True, True)

With Currency Symbol:

Dim amount = 1000
Dim formattedAmount = FormatNumber(amount, False, False, "$")

With Negative Sign:

Dim balance = -123.45
Dim formattedBalance = FormatNumber(balance, False, False, ".", ",", "", "(")

Common Issues

  • Leading Zeroes: If leading zeroes are not desired, set UseLeadingZeroes to False.
  • Grouping: If digit grouping is not desired, set GroupDigits to False.
  • Decimal Separator: Ensure the specified decimal separator matches the regional settings.
  • Thousands Separator: Use the appropriate separator for the target audience.
  • Currency Symbol: Include the currency symbol only if necessary.

Integration

The FormatNumber command can be combined with the MsgBox command to display formatted numbers in a message box. It can also be used in conjunction with Round or Fix for more precise formatting.

  • CStr: Converts a numeric value to a string.
  • NumberFormat: Provides advanced formatting options for numbers.