| Microsoft Office 2000/Visual Basic Programmer's Guide | |
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 need 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
The FormatLeadingZeros procedure is available in the modNumbers module in VBA.mdb in the ODETools\V9\Samples\OPG\Samples\CH07 subfolder on the Office 2000 Developer CD-ROM.
For more information about creating custom formats, search the Visual Basic Reference Help index for "Format function."