Akib Bin Rashid
Excel is the most widely used tool when it comes to dealing with huge datasets. We can perform myriads of tasks of multiple dimensions in Excel. Sometimes, we need to round the numbers that we have in our dataset. In this article, I am going to show how to round to 2 decimal places with Excel VBA. I am going to describe 5 methods using Excel VBA to round to 2 decimal places.
Download Workbook
This is the dataset that I am going to use. Here we have a number, which I will round to 2 decimal places.
5 Easy Methods to Use Excel VBA to Round a Number to 2 Decimal Places
1. Using VBA to Round a Number to 2 Decimal Places for a Specific Sheet
In the first method, I will do the simplest task. I will round a number to 2 decimal places using VBA macros.
- VBA window will appear. Then go to Insert >> select Module.
- In the module, write down the following code.
Sub Round_Numbers()
Dim ws As Worksheet
Set ws = Worksheets("Round A Number")
ws.Range("C4") = Application.WorksheetFunction.Round(ws.Range("B4"), 2)
End Sub
Code Breakdown
- I have created a Sub Procedure Round_Numbers. Then I used the dim statement to define a variable ws as Worksheet.
- Next, I used the Set statement to set the worksheet named “Round A Number” using the Worksheets object in ws
- Then I used Round to get the number in B4 rounded. The result will be in C4 and 2 defines that Excel will round the number to 2 decimal places.
- Then press F5 to run the code. Excel will return the output in C4.
Read More: How to Use Excel VBA to Format Number in Decimal Places (6 Ways)
2. Applying VBA Macros to Round a Cell Value
Now I will apply VBA macros to round a cell value. This time I will use row and column index to define a cell.
STEPS:
- In the module, write down the following code.
Sub Round_Cell_Value()
Worksheets("Round A Cell").Cells(4, 3).Value = _
Application.WorksheetFunction.Round(Cells(4, 2).Value, 2)
End Sub
Code Breakdown
- I have created a Sub Procedure Round_Cell_Value.
- Then I used the Worksheet object to mention the Sheet name Cell Value where the Sheet name is “Round A Cell” and the Cell Value (4,3) is C4 to keep my output.
- Then I used Round to get the number in B4 rounded. The result will be in C4 and 2 defines that Excel will round the number to 2 decimal places.
- Then run the code by selecting the icon (see image).
Excel will return the output.
Read More: Excel VBA: Format Percentage to 2 Decimal Places
3. Using VBA to Round a Range of Numbers to 2 Decimal Places
Now, I will go for something a bit more complex as well as useful. This time I will use VBA macros to round a range of numbers.
I have the Amount of Tax for some people (in C4:C9) and I want the numbers to be rounded.
STEPS:
- Open a module in the VBA macros window following method-1 or method-2.
- In the module, write down the following code
Sub Round_a_Range()
Dim n As Integer
Dim ws As Worksheet
Set ws = Worksheets("Round A Range")
For n = 4 To 9
ws.Cells(n, 4).Value = Application.WorksheetFunction.Round(Cells(n, 3).Value, 2)
Next n
End Sub
Code Breakdown
- I have created a Sub Procedure Round_a_range. I used the dim statement to define a variable ws as Worksheet.
- Next, I used the Set statement to set the worksheet named “Round A Range” using the Worksheets object in ws
- I also took a variable n as an integer and applied a For loop for n which will go from the 4th to 9th row.
- Then I used Round to get the numbers rounded.
- Press F5 to run the code. Excel will return the output.
- Format as you wish.
Read More: Excel VBA: Format Currency to Two Decimal Places (3 Methods)
4. Inserting MsgBox to Show Rounded Number
In Excel, you can also use a message box to let Excel show you something. In this method, I will insert a message box to show rounded numbers.
STEPS:
- Open a module in the VBA macros window following method-1 or method-2.
- In the module, write down the following code
Sub Using_MsgBox()
Dim Number As Double
Number = 257.865
MsgBox "The value is " & Round(Number, 2)
End Sub
Code Breakdown
- I have created a Sub Procedure Using_Msgbox. Then I used the dim statement to define a variable Number as Double and placed 865 in the Number variable.
- Then I inserted a MsgBox which will show me the rounded number.
- Press F5 to run the code. Excel will return the output.
Read More: Excel VBA: Number Format with No Decimal Places (6 Methods)
5. Use of Cell Reference to Round a Number in Excel
Now I show you another method that will show how to insert a message box while using cell reference.
STEPS:
- Open a module in the VBA macros window following method-1 or method-2.
- In the module, write down the following code
Sub Using_Cell_Reference()
Dim Round_2_Decimal As Double
Round_2_Decimal = Round(Range("B4").Value, 2)
MsgBox "The value is " & Round_2_Decimal
End Sub
Code Breakdown
- I have created a Sub Procedure Using_Cell_Reference. Then I used the dim statement to define a variable Round_2_Decimal as Double.
- Round_2_Decimal will hold the rounded value of the number in the B4 cell upto 2 decimal places.
- Then I inserted a MsgBox which will show me the rounded number.
- Press F5 to run the code. Excel will return the output.
Practice Workbook
Practice makes a man perfect. It is important to practice to internalize any method. That’s why I have attached a practice sheet for you.
Conclusion
In this article, I have demonstrated 5 effective methods in Excel VBA to round to 2 decimal places. I hope it helps everyone. And lastly, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.
