How to Use VBA Round Function in Excel (6 Quick Uses)

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.


Download the Practice Workbook

You can download the practice workbook from here.


Overview of Excel VBA Round Function

  • 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.


6 Uses of VBA Round Function in Excel

1. Round a Number with 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:

Round a Number with VBA Round function

  • Next, from the Insert tab select the option Module.

Round a Number with VBA Round function

  • Now, we can see a new blank VBA Module.

Round a Number with VBA Round function

  • 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. VBA Round function to Round a Variable

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.

VBA Round function to Round a Variable

Read More: How to Use VBA Val Function in Excel (7 Examples)


3. Use 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:

Use VBA Round Function to Round Cell Value

  • 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.

Use VBA Round Function to Round Cell Value

  • So, we get the rounded value of cell B5 to two decimal places.

Use VBA Round Function to Round Cell Value

  • 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.

Use VBA Round Function to Round Cell Value

  • 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:


4. Rounding a Number Up Using 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.

Rounding a Number Up Using RoundUp Function

  • 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 upper rounding value to two decimal places.

Read More: How to Use VBA Rnd in Excel (4 Methods)


5. Use RoundDown Function to Find Nearest Whole Number

In the previous example, we use 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.

Use RoundDown Function to Find Nearest Whole Number

Read More: How to Use VBA Int Function in Excel ( 3 Examples)


6. VBA Round Function to Round 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:

VBA Round Function to Round Nearest 100

  • 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 see the rounded value of cell B5 in cell C5. The value is 100.

VBA Round Function to Round Nearest 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.

VBA Round Function to Round Nearest 100

  • 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.

VBA Round Function to Round Nearest 100

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 round 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.


Conclusion

In conclusion, we have touched on almost all the functioning 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.


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo