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.


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.

VBA to Format Numbers with Predefined Formats

Read More: How to Use VBA IsNumeric Function (9 Examples)


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.

VBA to Format User-Defined Numbers

Related Content: How to Use Fix Function in Excel VBA (4 Examples)


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.

VBA to Format Dates with Predefined Formats

Read More: How to Use IsDate Function in VBA (3 Examples)


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.

VBA to Format User-Defined Dates

Read More: VBA Date Function (12 Uses of Macros with Examples)


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.

Read More: How to Use VBA TimeValue Function (6 Relevant Examples)


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.

Related Content: How to Return a Value in VBA Function (Both Array and Non-Array Values)


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, "[email protected]@@[email protected]@[email protected]@")
'Result: - XY-Za-bc
MsgBox Format(iStr, "-&&&-&&-&&")
'Result: -XY-Za-bc
MsgBox Format(iStr, "[email protected]@@[email protected]@[email protected]@[email protected]@")
'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, "[email protected]@@[email protected]@[email protected]@[email protected]@")
'Result: -XYZ-ab-c -
MsgBox Format(iStr, "!-&&&-&&-&&-&&")
'Result: -XYZ-ab-c
MsgBox Format(iStr, ">")
'Result: XYZABC
MsgBox Format(iStr, "<")
'Result: xyzabc
MsgBox Format(1234567890, "@@@[email protected]@@[email protected]@@@")
'Result: 123-456-7890
MsgBox Format(1234567890, "@@@@[email protected]@@[email protected]@@")
'Result: 1234-567-890
End Sub

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

VBA to Format String or Characters

Read More: How to Use VBA Str Function in Excel (4 Examples)


Conclusion

This article showed you how to use the Format function in Excel with VBA. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.


Related Articles

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo