Md. Abdur Rahim Rasel
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
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.
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
- 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
- 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.
- 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.
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.
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
Step 2:
- While running the code, you will be able to give get the number format including the font.
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
- 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.
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
- 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.
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
- 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.
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
- 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.
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.
