VBA FORMAT Function (Syntax + Example)

The VBA FORMAT function is listed under the text category of VBA functions. When you use it in a VBA code, it returns a value formatted in the format you have specified. In simple words, you can use it to format an expression into a format that you can specify. There is one thing you need to note here the result that it returns is the string data type.

Syntax

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

Arguments

• Expression: The expression that you want to format.
• [Format]: The format which you want to apply to the expression [This is an optional argument and if omitted VBA takes General by default].
• [FirstDayOfWeek]: A string to define the first day of the week [This is an optional argument and if omitted vbSunday by default].
  • vbUseSystemDayOfWeek – As per the system settings.
  • vbSunday – Sunday
  • vbMonday – Monday
  • vbTuesday – Tuesday
  • vbWednesday – Wednesday
  • vbThursday – Thursday
  • vbFriday – Friday
  • vbSaturday – Saturday
• [FirstWeekOfYear]: A string to define the first week of the year [This is an optional argument and if omitted vbFirstJan1 by default].
  • vbSystem – As per the system settings.
  • vbFirstJan1 – The week in which the 1st Day of Jan occurs.
  • vbFirstFourDays – The first week that contains at least four days in the new year.
  • vbFirstFullWeek – The first full week in the new year.

Example

To practically understand how to use VBA FORMAT function, you need to go through the below example where we have written a vba code by using it:

Sub example_FORMAT()
Range("B1").Value = Format(Range("A1"), "Currency")
Range("B2").Value = Format(Range("A2"), "Long Date")
Range("B3").Value = Format(Range("A3"), "True/False")
End Sub

In the above example, we have used FORMAT with three different predefined formats:

1. Converting the value from cell A1 into a currency format.
2. Converting the date from cell A2 into a long date.
3. Converting the number from cell A3 into a boolean.

Notes

• You can also create your own format to use in the “format” argument.