WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Format

VBA Format

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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 formatting 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

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 macro 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 box.

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.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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.

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

  • The value returned by the FORMAT function is the string.
  • We can also use our own date, time, and number formatting codes, like how we use in worksheet formatting.
  • FORMAT is a VBA function and available only in VBA, not in the worksheet.

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 –

  • VBA Data Type
  • VBA DIR Function
  • VBA MOD
  • Create VBA InputBox
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Format Template

Special Offer - VBA Training Course (6 courses, 35+ hours video) View More