VBA Format Function

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

Syntax

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

Output

11/11/2019 23:00
Monday, November 11,2019
11:00 PM
11/11/2019
Monday 11/11/2019 23:00:00
Excel VBA Format Function

Example 2

Output

5/18/1927 0:00
$10,000.00
288.00%
9/26/1902
$2.90
Excel VBA Format Function

Example 3

Output

JOE JONAS
97-11-07-77

Pin It on Pinterest

Share This