VBA Format Function
On a daily basis, we usually format dates, text & numeric data, In VBA, FORMAT function helps you out to format these types of data. VBA FORMAT function is categorized under Text/String function. It can be used either as procedure or function in a VBA editor window in Excel.
VBA FORMAT function returns or results in a formatted string from a string expression.
In VB macro window, when you click or enter CTRL + SPACE VBA Intellisense, a drop-down menu appears, which helps you out in writing the code & you can autocomplete words in the VB editor. After typing FORMAT and click on spacebar below-mentioned syntax appears.
Syntax of VBA Format Function in Excel
The syntax for VBA Format function in excel is as follows:
It contains one required or compulsory parameter and the remaining three as optional parameters.
Expression: (Compulsory or required argument): It represents or indicates an expression which you want to format or it is the value we want to format.
Format: It is a format, which you want to apply to the Expression argument that you have selected. It can be either of the below-mentioned formats.
Two types of format in excel:
- User-Defined Format
- Built-In or Predefined Format
Different types of data format
- VBA Date Format
- VBA Text Format
- VBA Number OR Numeric Format
[FirstDayOfWeek] (optional argument): A constant that specifies the first day of the week.
If left blank or argument is not entered then it uses the default value vbSunday (Sunday).
[FirstWeekofYear] (optional argument): A constant that specifies the first week of the year. It represents or indicates the first week of the year. If this argument left blank or argument not entered then uses the default value vbFirstJan1 (1st January).
Below mentioned are the Various Types of Predefined Date & Time Format
General Date, Short Date, Medium Date, Long Date, Short Time, Medium Time & Long Time.
Below mentioned are the Various Types of Predefined Number Format
Following characters can also be used to create a format expression of strings.
How to Use VBA Format in Excel?
Below are the different examples to use VBA Format Function in Excel
VBA Format – Example #1
Step 1: Go to the Developer tab and click on Visual Basic.
Step 2: To create a blank module, click on Insert and under the menu section select Module, so that a new blank module gets created.
Step 3: Under the Microsoft Excel objects, right-click on sheet 1 (VBA_Format) Insert and under the menu section select Module, so that a new blank module gets created.
VBA Format – Example #2
Now the blank module is created, it is also called a code window, where you can start writing VBA FORMAT function statement codes. Suppose, I have the numeric value “19049.83” let’s apply VB format function.
Step 1: In the VBA editor, I have given a name as VBA_FORMAT() after typing Sub
Code:
Sub VBA_FORMAT() End Sub
Step 2: As VB format function is categorized under text/string type Variables, DIM (Dimension) is used in a VBA code to declare a variable name and its type. Therefore, I define the variable as “string” data type.
Code:
Sub VBA_FORMAT() Dim A As String End Sub
Step 3: After declaring a variable, I need to assign a value to this variable, i.e. A = 19049.83. Here I assigned a Numeric value i.e. 19049.83 now I want this numeric value to appear in the message box. Therefore, I need to add msgbox.
Code:
Sub VBA_FORMAT() Dim A As String A = 19049.83 MsgBox A End Sub
Step 4: Now if I run a code, that numeric value appears in msg box, now in the next example let’s apply various formatting options.
For this numeric value, you can apply various numeric format options of your choice with the help of format function.
Step 5: Let’s apply predefined numeric format i.e. standard. Code format to be used (You just need to change the sub name & format type in the code)
Code:
Sub VBA_FORMAT() Dim A As String A = 19049.83 A = Format(A, "STANDARD") MsgBox A End Sub
Step 6: If you run the above code, it results in the below-mentioned output. similarly, you can test different numeric format (standard, currency, percentage), based on your choice.
Step 7: Let’s apply predefined Date format i.e. “Long date” in the FORMAT function argument, Date value needs to be supplied in the double quotes to get the correct result.
Code:
Sub VBA_FORMAT4() Dim A As String A = 4 - 12 - 2019 A = Format("04 - 12 - 2019", "long date") MsgBox A End Sub
Step 8: If you run the above code, it results in the below-mentioned output. similarly, you can test different date format (Short or medium), based on your choice
Things to Remember
Like predefined function, you can also use VBA User-Defined Format function with the below-mentioned syntax.
Format(n,”user’s format”)
e.g. Format(4879.6623, “0.00”) returns a value 4879.66
Recommended Articles
This is a guide to VBA Format. Here we discuss how to use VBA Format in Excel along with some practical examples and downloadable excel template. You can also go through our other suggested articles –
