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":
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":
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%":
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")
© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited