In this article, we will illustrate the use of the VBA Round function. Rounding is basically a mathematical term. It makes a decimal value shorter and simpler but keeps the value close to the actual value. To demonstrate this VBA Round function to you we will explain it in 6 easy ways throughout this article.
Excel VBA ROUND Function Overview
- Description
The VBA Round function rounds numbers and returns to a fixed number of decimal places.
- Generic Syntax
Round(expression, [ Digits_After_Decimal])
- Argument Description
| ARGUMENT | REQUIREMENT | EXPLANATION |
|---|---|---|
| expression | Required | The numeric expression that you want to round. |
| Digits_After_Decimal | Optional | It describes how many digits you want to take after decimal including rounding.
Returns the nearest integer if the value of this argument is not provided. |
- Returns
Rounded value to a fixed number of decimal places.
- Available in
Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000.
1. Rounding a Number with the VBA ROUND Function
First and foremost, we will round a number using the VBA Round function. After rounding, we will display the result in the message box. In this example, we will round “20.8298” at two decimal places. So, to perform this action just go through the following steps:
- In the beginning, from the Developer tab, go to Visual Basic.
- Next, from the Insert tab select the option Module.
- Now, we can see a new blank VBA Module.
- After that, Insert the following code in the blank Module:
Sub Example_1()
MsgBox "The Rounded Value : " & Round(20.8298, 2)
End Sub
- Here, the value 2 of the Round argument fixes the two decimal places of rounding.
- Then, press F5 or click on Run.
- Finally, we can see the rounded value of “20.8298” in the message box. The rounded value is “20.83”.
Read More: Excel Formula to Generate Random Number (5 examples)
2. Rounding a Variable Using VBA ROUND Function
Like the previous example, we will round numeric numbers in this example also. The difference is that in this example, we will define a variable to round a numeric number just at the starting of the VBA code. Here, we will round “20.958” at one decimal place. So, let’s see the steps of doing this:
- Firstly, open the VBA Module.
- Next, Insert the following code:
Sub Example_2()
Dim NumberToRound As Double
NumberToRound = 20.958
MsgBox "The rounded value : " & Round(NumberToRound, 1)
End Sub- Then, click on Run or press F5.
- So, we can see the rounded value to one decimal in the message box.
Read More: How to Use VBA Val Function in Excel (7 Examples)
3. Applying VBA ROUND Function to Round Cell Value
Suppose, we have to round a specific cell value in our dataset. We can do this by using the VBA Round function. To illustrate this method we will use the following dataset of decimals values. We will round them up to two decimals. Just do the following steps to perform this action:
- First, open the VBA Module.
- Next insert the following code:
Sub Example_3()
Range("B6").Value = Round(Range("B6").Value, 2)
End Sub- Here, we can see the range value is B5.
- Then, click on Run.
- So, we get the rounded value of cell B5 to two decimal places.
- Now, if we want to round the value of cell B6, we will use the same code. Just, we will change the range value to B6.
- Then, click on Run.
- Here, we get the rounded value of cell B6.
- Finally, if we change the range value in the code to B7, B8, and B9 we will get the rounded values of Cells B7, B8, and B9.
Related Content: How to Use VBA Replace Function in Excel (11 Applications)
Similar Readings:
- How to Use Log Function in Excel VBA (5 Suitable Examples)
- Use VBA Randomize Function in Excel (5 Examples)
- How to Use VBA Abs Function in Excel (9 Examples)
- Use VBA LTrim Function in Excel (4 Examples)
- How to Unhide Top Rows in Excel (7 Methods)
4. Rounding a Number Up Using the RoundUp Function
In this method, we will demonstrate the use of the RoundUp function to round a decimal number to the upper rounding value. We will round the value “20.846826” to two decimals using the RoundUp function. We will follow the below steps to perform this action:
- In the beginning, open the blank code window.
- Insert the following code:
Sub Example_4()
Dim RoundNum As Double
Dim RoundUpNum As Double
RoundNum = 20.846826
RoundUpNum = Application.WorksheetFunction.RoundUp(RoundNum, 2)
MsgBox "The rounded number : " & RoundUpNum
End Sub
- Then, click on Run.
- Finally, we can see the rounded value in the message box.
Our original value was “20.846826” whereas the rounded value is “20.85”. So, we can see that the value is rounded to the upper rounding value to two decimal places.
Read More: How to Use VBA Rnd in Excel (4 Methods)
5. Using the RoundDown Function to Find the Nearest Whole Number
In the previous example, we used the RoundUp function to do upper rounding. Now, in this example, we will do lower rounding by using the RoundDown Function. We will do lower rounding for the value “12.92989”. Let’s see the steps to perform this action:
- Firstly, open the VBA code window.
- insert the following code:
Sub Example_5()
Dim RoundDownToBe As Double
Dim RoundDownNum As Double
RoundDownToBe = 12.92989
RoundDownNum = Application.WorksheetFunction.RoundDown(RoundDownToBe, 2)
MsgBox "The rounded down value : " & RoundDownNum
End Sub- Now, click on Run.
- So, we get the rounded value of “12.92989” in the form of lower rounding to two decimals.
Read More: How to Use VBA Int Function in Excel ( 3 Examples)
6. Utilizing the VBA ROUND Function to Round the Nearest 100
In this method, we will use the VBA Round function to round a number to its nearest hundred. In order to do this kind of rounding, we do not need a decimal number. To demonstrate this method properly, we have created a dataset of some values. We will round them to their nearest hundred. To do this follow the below steps:
- First, open the VBA Module.
- Next, Insert the following code:
Sub Example_6()
Worksheets("Nearest_100").Cells(6, 3).Value = Application.WorksheetFunction.MRound(Cells(6, 2).Value, 100)
End Sub- Here, we are rounding the cell value of B5 by taking the reference Cells(5,2).
- Also, we are inputting the rounded value in cell C5 by using the argument Cells(5,2) in the MRound Function.
- Then, click on the Run.
- So, we can see the rounded value of cell B5 in cell C5. The value is 100.
- Now, assume we want to round the value of cell B6. So, we just need to change the cell reference from Cells(5,2) to Cells(6,2) in order to take the value.
- Then, to paste the rounded value in cell C6 we need to change the argument value of the MRound function to Cells(6,3) in the previous code.
- The final output is shown below.
- Finally, to round the rest of the two values just change the cell references from the above code. The dataset will look like this in the end.
Read More: How to Use VBA IsNumeric Function (9 Examples)
Difference Between Excel and VBA ROUND Function
We get the applications of the Round function in both Excel and VBA. The main functioning of these two functions is basically the same. But we can highlight three differences between these two. The differences are-
| Topic | Excel ROUND Function | VBA Round Function |
|---|---|---|
| Syntax | Round (number, num_digits) | Round(expression, [ NumberOfDigitsAfterDecimal]) |
| Argument | Necessary | Optional |
| Result | Value of ROUND(6.5,0) = 7 | Input Round(6.5,0) = 6 |
| ROUND(4.25,1) = 4.3 | Round(4.25,1) = 4.2 |
Things to Remember
1. VBA Round function rounds numbers by using Banker’s rounding method. It is different than conventional rounding.
2. This function rounds numbers by considering the numbers after decimal points.
3. The second argument “Digits_After_Decimal” argument has to be greater than or equal to zero.
4. If “Digits_After_Decimal” is zero, the function rounds to the nearest integer.
5. We will face “Run-time error ‘5’: Invalid procedure call or argument” if we input the negative value of “Digits_After_Decimal”.
6. If we input any non-numeric value in any of the arguments we will face a “Run-time error ’13’: Type mismatch” error.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
In conclusion, we have touched on almost all the functions of the VBA Round function in this article. So, to practice yourself download the practice workbook added with this article. If you feel any confusion just leave a comment in the below box. We will try to answer as soon as possible. Stay tuned with us for more interesting solutions to Microsoft Excel problems.
