Excel VBA Format Function
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 |