VBA Format function in Excel is categorized as a Text/String function in VBA. It is a built-in function in MS Office Excel. VBA Format function returns a formatted string from a string expression. This function has one required parameter and three optional parameters. If Format argument is left blank, then the function behaves like the CSTR function.
This function use as a VBA function and can’t use as a Excel Worksheet function. The VBA Format function can be used in either procedure or function in a VBA editor window in Excel. We can use this VBA Format function any number of times in any number of procedures or functions. In the following section we learn what is the syntax and parameters of the Format function, where we can use this Format function and real-time examples in VBA.
- Overview
- Syntax of VBA Format Function
- Parameters or Arguments
- VBA Date Formats
- VBA Number Formats
- VBA Text Formats
- Enumeration values of the FirstDayOfWeek
- Enumeration values of the FirstWeekOfYear
- Where we can apply or use the VBA Format Function?
- Example 1: Format Date and Time
- Example 2: Format Numbers and currency
- Example 3: Format Text/String
- Example 4: User Defined Format
- Instructions to Run VBA Macro Code
- Other Useful Resources
Syntax of VBA Format Function
The syntax of the VBA Format function is
Format(Expression, [Format], [FirstDayOfWeek],[FirstWeekOfYear])
Note: This Format function returns a string.
Parameters or Arguments
This function has one mandatory parameter and three optional parameters for the Format Function.
Where
Expression: The Expression is a mandatory argument. It represents an expression which you want to format.
Format: The Format is an optional argument. It represents the user defined or below specified built-in format. It is applied to an Expression.
VBA Date Formats: Here are the following list of built-in Date formats.
Format | Description |
---|---|
General Date | It displays date as defined in your system general Date settings. It displays short date and short time. |
Long Date | It displays date as defined in your system Long Date settings |
Medium Date | It displays date as defined in your system Medium Date settings |
Short Date | It displays date as defined in your system Short Date settings |
Long Time | It displays time as defined in your system Long time settings |
Medium Time | It displays time as defined in your system Medium time settings |
Short Time | It displays time as defined in your system Short time settings |
VBA Number Formats: Here are the following list of built-in Number formats.
Format | Description |
---|---|
General Number | It displays a number without any thousand separators. |
Currency | It displays a number with thousand separators and two decimal places. |
Euro | It displays a number with the euro currency symbol. |
Fixed | It displays at least one integer digit and two decimal places number. |
Standard | It displays a number with thousand separators, at least one integer digit and two decimal places. |
Percent | It displays the number to the percentage form and adds % sign and rounds it up to two decimal places. |
Scientific | It displays a number in scientific notation. |
Yes/No | It displays No if the number is equal to zero or Yes otherwise. |
True/False | It displays False if the number is equal to zero or True otherwise. |
On/Off | It displays Off if the number is equal to zero or On otherwise. |
VBA Text Formats: Here are the following list of Text and Memo formats.
Format Symbol | Description |
---|---|
@ | Text character is required. |
& | Text character is not required. |
< | Convert all characters to lowercase. |
> | Convert all characters to uppercase. |
FirstDayOfWeek: The FirstDayOfWeek is an optional argument. It represents the first day of week. This argument uses the default value vbSunday (Sunday).
VBA Constant | Value | Description |
---|---|---|
vbUseSystem | 0 | Uses the NLS API setting (The first day of the week specified in system settings) |
VbSunday | 1 | Sunday |
vbMonday | 2 | Monday |
vbTuesday | 3 | Tuesday |
vbWednesday | 4 | Wednesday |
vbThursday | 5 | Thursday |
vbFriday | 6 | Friday |
vbSaturday | 7 | Saturday |
FirstWeekOfYear: The FirstWeekOfYear is an optional argument. It represents the first week of the year. This argument uses the default value vbFirstJan1 (1st January).
VBA Constant | Value | Description |
---|---|---|
vbUseSystem | 0 | Uses the NLS API setting. |
vbFirstJan1 | 1 | The week that conatins 1st Jan in the year. |
vbFirstFourDays | 2 | The first week that conatins atleast 4 days in the year. |
vbFirstFullWeek | 3 | The first full week of the year. |
Where we can apply or use the VBA Format Function?
We can use this VBA Format function in MS Office 365, MS Excel 2016, MS Excel 2013, 2011, Excel 2010, Excel 2007, Excel 2003, Excel 2016 for Mac, Excel 2011 for Mac, Excel Online, Excel for iPhone, Excel for iPad, Excel for Android tablets and Excel for Android Mobiles.
Example 1: Format Date and Time
Here is a simple example of the VBA Format function. Here you can see multiple examples of VBA Date and Time Format function.
'Format Date and Time Sub VBA_Format_Function_Ex1() Dim sDate As String, sTime As String Dim sDateTime As String Dim sOutput As String, sOutput1 As String, sOutput2 As String sDate = Date: sTime = Time sDateTime = sDate & " " & sTime sOutput = Format(sDateTime) MsgBox "General Date & Time Format : " & vbCrLf & sOutput, vbInformation, "VBA Format Function" '-------------------------------------------------------------------------------------- sOutput1 = Format(sDate, "Medium Date") sOutput2 = Format(sTime, "Medium time") MsgBox "Medium Date Format : " & sOutput1 & vbCrLf & "Medium Time Format : " & sOutput2, vbInformation, "VBA Format Function" '-------------------------------------------------------------------------------------- sOutput1 = Format(sDate, "Long Date") sOutput2 = Format(sTime, "Long time") MsgBox "Long Date Format : " & sOutput1 & vbCrLf & "Long Time Format : " & sOutput2, vbInformation, "VBA Format Function" '-------------------------------------------------------------------------------------- sOutput1 = Format(sDate, "dddd mm/dd/yyyy") sOutput2 = Format(sTime, "hh:mm:ss AMPM") MsgBox "User defined Date Format : " & sOutput1 & vbCrLf & "User defined Time Format : " & sOutput2, vbInformation, "VBA Format Function" End Sub
Output: Here is the screen shot of the first example output.
Example 2: Format Numbers and Currency
Here is a simple example of the VBA Format function. Here you can see multiple examples of VBA Number and Currency Format function.
'Format Numbers and currencies Sub VBA_Format_Function_Ex2() Dim sValue As String, sValue1 As String Dim sOutput As String sValue = 0.1234: sValue1 = 12345 sOutput = Format(sValue) ' General Number Format MsgBox "General Number Format : " & sOutput, vbInformation, "VBA Format Function" '-------------------------------------------------------------------------------------- sOutput = Format(sValue1, "Standard") ' MsgBox "Standard Format : " & sOutput, vbInformation, "VBA Format Function" '-------------------------------------------------------------------------------------- sOutput = Format(sValue1, "Fixed") ' MsgBox "Fixed Format : " & sOutput, vbInformation, "VBA Format Function" '-------------------------------------------------------------------------------------- sOutput = Format(sValue, "Currency") ' MsgBox "Currency Format : " & sOutput, vbInformation, "VBA Format Function" '-------------------------------------------------------------------------------------- sOutput = Format(sValue, "Percent") ' MsgBox "Percent Format : " & sOutput, vbInformation, "VBA Format Function" End Sub
Output: Here is the screen shot of the second example output.
Example 3: Format Text/String
Here is a simple example of the VBA Format function. Here you can see multiple examples of VBA Text/String Format function.
'Format Text/String Sub VBA_Format_Function_Ex3() Dim sValue As String, sValue1 As String Dim sOutput As String sValue = "Welcome to VBAF1": sValue1 = "999999999" sOutput = Format(sValue, ">") MsgBox "User defined Format : " & sOutput, vbInformation, "VBA Format Function" '-------------------------------------------------------------------------------------- sOutput = Format(sValue, "<") MsgBox "User defined Format : " & sOutput, vbInformation, "VBA Format Function" '-------------------------------------------------------------------------------------- sOutput = Format(sValue1, "@@@@@@@@@") MsgBox "User defined Format : " & sOutput, vbInformation, "VBA Format Function" '-------------------------------------------------------------------------------------- sOutput = Format(sValue1, "@@@-@@@-@@@") MsgBox "User defined Format : " & sOutput, vbInformation, "VBA Format Function" '-------------------------------------------------------------------------------------- sOutput = Format(sValue1, "@@@") MsgBox "User defined Format : " & sOutput, vbInformation, "VBA Format Function" '-------------------------------------------------------------------------------------- sOutput = Format(sValue1, "@@@-&&&-@@@") MsgBox "User defined Format : " & sOutput, vbInformation, "VBA Format Function" End Sub
Output: Here is the screen shot of the third example output.
Example 4: User Defined Format
Here is a simple example of the VBA Format function. Here you can see multiple examples of VBA User Defined Format function.
'User Defined Format Sub VBA_Format_Function_Ex4() Dim sValue As String, sValue1 As String Dim sOutput As String sValue = 12345.678: sValue1 = 0.1357 sOutput = Format(sValue, "0.000") MsgBox "User defined Format : " & sOutput, vbInformation, "VBA Format Function" '-------------------------------------------------------------------------------------- sOutput = Format(sValue, "##,##0") ' MsgBox "User defined Format : " & sOutput, vbInformation, "VBA Format Function" '-------------------------------------------------------------------------------------- sOutput = Format(sValue, "$##,##0.00") ' MsgBox "User defined Format : " & sOutput, vbInformation, "VBA Format Function" '-------------------------------------------------------------------------------------- sOutput = Format(sValue, "£##,##0.00") ' MsgBox "User defined Format : " & sOutput, vbInformation, "VBA Format Function" '-------------------------------------------------------------------------------------- sOutput = Format(sValue1, "0%") ' MsgBox "User defined Format : " & sOutput, vbInformation, "VBA Format Function" '-------------------------------------------------------------------------------------- sOutput = Format(sValue1, "0.00%") ' MsgBox "User defined Format : " & sOutput, vbInformation, "VBA Format Function" End Sub
Output: Here is the screen shot of the fourth example output.
Instructions to Run VBA Macro Code or Procedure:
You can refer the following link for the step by step instructions.
Instructions to run VBA Macro Code
Other Useful Resources:
Click on the following links of the useful resources. These helps to learn and gain more knowledge.
VBA Tutorial VBA Functions List VBA Arrays in Excel Blog
VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers
Very Nice Post. I certainly love this web site, keep up the good work.
Thanks for your valuable feedback!