VBA FORMAT Function

HomeVBA Functions LIST (Category Wise)How to use the VBA FORMAT Function (Syntax + Example)

What is VBA FORMAT Function

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 which formatted as 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 which it returns is the string data type.

how to use it

To use VBA's FORMAT function you need to understand its syntax and arguments:

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 use FORMAT Function in VBA

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 the cell A1 into a currency format.
  2. Converting the date from the cell A2 into a long date.
  3. Converting the number from the cell A3 into a boolean.

Notes

Below are some important points which you need to take care while using FORMAT function in VBA.

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

More TEXT (String) Functions

About the Author

puneet one point one

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.