Formatting Numeric Values

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Microsoft® Visual Basic® for Applications (VBA) provides several functions that you can use to format numbers, including the FormatNumber, FormatCurrency, FormatPercent, and Format functions. Each of these functions returns a number formatted as a string.

The FormatNumber function formats a number with the comma as the thousands separator. You can specify the number of decimal places you want to appear. For example, calling the following code from the Immediate window prints "8,012.36":

? FormatNumber(8012.36)

The FormatCurrency function formats a number with a dollar sign, including two decimal places by default. Calling this code from the Immediate window prints "$10,456.45":

? FormatCurrency(10456.45)

The FormatPercent function formats a number as a percentage, including two decimal places by default. For example, calling this code from the Immediate window prints "80.00%":

? FormatPercent(4/5)

If you must have finer control over the formatting of a number, you can use the Format function to specify a custom format. For example, to display leading zeros before a number, you can create a custom format that includes placeholders for each digit. If a digit is absent, a zero appears in that position. The following procedure shows an example that returns a formatted string complete with leading zeros:

Function FormatLeadingZeros(lngNum As Long) As String
   ' Formats number with leading zeros.

   FormatLeadingZeros = Format$(lngNum, "00000")
End Function

For more information about creating custom formats, search the Visual Basic Reference Help index for "Format function."

See Also

Working with Numbers | The Integer, Long, and Byte Data Types | The Boolean Data Type | The Floating-Point Data Types | The Currency and Decimal Data Types | Conversion, Rounding, and Truncation | Using the Mod Operator | Performing Calculations on Numeric Arrays