EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Math & Trig Functions VBA Number Format
Secondary Sidebar
VBA Math & Trig Functions
  • VBA Math & Trig
    • VBA Random Number
    • VBA Number Format
    • VBA Integer
    • VBA MOD
    • VBA IsNumeric
    • VBA Round
    • VBA INT
    • VBA RoundUp
    • VBA Intersect
    • VBA Randomize
    • VBA Square Root

VBA Number Format

By Madhuri ThakurMadhuri Thakur

VBA Number Format in Excel

Excel VBA Number Format

VBA Number Format though looks simple but it is very important to master them. In VBA, we have several ways to format numbers, we have the Number Format Function.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,088 ratings)

When it comes to range object, we use the property Range.NumberFormat to format numbers in the range. In today’s article, we will see how to use number format in range object to apply formatting style to our numbers.

Watch our Demo Courses and Videos

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

What does Number Format Function do in VBA?

Just to remind you, excel stores all numerical values as serial numbers, be it date or time this will also be stored as serial numbers. According to the format given by the user to the serial number, it will display the result.

For example assume you have the number 43542 in cell A2.

VBA Number Format Demo 1

Now I will apply the date format of “dd-mmm-yyyy”.

VBA Number Format Demo 1-2

And it will display the result as 18-Mar-2019.

VBA Number Format Demo 1-3

Similarly in VBA as well we will perform the same job by using number format property.

How to Use Number Format Function in VBA?

Let’s understand how to use Number Format Function in VBA with some examples.

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

Example #1

Ok, we will see how to format the same cell value using VBA code. I will remove the date format I have applied to the serial number 43542 in cell A2.

VBA Number Format Example 1

Now go to VBA editor and create a macro name.

Code:

Sub NumberFormat_Example1()

End Sub

VBA Number Format Example 2-2

Now we need to tell which cell actually we want to format, in this case, we need to format the cell A2. So write the code as “Range (“A2”)”

Code:

Sub NumberFormat_Example1()

    Range ("A2")

End Sub

VBA Number Format Example 1-3

After selecting the cell to select the property called “NumberFormat” by putting dot (.)

VBA Number Format Example 1-4

After selecting the property put an equal sign.

VBA Number Format Example 1-5

Now apply the format we wish to apply in this case, format is date format i.e. “dd-mmm-yyyy” format.

Code:

Sub NumberFormat_Example1()

    Range("A2").NumberFormat = "dd-mmm-yyyy"

End Sub

VBA Number Format Example 1-6

Now run this code, it will display the result exactly the same as the worksheet number formatting.

VBA Number Format Example 1-7

Example #2

Format Numbers using Built-in Formats 

Assume you have few numbers from cell A1 to A5.

Example 2-1

We will try out different built-in number formats. Some of the number formats are “Standard”, General”, “Currency”, “Accounting”.

To apply the formatting we need to select the range of cells first, here the range of cells is from A1 to A5 and then select the number format property.

Example 2-2

Apply the number format as “General”.

Code:

Sub NumberFormat_Example2()

    Range("A1:A5").NumberFormat = "General"

End Sub

Example 2-3

Example #3

As “General” doesn’t have any impact on default numbers we don’t see changes. So apply the currency format and code for currency format is “#,##0.00”.

Code:

Sub NumberFormat_Example3()

    Range("A1:A5").NumberFormat = "#,##0.0"

End Sub

Example 3-1

This will apply the currency format like the below.

Example 3-2

Example #4

If you wish to have currency symbol you can provide the currency symbol just before the code.

Code:

Sub NumberFormat_Example4()

    Range("A1:A5").NumberFormat = "$#,##0.0"

End Sub

Example 4-1

This code will add a currency symbol to the numbers as part of the formatting.

Example 4-2

Example #5

Format Percentage Values

Now we will see how to format percentage values. For this example, I have created some of the percentage values from cell A1 to A5.

Example 5-1

Now select the range and select Number Format property.

Example 5-2

Apply the formatting code as “0.00%”.

Code:

Sub NumberFormat_Example5()

    Range("A1:A5").NumberFormat = "0.00%"

End Sub

Example 5-3

Run this code using F5 key or manually then it will convert all the values to the percentage.

Example 5-4

Now, look at the cells a2 & a5 we have negative values. As part of the formatting, we can show the negative values in red color as well. To show all the negative values formatting code is “0.00%;[Red]-0.00%”

Code:

Sub NumberFormat_Example5()

    Range("A1:A5").NumberFormat = "0.00%;[red]-0.00%"

End Sub

Example 5-5

Run this code using F5 key or manually and we will have all the negative percentage values in red color.

Example 5-6

Example #6

Format Positive Numbers and Negative Numbers

As we can format numbers we can play around with them as well. Assume few numbers from range A1 to A5 which includes negative numbers as well.

Example 6-1

As we have shown in the percentage here also we can show all the negative numbers in red color. To show negative numbers in red color code is “#,##.00;[red]-#,##.00”

Code:

Sub NumberFormat_Example6()

    Range("A1:A5").NumberFormat = "#,##.00;[red]-#,##.00"

End Sub

Example 6-2

This will format the numbers like this.

Example 6-3

We can also show negative numbers in red as well as in brackets. To do this below is the formatting code.

Code:

Sub NumberFormat_Example6()

    Range("A1:A5").NumberFormat = "#,##.00;[red](-#,##.00)"

End Sub

Example 6-4

Run this code using F5 keys or manually and this will format the numbers like this.

Example 6-5

Example #7

Text with Number Formatting

The beauty of number formatting is we can also add text values to it. I have a list of items that measure their weight in “Kg’s”.

Example 7-1

The problem here is Carrot’s weight says 30, by looking at this weight reader cannot understand whether it is 30 grams or 30 kgs. So we will format this by using VBA code.

Code:

Sub NumberFormat_Example7()

    Range("B2:B6").NumberFormat = "0#"" Kg"""

End Sub

Example 7-2

Run this code using F5 key or manually and this will add the word “Kg” in front of all the number from B2 to B6.

Example 7-3

Recommended Articles

This has been a guide to VBA Number Format Function. Here we discussed how to use Excel VBA Number Format Function along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. FIND Function in Excel with Examples
  2. What is VBA Function in Excel?
  3. Guide to VBA Range Object
  4. How to use VBA VLOOKUP Function?
  5. How to Use Negative Numbers in Excel?
7 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Watch our Demo Courses and Videos

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more