VBA Format Function in Excel (8 Uses with Examples)

The Format function in Excel is a built-in function categorized as a String/ Text function. It is also used as a VBA function in Excel. In this article, we will show you how to use the Format function of VBA in Excel.

Download Workbook

You can download the free practice Excel workbook from here.

VBA Format Function.xlsm

Introduction to the Format Function in VBA

The Format function in VBA is used to format any given value, this can be used to format dates, numbers or any other string.

Syntax

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

Parameter Description

Parameter	Required/ Optional	Description
Expression	Required	The value you want to format
Format	Optional	A pre-defined or user-defined valid format expression.
*FirstDayOfWeek*	Optional	A constant value that refers to the first day of the week.
*FirstWeekOfYear*	Optional	A constant value that refers to the first week of the year.

The 2nd parameter, Format, has the following pre-defined named formats:

Format	Description
Predefined Date Formats
General Date	Displays date based on the system’s settings.
Long Date	Displays date based on the system’s long date settings.
Medium Date	Displays date based on the system’s medium date settings.
Short Date	Displays date based on the system’s short date settings.
Long Time	Displays date based on the system’s long time settings.
Medium Time	Displays date based on the system’s medium time settings.
Short Time	Displays date based on the system’s short time settings.
Predefined Number Formats
General Number	Displays the number as it is written.
Currency	Displays numbers with thousand separators and decimal places, also currency symbols based on the system’s currency settings.
Fixed	Displays at least one digit to the left of the decimal point and displays the number of digits according to the system’s settings to the right of the decimal place.
Standard	Displays the thousand separator and at least one digit to the left of the decimal point and follows the standard system settings for the number of digits to display to the right of the decimal point.
Percent	Displays a percent value, a number multiplied by 100 followed by a percent symbol. This format follows the standard system settings for the number of digits to display at both sides of the decimal place.
Scientific	Displays a number with scientific notation.
Yes/No	Displays No if the number is equal to zero, otherwise displays Yes.
True/False	Displays False if the number is equal to zero, otherwise displays True.
On/Off	Displays Off if the number is equal to zero, otherwise displays On.

If the Format parameter is omitted, the function uses the system’s “General” pre-defined format for the Expression data type.

The 3rd parameter, FirstDayOfWeek, has the following values:

Number	Code	Description
0	vbUseSystemDayOfWeek	The first day of the week as per the system settings.
1	vbSunday	Sunday. If omitted, the function use this value as default.
2	vbMonday	Monday
3	vbTuesday	Tuesday
4	vbWednesday	Wednesday
5	vbThursday	Thursday
6	vbFriday	Friday
7	vbSaturday	Saturday

The 4th parameter, FirstWeekOfYear, has the following values:

Number	Code	Description
0	vbUseSystem	Uses the NLI API settings of the system.
1	vbFirstJan1	The week containing 1st January of the year. If omitted, the function use this value as default.
2	vbFirstFourDays	The first week that contains at least four days in the year.
3	vbFirstFullWeek	The first full week of the year.

8 Examples of Using VBA Format Function in Excel

In this section, you will know how we can use the Format function in VBA with Predefined and User-Defined Numbers, Dates, Texts and Strings.

1. VBA to Format Numbers with Predefined Formats

Below is the VBA code which covers all the predefined formats to Format numbers in Excel. The predefined formats are Currency, Fixed, Standard, Percent, Scientific, Yes/No, True/False, and On/Off.

Sub PreDefinedNumFormat()
MsgBox Format(12345678.9)
'Result: 12345678.9
'Format: General
'Just leaves the number as it is
MsgBox Format(12345678.9, "Currency")
'Result: $12,346,678.90
'Format: Currency
'Uses the systems currency settings
MsgBox Format(12345678.9, "Fixed")
'Result: 12345678.90
'Format: Fixed
'At least one digit before the decimal point and
'uses system settings for the decimal place after
MsgBox Format(12345678.9, "Standard")
'Result is: 12,345,678.90
'Format: Standard
'Thousand separators, decimal values and standard system settings
MsgBox Format(12345678.9, "Percent")
'Result: 1234567890.00%
'Format: Percent
'Multiplies by 100 with % symbol and standard system settings
MsgBox Format(12345678.9, "Scientific")
'Result: 1.23E+07
'Format: Scientific
'Displays the result with scientific notation
MsgBox Format(12345678.9, "Yes/No")
'Result: Yes
'Format: Yes/No
'No, if the number is zero; Yes otherwise
MsgBox Format(12345678.9, "True/False")
'Result: True
'Format: True/False
'False if the number is zero; True otherwise
MsgBox Format(12345678.9, "On/Off")
'Result: On
'Format: On/Off
'Off if the number is zero; On otherwise
End Sub

Run this code and you will get the result of every predefined number format in Excel message-box.

2. Macro to Format User-Defined Numbers

Excel’s Format function can convert user-defined numbers to strings.

Where,

0 displays a digit or zero
# displays a digit or nothing
A dot (.) works as the decimal placeholder
% is the percentage placeholder
The comma (,) is the thousands separator
Double quotes (“”) are used to add texts
The backslash (\) is used after a single character is added.

The VBA code with the implementation of these is shown below.

Sub UserDefinedNumFormat()
MsgBox Format(1.2, "000.00")
'Result: 001.20
MsgBox Format(12345.6789, "000.00")
'Result: 12345.68
MsgBox Format(1.2, "###.##")
'Result: 1.2
MsgBox Format(12345.6789, "###.##")
'Result: 12345.68
MsgBox Format(1.2, "\$.00")
'Result: $1.20
MsgBox Format(1234.567, "AABB0.00")
'Result: AABB1234.57
MsgBox Format(12345.6789, "000.00%")
'Result: 1234567.89%
MsgBox Format(12345.6789, "%000.00")
'Result: %12345.68
End Sub

Run this code and you will get the result of all the user-defined number formats in the Excel message box that you provided in the code.

3. Embed VBA to Format Numbers Based on Values

Format function has different rules to work with positive numbers, negative numbers, zero and Null values. These values are separated by a semicolon.

Sub FormatNumBasedOnValue()
MsgBox Format(1.2, "000.00;(000.00);\z\e\r\o;nothing")
'Result: 001.20
MsgBox Format(-1.2, "000.00;(000.00);\z\e\r\o;nothing")
'Result: (001.20)
MsgBox Format(0, "000.00;(000.00);\z\e\r\o;nothing")
'Result: zero
MsgBox Format(Null, "000.00;(000.00);\z\e\r\o;nothing")
'Result: nothing
End Sub

Run this code and you will get the result for each format.

Related Content: VBA EXP Function in Excel (5 Examples)

4. VBA to Format Dates with Predefined Formats in Excel

In Excel, dates have different built-in formats; the Format function can format those dates too. These formats are Long date, Medium date, Short date, Long time, Medium time and Short time.

Sub PredefinedDateFormat()
Dim iDate As Date
iDate = #1/31/2022 3:31:56 PM#
MsgBox Format(iDate, "General Date")
'Result: 1/31/2022 3:31:56 PM
MsgBox Format(iDate, "Long Date")
'Result: Monday, January 31, 2022
MsgBox Format(iDate, "Medium Date")
'Result: 31-Jan-22
MsgBox Format(iDate, "Short Date")
'Result: 1/31/2022
MsgBox Format(iDate, "Long Time")
'Result: 3:31:56 PM
MsgBox Format(iDate, "Medium Time")
'Result: 03:31 PM
MsgBox Format(iDate, "Short Time")
'Result: 15:31
End Sub

Run this code and you will get the result of every predefined date format in Excel message-box.

Similar Readings:

5. Macro to Format User-Defined Dates in Excel

Excel’s Format function can convert dates with user-defined formats. Characters such as d, m, y, w, q are usually used to create custom date formats.

Sub UserDefinedDateFormat()
Dim iDate As Date
iDate = #1/31/2022 3:31:56 PM#
MsgBox Format(iDate, "m/d/yy")
'Result: 1/31/2022
MsgBox Format(iDate, "mm-dd-yy")
'Result: 01-31-22
MsgBox Format(iDate, "mmm-dd-yy")
'Result: Jan-31-22
MsgBox Format(iDate, "mmmm-dd-yyyy")
'Result: January-31-2022
MsgBox Format(iDate, "mm-ddd-yy")
'Result: 01-Mon-22
MsgBox Format(iDate, "mm-dddd-yyyy")
'Result: 01-Monday-2022
MsgBox Format(iDate, "y")
'Result: 31
'Number of day in year 1-366
MsgBox Format(iDate, "ww")
'Result: 6
'Number of weeks in year 1-52
MsgBox Format(iDate, "q")
'Result: 1
'Quarter in year 1-4
End Sub

Run this code and you will get the result of all the user-defined date formats in the Excel message box that you provided here.

6. VBA Macro to Customize Time Format in Excel

Format function can convert time with user-defined formats. Characters such as h, n, s, am and pm are usually used to create custom time formats.

Sub CustomTimeFormat()
Dim iDate As Date
iDate = #1/31/2022 3:01:06 PM#
MsgBox Format(iDate, "h:n:s")
'Result: 15:1:6
MsgBox Format(iDate, "hh:nn:ss")
'Result: 15:01:06
MsgBox Format(iDate, "hh:nn:ss am/pm")
'Result: 03:01:06 pm
MsgBox Format(iDate, "hh:nn:ss AM/PM")
'Result: 03:01:06 PM
MsgBox Format(iDate, "hh:nn:ss a/p")
'Result: 03:01:06 p
MsgBox Format(iDate, "hh:nn:ss A/P")
'Result: 03:01:06 P
End Sub

Run this code and you will get the result of all the user-defined time formats in the Excel message-box that you provided in the code.

7. Embed VBA to Format Text in Excel

Format function can also be used to change the format of a text value inside the VBA code. For that, we have to use WorksheetFunction.Text to get the same result.

Sub FormatText()
MsgBox Format(1.2, "000.00")
'Result: 001.20
MsgBox WorksheetFunction.Text(1.2, "000.00")
'Result: 001.20
MsgBox Format(1.2, "###.##")
'Result: 1.2
MsgBox WorksheetFunction.Text(1.2, "###.##")
'Result: 1.2
End Sub

Notice the similarities between both of the functions in the picture shown below.

8. VBA to Format String or Characters in Excel

Format function can format any string or character to make the data more user friendly without changing the original value.

@ displays a character or space
& displays a character or nothing
! displays result from left to right
< or > is used to enforce lower or upper case

This process really helps update telephone numbers or other large numbers for better readability.

Sub FormatString()
Dim iStr As String
iStr = "XYZabc"
MsgBox Format(iStr, "-@@@-@@-@@")
'Result: - XY-Za-bc
MsgBox Format(iStr, "-&&&-&&-&&")
'Result: -XY-Za-bc
MsgBox Format(iStr, "-@@@-@@-@@-@@")
'Result: -  -XY-Za-bc
'When out of characters @ adds spaces and & adds nothing
MsgBox Format(iStr, "-&&&-&&-&&-&&")
'Result: --XY-Za-bc
'When out of characters @ adds spaces and & adds nothing
MsgBox Format(iStr, "!-@@@-@@-@@-@@")
'Result: -XYZ-ab-c -
MsgBox Format(iStr, "!-&&&-&&-&&-&&")
'Result: -XYZ-ab-c
MsgBox Format(iStr, ">")
'Result: XYZABC
MsgBox Format(iStr, "<")
'Result: xyzabc
MsgBox Format(1234567890, "@@@-@@@-@@@@")
'Result: 123-456-7890
MsgBox Format(1234567890, "@@@@-@@@-@@@")
'Result: 1234-567-890
End Sub