VBA Format

Excel VBA Format Function

Format function in VBA is used to format the given values in the desired format, this function can be used for formatting dates or numbers or any trigonometrical values, this function has basically two mandatory arguments, one is the input which is taken in the form of a string and the second argument is the type of format we want to use for example if we use Format (.99,” Percent”) this will give us the result as 99%.

In VBA, we need to use the function called “FORMAT” to apply to 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. The common formatting techniques we all use in our daily work are “date format, time format, number formatting, and other important formatting codes.” In regular excel worksheets, we just simply hit the format excel cell option and perform the formatting duty by applying the appropriate formatting code. However, in VBA, this isn’t that straight forward as our worksheet technique.

VBA Format

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Format (wallstreetmojo.com)

Syntax

VBA Format Formula
  • Expression: This is nothing but the value we want to format. In VAB technicality, it is called as Expression.
  • [Format]: What is the format you want to apply to the expression you have selected? We have two kinds of formatting here, one is the user-defined format, and the second one is the built-in format.
    Here we have VBA Date Formats, Number Formats, 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.
VBA Format Table 1
  • [First Week of the Year]: What is the first week of the year? This specifies the week that should be used as the very first week of the year.
VBA Format Table 2

How to Use?

You can download this VBA Format Template here – VBA Format Template

Ok, let’s apply this function practically to understand the functionality of the FORMAT function. Assume you have the number 8072.56489, and you want to apply number formatting to it. Follow the below steps to apply number formatting to it.

Step 1: Start an excel macroExcel MacroMacro in excel is a series of instructions in code that helps automate manual tasks, thereby saving time. Excel executes those instructions in a step-by-step manner on the given data. read more and define the variable as a “string” data type.

Code:

Sub Worksheet_Function_Example1()

  Dim K As String

End Sub
VBA Format Example 1

Step 2: Assign a value to k as our number i.e., 8072.56489

Code:

Sub Worksheet_Function_Example1()
  Dim K As String

  K = 8072.56489

End Sub
VBA Format Example 1-1

Step 3: Show “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.

Code:

Sub Worksheet_Function_Example1()
  Dim K As String

  K = 8072.56489
  MsgBox K

End Sub
VBA Format Example 1-2

Step 4: If you run this macro, we will get the below result.

VBA Format Example 1-3

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’s use the FORMAT function.

Code:

Sub Worksheet_Function_Example1()
  Dim K As String

  K = Format(
  MsgBox K

End Sub
VBA Format Example 1-4

Step 6: Now for Expression, assign the number 8072.56489.

Code:

Sub Worksheet_Function_Example1()
  Dim K As String

  K = Format(8072.56489,
  MsgBox K

End Sub
VBA Format Example 1-5

Step 7: In the formatting option, we can either use a built-in format, or we can use our own formatting code. Now I will use a built-in formatting style as “Standard.”

Code:

Sub Worksheet_Function_Example1()
  Dim K As String

  K = Format(8072.56489, "Standard")
  MsgBox K

End Sub

VBA Format Example 1-6
Step 8: Now run this code and see the result of the message box.

VBA Format Example 1-7

Ok, we have got comma (,) as thousand separators, and decimal is rounded 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 I have applied.

#1 – Currency Format

Code:

Sub Worksheet_Function_Example2()
  Dim K As String

  K = Format(8072.56489, "Currency")
  MsgBox K

End Sub
VBA Currency Format

Result:

VBA Currency Format 1

#2 – Fixed Format

Code:

Sub Worksheet_Function_Example3()
  Dim K As String

  K = Format(8072.56489, "Fixed")
  MsgBox K

End Sub
Fixed example 1

Result:

Fixed example 1-1

#3 – Percent Format

Code:

Sub Worksheet_Function_Example4()
  Dim K As String

  K = Format(8072.56489, "Percent")
  MsgBox K

End Sub
Percent Format

Result:

Percent Format 1

#4 – User-Defined Formats

Ok, now we will see some of the user-defined formats.

Code:

Sub Worksheet_Function_Example5()
  Dim K As String

  K = Format(8072.56489, "#.##")
  MsgBox K

End Sub
Example 5

Result:

Example 5-1

Code:

Sub Worksheet_Function_Example5()
  Dim K As String

  K = Format(8072.56489, "#,##.##")
  MsgBox K

End Sub
Example 5-2

Result:

Example 5-3

#5 – Date FORMAT

We have seen some of the important numbers of formatting techniques. Now 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

I have written code to show the result of the date through the variable.

Code:

Sub Worksheet_Function_Example6()
  Dim K As String

  K = 13 - 3 - 2019
  MsgBox K

End Sub

When I run this code, I would not get an accurate date. Rather result is pathetic.

Date Function 1

In order to get the accurate dates, we need to assign the date format to it. The first thing is we need to do supply the date in double-quotes and apply the date format.

Code:

Sub Worksheet_Function_Example6()
  Dim K As String

  K = Format("10 - 3 - 2019", "Long Date")
  MsgBox K

End Sub

I run this code now, and I will get a proper long date.

Date Function 1-1

“Long Date” is a built-in format. Similarly, you can use “short date” and “medium data” options.

Things to Remember

Recommended Articles

This 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 –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>