Home / VBA / Top VBA Functions / VBA FORMAT Function (Syntax + Example)

VBA FORMAT Function (Syntax + Example)

Contents hide
Syntax
Arguments
Example
Notes
Related Functions

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.

Related Functions

INSTR | INSTRREV | LCASE | LEFT | LEN | LTRIM | MID | REPLACE | RIGHT | RTRIM | SPACE | STRCOMP | STRCONV | STRING | STRREVERSE | TRIM | UCASE