VBA Format Function

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

Syntax

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

Parameter

Expression (required)- This parameter represents the expression that you want to format.

Format (optional)- This parameter represents the format that is to be applied to the expression. The default value is General.

It can take the following values:

General Date: It shows a date as defined in your system's General Date setting.
Long Date: It shows a date as defined in your system's Long Date settings.
Medium Date: It shows a date as defined in your system's Medium Date settings.
Short Date: It shows a date as defined in your system's Short Date settings.
Long Time: It shows a time as defined in your system's Long Time settings.
Medium Time: It shows a time as defined in your system's Medium Time settings.
Short Time: It shows a time as defined in your system's Short Time settings.
General Number: It displays that same number as it is entered.
Currency: It displays a number with a currency symbol, using the thousand separator and decimal places
Euro: It shows a number as a currency, with the euro currency symbol.
Fixed: It shows at least one digit to the left of the decimal place
Standard: It displays the thousand separator and follows the standard system settings for the number of digits displayed at either side of the decimal place.
Percent: It shows a number multiplied by 100 and followed by the percent symbol
Scientific: It shows a number using scientific notation.
Yes/No: It shows No if the number is equal to zero else it displays Yes.
True/False: It shows False if the number is equal to zero else it displays True.
On/Off: It shows Off if the number is equal to zero else it displays On.

FirstDayOfWeek (optional)- This parameter specifies the weekday that should be used as the first day of the week. If skipped, the default value is vbSunday.

It can take the following values:

vbUseSystemDayOfWeek: It takes the first day of the week as specified in your computer’s settings.
vbSunday (default value): It takes value as Sunday
vbMonday: It takes value as Monday
vbTuesday: It takes value as Tuesday
vbWednesday: It takes value as Wednesday
vbThursday: It takes value as Thursday
vbFriday: It takes value as Friday
vbSaturday: It takes value as Saturday

FirstWeekOfYear (optional)- This parameter represents the week that should be used as the first week of the year.

It can take the following values:

vbSystem: It takes the first week of the year as specified in your computer’s settings.
vbFirstJan1 (default value): It takes the week in which Jan 1st occurs
vbFirstFourDays: It takes the first week that contains at least four days in the new year
vbFirstFullWeek: It takes the first full week in the new year

Return

This function returns the formatted expression as a string.

Example 1

Sub Format_Function()
 Dim val1 As String
 Dim val2 As String
 Dim val3 As String
 Dim val4 As String
 Dim val5 As String 
 val1 = Format(#11/11/2019 11:00:00 PM#)
 ' will return the String "11/11/2019 23:00".
 ActiveCell.Value = val1
 val2 = Format(#11/11/2019 11:00:00 PM#, "Long Date")
 ' will return the String "Monday, November 11, 2019".
 ActiveCell.Offset(1, 0).Value = val2 
 val3 = Format(#11/11/2019 11:00:00 PM#, "Medium Time")
 ' will return the String "11:00 PM".
 ActiveCell.Offset(2, 0).Value = val3
 val4 = Format(#11/11/2019 11:00:00 PM#, "mm/dd/yyyy")
 ' will return the String "Monday 11/11/2019 23:00:00".
 ActiveCell.Offset(3, 0).Value = val4
 val5 = Format(#11/11/2019 11:00:00 PM#, "dddd mm/dd/yyyy hh:mm:ss")
 ' will return the String "Monday 11/11/2019 23:00:00".
 ActiveCell.Offset(4, 0).Value = val5
 End Sub

Output

11/11/2019 23:00

Monday, November 11,2019

11:00 PM

11/11/2019

Monday 11/11/2019 23:00:00

Example 2

Sub FormatFunction_Example2()
 Dim val1 As String
 Dim val2 As String, val3 As String
 Dim val4 As String, val5 As String
 val1 = Format(10000) 
 ' it will return the String "5/18/1927 0:00".
 ActiveCell.Value = val1
 val2 = Format(10000, "Currency")
 ' it will return a String "$10,000.00".
 ActiveCell.Offset(1, 0) = val2
 val3 = Format(2.88, "Percent") 
 ' it will return a String "288.00%".
 ActiveCell.Offset(2, 0) = val3
 val4 = Format(1000, "standard", vbThursday)
 ' it will return a String "9/26/1902".
 ActiveCell.Offset(3, 0) = val4
 val5 = Format(2.88, "0.0")
 ' str5 is now equal to the String "2.9".
 ActiveCell.Offset(4, 0) = val5
 End Sub

Output

5/18/1927 0:00

$10,000.00

288.00%

9/26/1902

$2.90

Example 3

Sub FormatFunction_Example3()
 Dim val1 As String
 Dim val2 As String
 val1 = Format("Joe Jonas", ">")
 'will return the String "JOE JONAS".
 ActiveCell.Value = val1
 val2 = Format("97110777", "@@-@@-@@-@")
 'return the String "97-11-07-77". 
 ActiveCell.Offset(1, 0) = val2
 End Sub

Output

JOE JONAS

97-11-07-77

Excel VBA Format Function