Excel VBA: Number Format with No Decimal Places (6 Methods)

While working with large Microsoft Excel, sometimes we need to use the integer number only which means we need to use number format with no decimal places. We can easily do that by using the VBA Macros. Applying a VBA code to give a number format is an easy task. Today, in this article, we’ll learn six quick and suitable ways to use the number format with no decimal places by applying the VBA code in Excel effectively.


VBA to Get Number Format with No Decimal Places (Quick View)

Sub Number_Format()
Dim R As Long
R = InputBox("Enter a number:")
MsgBox R & (" is the number with no decimal places.")
End Sub

excel vba number format no decimal places


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


6 Suitable Ways to Apply Number Format with No Decimal Places with Excel VBA

Let’s say, we have a dataset that contains information about several Students of the Cantonment School. The identification number of the students and their securing mark in Physics is given in columns C, and D respectively. We will give a number format with no decimal places of the marks of Physics by applying a simple VBA Code. Here’s an overview of the dataset for today’s task.

excel vba number format no decimal places


1. Develop a VBA Code to Use Number Format with No Decimal Places in Excel

Now I’ll show how to give a number format with no decimal places by using a simple VBA code with the Input Box command. It’s very helpful for some particular moments. Let’s follow the instructions below to learn!

Step 1:

  • First of all, open a Module, to do that, firstly, from your Developer tab, go to,

Developer → Visual Basic

Develop a VBA Code to Use Number Format with No Decimal Places in Excel

  • After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Number Format with no Decimal Places will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,

Insert → Module

Step 2:

  • Hence, the Number Format with no Decimal Places module pops up. In the Number Format with no Decimal Places module, write down the below VBA
Sub Number_Format()
Dim R As Long
R = InputBox("Enter a number:")
MsgBox R & (" is the number with no decimal places.")
End Sub

Develop a VBA Code to Use Number Format with No Decimal Places in Excel

  • After that, run the VBA To do that, go to,

Run → Run Sub/UserForm

  • After running the code, an input box named Microsoft Excel will appear instantly in front of you. Hence, Enter any number in the Enter a number Let’s say, we enter 50.25 and then press OK.

Develop a VBA Code to Use Number Format with No Decimal Places in Excel

  • After pressing OK, you will be able to give the 50.25 number to 50 into the number format with no decimal places which is shown in the below screenshot.

Develop a VBA Code to Use Number Format with No Decimal Places in Excel

Read More: How to Use Excel VBA to Format Number in Decimal Places (6 Ways)


2. Use the Number Format Command with Font to Get Number Format with No Decimal Places with Excel VBA

We will get the number format with no decimal places by using the Number Format command including Font in a VBA code. Now, have a look at our dataset, the student’s Physics mark containing decimal places.

Use the Number Format Command with Font to Get Number Format with No Decimal Places with Excel VBA

Now, we will give those marks in the number format with no decimal places of the Cantonment School. Let’s follow the steps below to learn!

Step 1:

  • According to method 1, insert a new module and type the below VBA code to get the number format including the font. The VBA code is,
Sub Number_Format_with_Font()
Range("D5:D12").Select
Selection.NumberFormat = "0"
Selection.Font.Name = "Calibri"
End Sub

  • Hence, run the VBA To do that, go to,

Run → Run Sub/UserForm

Use the Number Format Command with Font to Get Number Format with No Decimal Places with Excel VBA

Step 2:

  • While running the code, you will be able to give get the number format including the font.

Use the Number Format Command with Font to Get Number Format with No Decimal Places with Excel VBA

Read More: Excel VBA: Format Currency to Two Decimal Places (3 Methods)


3. Use the Message Box Command in VBA Code to Give Number Format with No Decimal Places in Excel

Now, we will apply the VBA code with the Message Box command to get the number format with no decimal places in Excel. Let’s follow the steps below to learn!

Step 1:

  • According to method 1, insert a new module and type the below VBA code to get the number format with no decimal places. The VBA code is,
Sub Msg_Box()
MsgBox Format(1312036.25, "#,##0")
End Sub

Use the Message Box Command in VBA Code to Give Number Format with No Decimal Places in Excel

  • Hence, run the VBA To do that, go to,

Run → Run Sub/UserForm

Step 2:

  • After running the code, a message box named Microsoft Excel pops up showing 1,312,036 which contains no decimal places.

Use the Message Box Command in VBA Code to Give Number Format with No Decimal Places in Excel

Read More: Excel VBA: Round to 2 Decimal Places (5 Ways)


4. Give Number Format with No Decimal Places with Worksheet Name Using a VBA Code

In this method, we will get the number format by applying VBA code according to the worksheet name. Undoubtedly, this is an easy and time-saving task. Let’s follow the instructions below to learn!

Steps:

  • First of all, insert a new module according to method 1, and type the below VBA code to get the number format with no decimal places. The VBA code is,
Sub Num_Format()
Sheets("Number Format").Range("D5:D12").NumberFormat = "#,##??"
End Sub

Give Number Format with No Decimal Places with Worksheet Name Using a VBA Code

  • Further, run the VBA To do that, go to,

Run → Run Sub/UserForm

  • After completing the above process, you will be able to get the number format with no decimal places which has been given in the below screenshot.

Give Number Format with No Decimal Places with Worksheet Name Using a VBA Code


5. Apply the Conditional Formatting to Give Number Format with No Decimal Places in Excel VBA

Now we will learn how to give a number format with no decimal places including color by applying the Conditional Formatting command in a simple VBA code. This method saves your time also. Let’s follow the instructions below to get the number format with no decimal places including color by applying the Conditional Formatting command!

Step 1:

  • First, insert a new module according to method 1, and type the below VBA code to get the number format with no decimal places. The VBA code is,
Sub Conditional_Number_Formatting()
Range("D5:D12").NumberFormat = "[>=55][Green]#,##;[<55][Red]#,##"
End Sub

Apply the Conditional Formatting to Give Number Format with No Decimal Places in Excel VBA

  • After typing the VBA code in the Number Format with no Decimal Places, run the VBA To do that, go to,

Run → Run Sub/UserForm

Step 2:

  • After running the code, go back to your active worksheet, and you will be able to see a number format with no decimal places with different colors.

Apply the Conditional Formatting to Give Number Format with No Decimal Places in Excel VBA

Read More: Excel VBA: Format Percentage to 2 Decimal Places


6. VBA to Apply the Number Format with No Decimal Places in Excel

Last but not the least, we will apply the number format with no decimal places. This is the easiest method. Let’s follow the steps below to learn!

Step 1:

  • According to method 1, insert a new module and type the below VBA code to get the number format including the font. The VBA code is,
Sub Format_Number_with_MsgBox()
Dim X As String
X= FormatNumber(269.15, 0)
MsgBox X
End Sub

VBA to Apply the Number Format with No Decimal Places in Excel

  • Further, run the VBA To do that, go to,

Run → Run Sub/UserForm

Step 2:

  • After running the code, a message box named Microsoft Excel will appear in front of you showing 269 which contains no decimal places.

VBA to Apply the Number Format with No Decimal Places in Excel

Read More: Excel VBA: Format Number with Comma (2 Examples)


Things to Remember

👉 You can pop up Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously.

👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,

File → Option → Customize Ribbon


Conclusion

I hope all of the suitable methods mentioned above to use the number format with no decimal places with VBA code will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo