EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home VBA VBA Resources VBA String and Text Functions VBA Format
 

VBA Format

Pradeep S
Article byPradeep S
Madhuri Thakur
Reviewed byMadhuri Thakur

VBA Format

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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:

Syntax of VBA Format

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).

VBA Format Code Details 1

[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).

VBA Details description

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

VBA Description

Following characters can also be used to create a format expression of strings.

VBA Symbol description

How to Use VBA Format in Excel?

Below are the different examples to use VBA Format Function in Excel

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

VBA Format – Example #1

Step 1: Go to the Developer tab and click on Visual Basic.

VBA Format Example 1-1

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.

VBA Format Module

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.

option

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

Example 2.1

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

Example 2.2

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

Example 2.3

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.

VBA Format GIF 1

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

VBA Example 2.4

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.

VBA Format GIF 2

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

Example 2.5

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

VBA GIF 2

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 –

  1. VBA Workbook
  2. VBA Get Cell Value
  3. VBA Count
  4. VBA RGB

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

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

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

EDUCBA

Download VBA Format Excel Template

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

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

Forgot Password?

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW