Formatting Numbers

VBA provides several functions that you can use to format numbers: FormatNumber, FormatCurrency, FormatPercent, and Format.
All of these functions return a number formatted as a string.


The FormatNumber function formats a number with the comma as the thousands separator.

FORMATNUMBER(expression [,numdigitsafter] [,includeleadingdigit] [,negativeuseparens] [,groupdigits]) 
Debug.Print FormatNumber(8012.36)
Debug.Print FormatNumber(8012.36,0) ' will remove decimal places


The FormatCurrency function formats a number with a dollar sign, including two decimal places by default.

FORMATCURRENCY(expression [,numdigitsafter] [,includeleadingdigit] [,negativeuseparens] [,groupdigits]) 
Debug.Print FormatCurrency(10456.45)
Debug.Print FormatCurrency(10456.45)


The FormatPercent function formats a number as a percentage, including two decimal places by default.

FORMATPERCENT(expression [,numdigitsafter] [,includeleadingdigit] [,negativeuseparens] [,groupdigits]) 
Debug.Print FormatPercent(4/5)
Debug.Print FormatPercent(4/5)


The Format function to specify a custom format if you need a very specific number format.

FORMAT(expression [,format] [,firstdayofweek] [,firstweekofyear]) 
Debug.Print Format(1234.56, "##,##0")
Debug.Print Format(1234.56, "##,##0")

© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited TopPrevNext