Logo
×

Excel VBA Format Function

VBA Format Function

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

Syntax

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

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

Sub Format_Function()
 Dim val1 As String
 Dim val2 As String
 Dim val3 As String
 Dim val4 As String
 Dim val5 As String 
 val1 = Format(#11/11/2019 11:00:00 PM#)
 ' will return the String "11/11/2019 23:00".
 ActiveCell.Value = val1
 val2 = Format(#11/11/2019 11:00:00 PM#, "Long Date")
 ' will return the String "Monday, November 11, 2019".
 ActiveCell.Offset(1, 0).Value = val2 
 val3 = Format(#11/11/2019 11:00:00 PM#, "Medium Time")
 ' will return the String "11:00 PM".
 ActiveCell.Offset(2, 0).Value = val3
 val4 = Format(#11/11/2019 11:00:00 PM#, "mm/dd/yyyy")
 ' will return the String "Monday 11/11/2019 23:00:00".
 ActiveCell.Offset(3, 0).Value = val4
 val5 = Format(#11/11/2019 11:00:00 PM#, "dddd mm/dd/yyyy hh:mm:ss")
 ' will return the String "Monday 11/11/2019 23:00:00".
 ActiveCell.Offset(4, 0).Value = val5
 End Sub

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

Sub FormatFunction_Example2()
 Dim val1 As String
 Dim val2 As String, val3 As String
 Dim val4 As String, val5 As String
 val1 = Format(10000) 
 ' it will return the String "5/18/1927 0:00".
 ActiveCell.Value = val1
 val2 = Format(10000, "Currency")
 ' it will return a String "$10,000.00".
 ActiveCell.Offset(1, 0) = val2
 val3 = Format(2.88, "Percent") 
 ' it will return a String "288.00%".
 ActiveCell.Offset(2, 0) = val3
 val4 = Format(1000, "standard", vbThursday)
 ' it will return a String "9/26/1902".
 ActiveCell.Offset(3, 0) = val4
 val5 = Format(2.88, "0.0")
 ' str5 is now equal to the String "2.9".
 ActiveCell.Offset(4, 0) = val5
 End Sub

Output

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

Example 3

Sub FormatFunction_Example3()
 Dim val1 As String
 Dim val2 As String
 val1 = Format("Joe Jonas", ">")
 'will return the String "JOE JONAS".
 ActiveCell.Value = val1
 val2 = Format("97110777", "@@-@@-@@-@")
 'return the String "97-11-07-77". 
 ActiveCell.Offset(1, 0) = val2
 End Sub

Output

JOE JONAS
97-11-07-77

Related Topics

For Next loop in VBA

For Next Loop The ”For Next” loop is used for a fixed number of times. It works by implementing the loop for the specified number of times. In this, the user specifies how...

3 minutes read.

VBA Cell

What is VBA cell? Cells is one of the elements (workbook, worksheet, range) in Excel VBA, which refers to cells of the Excel worksheet.  In VBA, the cell is also a property...

5 minutes read.

CodeIgniter Architecture

Here we will understand the architecture and working of the CodeIgniter application, which helps you to elaborate all steps in simple ways. As the above image represents that whenever a request comes from the...

2 minutes read.

Excel VBA : With End-with Constructs

With End-with Constructs The With-End With construct enables the user to perform multiple operations on a single object. If you are going to perform several different actions on the same object and typing the same...

4 minutes read.

Excel VBA CDbl Function

VBA CDbl Function: The CDbl function in VBA converts an expression into a Double data type. Syntax CDbl (Expression) Parameter Expression (required) - This parameter represents the expression that that you want to convert to...

1 minute read.

Excel VBA TimeValue Function

Excel VBA TimeValue Function: The TimeValue function in VBA returns a Time from the specified String interpretation of a time /date where the date information for the given string is...

1 minute read.

Declaring a Variable in VBA

Declaring a Variable A variable is broadly described as a storage location combined with a name and representing a specific value. Declaring a variable is instructing the computer to reserve space...

2 minutes read.

Excel VBA Hour Function

The hour function in VBA returns the hour element for the specified time. Syntax Hour (Time) Parameter Time (required) – This parameter represents the time. Return This function returns the hour element for the specified time. Example 1 Sub HourFunction_Example1() ...

1 minute read.

Scope in Visual Basics

Definition of Scope The scope of any programming language implies the area of code where the variables will be identified, accessed, and used. Every variable has a scope associated with it. The scope of...

4 minutes read.

VBA Type Mismatch Error

What is a Type Mismatch Error? VBA Type Mismatch Error is a run time error in excel, which often occurs when the data types contained in a VBA code are not matched...

3 minutes read.

Excel VBA Month Function

The Mont function in VBA returns the month number for the specified date. Syntax Month (Date) Parameter Time (required) – This parameter represents the date. Return This function returns the month number for the specified date. Example 1 Sub MonthFunction_Example1() ...

1 minute read.

Excel VBA Replace Function

VBA Replace Function: The Replace function in VBA searches for a substring within the specified string and replaces its occurrences with a second substring. Syntax Replace (Expression, Find, Replace, [Start], [Count], [Compare]) Parameter Expression (required) – This parameter represents...

2 minutes read.

Excel VBA CDec Function

VBA CDec Function: The CDec function in VBA converts an expression into a Decimal data type. Syntax CDec (Expression) Parameter Expression (required) – This parameter represents the expression that you want to convert to a...

1 minute read.

Excel VBA Val Function

VBA Val Function: The Val function in VBA converts the given string into a numeric value. This function ignores spaces and continues to read the characters after space(s). It stops...

1 minute read.

Excel VBA While wend Loop

WHILE wend loop is used when the user is not sure how many times they want to execute the VBA code within the program. With a WHILE loop, the loop body may...

3 minutes read.

Excel VBA FormatDateTime Function

VBA FormatDateTime Function: The FormatDateTime function in VBA returns the result as a string after applying a date and/or time format to the supplied expression. Syntax FormatDateTime (Expression, [NamedFormat]) Parameter Expression (specified) – This parameter...

2 minutes read.

Excel VBA IsArray Function

VBA IsArray Function: The IsArray function in VBA returns a Boolean, showing whether the given variable is an Array or not. Syntax IsArray (VarName) Parameter VarName (required)- This parameter represents the variable that you want...

1 minute read.

Excel VBA Second Function

Excel VBA Second Function: The Second function in VBA returns the second element for the specified time.  Syntax Second (Time) Parameter Time (required) – This parameter represents the time. Return This function returns the second...

1 minute read.

Excel VBA Choose Function

VBA Choose Function: The Choose function in VBA chooses function selects the corresponding value from a list of arguments depending as per the specified index. Syntax Choose (Index, [Choice-1], [Choice-2], ...) Parameter Index (required) – This...

2 minutes read.

VBA Screen Updating

What is VBA Screen Updating property? Screen Updating is a VBA property which is used to display the output generation while running the code. If this property is enabled, we could see...

6 minutes read.