Formatting – Part 7

Other Formatting Functions

FormatNumber                        Returns an expression formatted as a number

FormatCurrency                       Returns an expression formatted as a number with a leading currency symbol ($)

FormatPercent                         Returns an expression formatted as a percentage (multiplied by 100) with a trailing % character.

The syntax for FormatNumber is:

FormatNumber(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]])

Where:

Expression	is the expression to be formatted
NumDigitsAfterDecimal	is the number of digits desired after the decimal point
IncludeLeadingDigit	is a True/False value indicating whether or not a zero should be included to the left of decimal values
UseParensForNegativeNumbers	is a True/False value indicating whether or not parentheses should be used for negative values
GroupDigits	is a True/False value indicating whether or not to use commas to group digits

The syntax for FormatCurrency and FormatPercent are exactly the same as FormatNumber.

For the examples below, assume dblTestNumber contains the value 12345.678

Expression	Result
FormatNumber(dblTestNumber, 2, True, True, True)	12,345.68
FormatCurrency(dblTestNumber, 2, True, True, True)	$12,345.68
FormatPrecent(dblTestNumber, 2, True, True, True)	1,234,567.80%

"Try It" Code:

Private Sub cmdTryIt_Click()

    Dim dblTestNumber   As Double

    dblTestNumber = Val(InputBox("Please enter a number:"))

    Print "Input: "; Tab(25); dblTestNumber

    Print "Using FormatNumber:"; Tab(25); FormatNumber(dblTestNumber, 2, True, True, True)

    Print "Using FormatCurrency:"; Tab(25); FormatCurrency(dblTestNumber, 2, True, True, True)

    Print "Using FormatPercent:"; Tab(25); FormatPercent(dblTestNumber, 2, True, True, True)

End Sub

Output:

Download the VB project code for the example above here.

FormatDateTime  Returns an expression formatted as a date or time.

Syntax:

FormatDateTime(Date[,NamedFormat])

The FormatDateTime function syntax has these parts:

Part	Description
Date	Required. Date expression to be formatted.
NamedFormat	Optional. Numeric value that indicates the date/time format used. If omitted, vbGeneralDate is used.

Settings:

The NamedFormat argument has the following settings:

Constant	Value	Description
vbGeneralDate	0	Display a date and/or time. If there is a date part, display it as a short date. If there is a time part, display it as a long time. If present, both parts are displayed.
vbLongDate	1	Display a date using the long date format specified in your computer's regional settings.
vbShortDate	2	Display a date using the short date format specified in your computer's regional settings.
vbLongTime	3	Display a time using the time format specified in your computer's regional settings.
vbShortTime	4	Display a time using the 24-hour format (hh:mm).

"Try It" Code:

Private Sub cmdTryIt_Click()

    Print "Using vbGeneralDate:"; Tab(25); FormatDateTime(Now, vbGeneralDate)

    Print "Using vbLongDate:"; Tab(25); FormatDateTime(Now, vbLongDate)

    Print "Using vbShortDate:"; Tab(25); FormatDateTime(Now, vbShortDate)

    Print "Using vbLongTime:"; Tab(25); FormatDateTime(Now, vbLongTime)

    Print "Using vbShortTime:"; Tab(25); FormatDateTime(Now, vbShortTime)

End Sub

Output:

Note that the previous example used built-in VB constants ("vbGeneralDate", "vbLongDate", etc.). There are numerous VB constants available that represent arguments used with various VB functions. These built-in constants typically begin with the letters "vb". In your code, it is good practice to use the constant name as opposed to the constant value.

In other words, use:

FormatDateTime(Now, vbLongDate)

instead of:

FormatDateTime(Now, 1)

Download the VB project code for the example above here.