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.
- 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.
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:
- Converting the value from cell A1 into a currency format.
- Converting the date from cell A2 into a long date.
- Converting the number from cell A3 into a boolean.
- You can also create your own format to use in the “format” argument.