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 SubRun this code and you will get the result of every predefined number format in Excel message-box.
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 SubRun 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.
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 SubRun 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 SubRun this code and you will get the result of every predefined date format in Excel message-box.
Read More: How to Use IsDate Function in VBA (3 Examples)
Similar Readings:
- How to Use VBA InstRev Function (7 Suitable Examples)
- Use the VBA Chr Function (2 Examples)
- How to Use the Left Function in VBA in Excel (2 Examples)
- Call a Sub in VBA in Excel (4 Examples)
- How to Use VBA UCASE Function in Excel (4 Examples)
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 SubRun this code and you will get the result of all the user-defined date formats in the Excel message box that you provided here.
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 SubRun 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 SubNotice 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, "-@@@-@@-@@")
'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 SubRun this code and you will get the result for each format.
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.
