Excel VBA Format Function
In VBA, we need to use the function “FORMAT” to apply the format to cells. Excel formattingExcel FormattingFormatting is a useful feature in Excel that allows you to change the appearance of the data in a worksheet. Formatting can be done in a variety of ways. For example, we can use the styles and format tab on the home tab to change the font of a cell or a table.read more is one of the important concepts to master. We all use the common formatting techniques in our daily work: date, time, number, and other important formatting codes. We press the format excel cellFormat Excel CellFormatting cells is an important technique to master because it makes any data presentable, crisp, and in the user’s preferred format. The formatting of the cell depends upon the nature of the data present.read more option in regular Excel worksheets and perform the formatting duty by applying the appropriate formatting code. However, in VBA, this is not as straightforward as our worksheet technique.
You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Format (wallstreetmojo.com)
Syntax
Expression: This is nothing but the value we want to format. In VAB technicality, it is called Expression.[Format]: What format do you want to apply to the selected expression? We have two kinds of formatting here: user-defined format and built-in format.Here, we have VBA date, number, and text formats.VBA date formats have a short date, long date, medium date, and general date.Number formats have currency, standard, percentage, scientific, yes or no, true or false, and on or off.[First Day of the Week]: What is the first day of your week? We can select any day from the list. Below is the list of days and appropriate codes.
[First Week of the Year]: What is the year’s first week? It specifies the week it should use as the year’s first week.
How to Use?
Let us apply this function practically to understand the functionality of the FORMAT function. Assume you have the number 8072.56489. You want to apply number formatting to it. Follow the below steps to apply number formatting to it.
Step 1: Start an excel macroExcel MacroA macro in excel is a series of instructions in the form of code that helps automate manual tasks, thereby saving time. Excel executes those instructions in a step-by-step manner on the given data. For example, it can be used to automate repetitive tasks such as summation, cell formatting, information copying, etc. thereby rapidly replacing repetitious operations with a few clicks. read more and define the variable as a “string” data type.
Code:
Sub Worksheet_Function_Example1()
Dim K As String
End Sub
Step 2: Assign a value to k as our number, 8072.56489.
Sub Worksheet_Function_Example1() Dim K As String
K = 8072.56489
End Sub
Step 3: Show the “k” value in the VBA message boxVBA Message BoxVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.
Sub Worksheet_Function_Example1() Dim K As String
K = 8072.56489 MsgBox K
End Sub
Step 4: If you run this macro, we will get the below result.
The result is as it is, we assigned the value to variable “k.” But we need to apply some formatting to this number to make it beautiful.
Step 5: Instead of directly assigning a value to “k,“ let us use the FORMAT function.
Sub Worksheet_Function_Example1() Dim K As String
K = Format( MsgBox K
End Sub
Step 6: Now, for Expression, assign the number 8072.56489.
Sub Worksheet_Function_Example1() Dim K As String
K = Format(8072.56489, MsgBox K
End Sub
Step 7: We can use a built-in format or our own formatting code in the formatting option. Now, we will use a built-in formatting style as “Standard.”
Sub Worksheet_Function_Example1() Dim K As String
K = Format(8072.56489, “Standard”) MsgBox K
End Sub
Step 8: Now, run this code and see the result of the message box.
We have comma (,) as thousand separators and decimal rounds up to two digits only.
Like this, we can use many other built-in formatting styles to apply the formatting. Below are some of the codes we have applied.
#1 – Currency Format
Sub Worksheet_Function_Example2() Dim K As String
K = Format(8072.56489, “Currency”) MsgBox K
End Sub
Result:
#2 – Fixed Format
Sub Worksheet_Function_Example3() Dim K As String
K = Format(8072.56489, “Fixed”) MsgBox K
End Sub
#3 – Percent Format
Sub Worksheet_Function_Example4() Dim K As String
K = Format(8072.56489, “Percent”) MsgBox K
End Sub
#4 – User-Defined Formats
Now, we will see some of the user-defined formats.
Sub Worksheet_Function_Example5() Dim K As String
K = Format(8072.56489, “#.##”) MsgBox K
End Sub
Sub Worksheet_Function_Example5() Dim K As String
K = Format(8072.56489, “#,##.##”) MsgBox K
End Sub
#5 – Date FORMAT
We have seen some important numbers of formatting techniques. We will have to use the FORMAT function to format the date in VBA.Format The Date In VBA.To format the date in VBA, we use the in-built FORMAT function that converts a date expression into the required format. In order to perform the function, one must enter a valid expression and format type.read more
We have written code to show the result of the date through the variable.
Sub Worksheet_Function_Example6() Dim K As String
K = 13 - 3 - 2019 MsgBox K
End Sub
When we run this code, we would not get an accurate date. Rather, the result is pathetic.
We need to assign the date format to get accurate dates. So, we first need to supply the date in double quotes and apply the date format.
Sub Worksheet_Function_Example6() Dim K As String
K = Format(“10 - 3 - 2019”, “Long Date”) MsgBox K
End Sub
We run this code now. We will get a proper long date.
The “Long Date” is a built-in format. Similarly, you can use the “Short Date” and “Medium Date” options.
Things to Remember
- The value returned by the FORMAT function is the string.We can also use our date, time, and number formatting codes, like how we use them in worksheet formatting.The FORMAT is a VBA functionVBA FunctionVBA functions serve the primary purpose to carry out specific calculations and to return a value. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions.read more available only in VBA, not in the worksheet.
Recommended Articles
This article has been a guide to VBA Format Function. Here, we learned how to use VBA Format Function for currency, fixed, percentage, and date formatting, along with some practical examples and a downloadable Excel template. Below are some useful Excel articles related to VBA: –
- VBA Data TypeVBA DIR FunctionVBA MODCreate VBA InputBox
