The VBA Format Function

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.



VBA Format Function Examples

Example 1 - Format VBA Dates and Times

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:


Example 2 - Format VBA Numbers and Currencies

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:


Example 3 - Format VBA Strings

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: