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 Date & Time Functions VBA Format Number
 

VBA Format Number

VBA Format Number

Excel VBA Format Number

VBA comes with an inbuilt function for Formatting numbers. The name of that inbuilt Function is “Format Number”. This function formats the specific number in a format which is required by the user.

 

 

Before we go ahead, let us first understand what is number formatting. Number Formatting basically means the specific number should be shown in a particular way. For example, if you want to see a specific number in terms of two decimal point (150.55) or you want to see the same number in terms of no decimal point (150). These are just examples.

Watch our Demo Courses and Videos

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

Categories of Excel Format Number

Basically, we can categorize these number formatting in four categories.

  • Decimal points
  • Negative numbers in brackets
  • Leading digits
  • Group digits

We are going to learn all the formattings in VBA with the help of examples. But first, let us take a look at the syntax of VBA function for Format number.

VBA Format Number Syntax

So as you can see the syntax of VBA function for format number in the above screenshot, let us break that function and understand every part of the function.

  1. Expression: As you can see the first part of the function is an expression which is nothing but the number we want to Format.
  2. Num digit after the decimal: The second part of the function asks for the number of digits of the number you want to see after the decimal point.
  3. Leading Digit: This is useful when the number is between -1 and 1 which means when the number is more than -1 and less than 1. For example 0.25.

So if you want to see the value zero before the decimal point, you need to insert argument in the function as TRUE or -1. The result will be 0.55.

But if you insert the value in the argument as FALSE or 0, the result will .55.

  1. Use parent for Negative Numbers: This part is used for Negative numbers. If you want to see the negative numbers in a bracket then you should give an argument as TRUE or -1 and the result will be (156). But when you provide the argument as FALSE or 0, the result will be -156.
  2. Group Digits: This is the last part of the function which is used for the thousand separators. So if you want to see the numbers with a thousand separator you should provide the argument as TRUE or -1 and the result will be 52,000. But if you provide the same argument as FALSE or 0, the result will be like 52000.

How to Format Number Using VBA Code?

Now as you understand the different forms of Number Formatting and the VBA inbuilt function of Number format, we can go ahead and see how we can use this with the help of examples.

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

Before going to the examples, follow the below basic steps if you are new to VBA macro. You can insert the below codes in the VBA module.

Step 1: Create a Macro name which is the first line in the code as you can see in the screenshot.

Step 2: Declare one of the variables as String because the result given by the VBA Format number function is String.

Code:

Sub Format_Number_Example1()

Dim MyNum As String

End Sub

VBA Format Number Example 1-1

Now we can go ahead with our examples.

Decimal Points – Example #1

Suppose we are working with the number 56456 and you want to see two numbers after the decimal point.

Step 1: In the code, you can add the VBA inbuilt function as below.

Mynum = Formatnumber(

Step 2: As you know the first step of the function is the expression which is nothing but the number you want to format. 56456 in our case.

Mynum = Formatnumber(56456,

Step 3: The next step is Numdigitafterdecimal which is nothing but the number of digits you want to see after the decimal point which is 2 in our case.

Code:

Sub Format_Number_Example1()

Dim MyNum As String

MyNum = FormatNumber(56456, 2)

MsgBox MyNum

End Sub

VBA Format Number Example 1-2

Step 4: The next step is to show the number in a message box. VBA has an inbuilt function to show the value in a message box which is MSGBOX.

So you just need to insert the below code after inserting the function of Format number for MyNum.

Code:

Sub Format_Number_Example1()

Dim MyNum As String

MyNum = FormatNumber(56456, 2)

MsgBox MyNum

End Sub

VBA Format Number Example 1-3

As you can see the last line in the code is End Sub which means VBA code for the macro ends after that.

Step 6: As the coding for the format number ends, you can run the macro and you can see the below result after running the macro.

VBA Format Number Example 1-4 

Thousand Separator – Example #2

Now suppose with the same example, we need to show the same number 56456 with thousand separators. In that case, we need to give the last argument as “vbTrue” as shown in the below screenshot.

Code:

Sub Format_Number_Example2()

Dim MyNum As String

MyNum = FormatNumber(56456, 2, , , vbTrue)

MsgBox MyNum

End Sub

VBA Format Number Example 2-1

After running the macro, you will be able to see the below result.

VBA Format Number Example 2-3

If you select “vbFalse” as the last argument, the number will not be shown as thousand separator.

Show Negative number in Bracket – Example #3

Suppose the same number 56456 in the above example is negative and you need to show the negative numbers in a bracket then the fourth argument in the function should be “vbTrue” as shown in the below screenshot.

Code:

Sub Format_Number_Example3()

Dim MyNum As String

MyNum = FormatNumber(-56456, 2, , vbTrue, vbTrue)

MsgBox MyNum

End Sub

VBA Format Number Example 3-1

As you can see the Fourth & Fifth Argument in the code are “vbTrue” which means the formatting for Negative number (fourth argument) & formatting for thousand separators (Fifth argument) both are True. So you will see the negative number within a bracket and with Thousand Separators as shown in the below screenshot.

Example 3-2

Formatting for Number between -1 and 1 – Example #4

Suppose the number is 0.567 which is between -1 & 1 then you have two options for formatting the number. First option is to show Zero before the decimal point or not to show zero before the decimal point.

If you choose the option to show zero before the decimal point then you should provide the third argument in the function as “vbTrue” as shown in the below screenshot.

Code:

Sub Format_Number_Example4()

Dim MyNum As String

MyNum = FormatNumber(0.567, 2, vbTrue, vbTrue, vbTrue)

MsgBox MyNum

End Sub

Example 4-1

You will be able to see the below result after running the macro code.

Example 4-2

As you can see the result is showing only two digits after the decimal point because we have provided only 2 as the second argument in the function.

If you select the third argument as “vbFalse” as shown in the below screenshot.

Code:

Sub Format_Number_Example5()

Dim MyNum As String

MyNum = FormatNumber(0.567, 2, vbFalse, vbTrue, vbTrue)

MsgBox MyNum

End Sub

Example 4-3

Then you will not see the zero before the decimal point as shown in the below screenshot.

Example 4-4

Things to Remember

  • VBA FormatNumber Function has 5 arguments so make sure you provide the right arguments in the order of the function. We have to follow the order of the function, there is no other way out.
  • If you keep any one of the argument as Blank then it will automatically consider the VBA default option.
  • Be careful with vbTrue and vbFalse argument for last three arguments for number formatting.
  • For the first argument which is the expression, you can also provide the cell reference in the excel sheet instead of any number.

Recommended Articles

This is a guide to VBA Format Number. Here we discuss how to Format Numbers in Excel Using VBA Code along with practical examples and downloadable excel template. You can also go through our other suggested articles-

  1. VBA TRIM
  2. VBA Arrays
  3. VBA Select Case
  4. VBA Find

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
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
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

Download VBA Format Number Excel Template

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - ENROLL NOW