The VBA Format Function

Related Functions:

Description

The VBA Format function applies a specified format to an expression and returns the result as a string.

The syntax of the function is:

Format( Expression, [Format], [FirstDayOfWeek] , [FirstWeekOfYear] )

Where the function arguments are:

Expression

The expression that you want to format.

[Format]

An optional argument specifying the format that is to be applied to the Expression.

This can be a user-defined format or one of the predefined named formats listed below:

Predefined Date Formats:

Format		Description
General Date	-	Displays a date as defined in your system's General Date setting. If a date only, no time is displayed; If a time only, no date is displayed.
Long Date	-	Displays a date as defined in your system's Long Date settings.
Medium Date	-	Displays a date as defined in your system's Medium Date settings.
Short Date	-	Displays a date as defined in your system's Short Date settings.
Long Time	-	Displays a time as defined in your system's Long Time settings.
Medium Time	-	Displays a time as defined in your system's Medium Time settings.
Short Time	-	Displays a time as defined in your system's Short Time settings.

Predefined Number Formats:

Format		Description
General Number	-	Displays a number as it is entered.
Currency	-	Displays a number with a currency symbol, using the thousand separator and decimal places, as defined in your system's currency setting.
Euro	-	Displays a number as a currency, with the euro currency symbol.
Fixed	-	Displays at least one digit to the left of the decimal place and follows the system settings for the number of decimal places to the right of the decimal place.
Standard	-	Displays the thousand separator and follows the standard system settings for the number of digits displayed at either side of the decimal place.
Percent	-	Displays a number multiplied by 100 and followed by the percent symbol; The format follows the standard system settings for the number of digits displayed at either side of the decimal place.
Scientific	-	Displays a number using scientific notation.
Yes/No	-	Displays No if the number is equal to zero or Yes otherwise.
True/False	-	Displays False if the number is equal to zero or True otherwise.
On/Off	-	Displays Off if the number is equal to zero or On otherwise.

If the [Format] argument is omitted, the function uses the system 'General' format for the Expression data type.

[FirstDayOfWeek]

An optional FirstDayOfWeek enumeration value, specifying the weekday that should be used as the first day of the week.

This can have any of the following values:

vbUseSystemDayOfWeek	-	The first day of the week is as specified in your system settings
vbSunday	-	Sunday
vbMonday	-	Monday
vbTuesday	-	Tuesday
vbWednesday	-	Wednesday
vbThursday	-	Thursday
vbFriday	-	Friday
vbSaturday	-	Saturday

If omitted, the [FirstDayOfWeek] argument uses the default value vbSunday.

[FirstWeekOfYear]

An optional FirstWeekOfYear enumeration value, specifying the week that should be used as the first week of the year.

This can have any of the following values:

vbSystem	-	The first week of the year is as specified in your system settings
vbFirstJan1	-	The week in which Jan 1st occurs
vbFirstFourDays	-	The first week that contains at least four days in the new year
vbFirstFullWeek	-	The first full week in the new year

If omitted, the [FirstWeekOfYear] argument uses the default value vbFirstJan1.

The following VBA code shows how the VBA Format function can be used to format the date and time 12/31/2015 12:00:00 in five different ways.

' Format the date/time 12/31/2015 12:00:00 in different ways.

								Dim dt1 As String

								Dim dt2 As String

								Dim dt3 As String

								Dim dt4 As String

								Dim dt5 As String

								dt1 = Format( #12/31/2015 12:00:00 PM# )

								' dt1 is now equal to the String "12/31/2015 12:00:00 PM".

								dt2 = Format( #12/31/2015 12:00:00 PM#, "Long Date" )

								' dt2 is now equal to the String "Thursday, December 31, 2015".

								dt3 = Format( #12/31/2015 12:00:00 PM#, "Medium Time" )

								' dt3 is now equal to the String "12:00 PM".

								dt4 = Format( #12/31/2015 12:00:00 PM#, "mm/dd/yyyy" )

								' dt4 is now equal to the String "12/31/2015".

dt5 = Format( #12/31/2015 12:00:00 PM#, "dddd mm/dd/yyyy hh:mm:ss" )
' dt5 is now equal to the String "Thursday 12/31/2015 12:00:00".

Note that, in the above examples:

In the first call to the Format function, the [Format] argument is omitted. Therefore, the current system default date/time format is used.
The second and third calls to the Format function use the predefined formats "Long Date" and "Medium Time". These format definitions will vary, according to your system date/time settings.
The last two calls to the Format function have been supplied with user-defined formats.

In the following VBA code, the VBA Format function is used to format the numeric values 50000 and 0.88 in different ways.

' Format the numbers 50000 and 0.88 in different ways.

								Dim str1 As String

								Dim str2 As String

								Dim str3 As String

								Dim str4 As String

								Dim str5 As String

								str1 = Format( 50000 )

								' str1 is now equal to the String "50000".

								str2 = Format( 50000, "Currency" )

								' str2 is now equal to the String "$50,000.00".

								str3 = Format( 0.88, "Percent" )

								' str3 is now equal to the String "88.00%".

								str4 = Format( 50000, "#,##0.0" )

								' str4 is now equal to the String "50,000.0".

str5 = Format( 0.88, "0.0" )
' str5 is now equal to the String "0.9".

Note that, in the above examples:

In the first call to the Format function, the [Format] argument is omitted. Therefore the current system default number format is used.
The second and third calls to the Format function use the predefined formats "Currency" and "Percentage". These format definitions may vary, according to your system settings.
The last two calls to the Format function have been supplied with user-defined formats.

The following example shows the VBA Format function used with two different format options for Strings.

' Format two text strings using different user-defined formats.

								Dim str1 As String

								Dim str2 As String

								str1 = Format( "John Smith", ">" )

								' str1 is now equal to the String "JOHN SMITH".

str2 = Format( "123456789", "@@@-@@@-@@@" )
' str2 is now equal to the String "123-456-789".

In the above VBA code:

In the first call to the Format function, the ">" character forces the supplied text to be formatted in upper case.
In the second call to the Format function, the "@" character represents a displayed character and the "-" character is text to be inserted in the specified positions of the String.