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

Get FREE Advanced Excel Exercises with Solutions!

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: • 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. ## 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)

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

## Related Articles 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 Advanced Excel Exercises with Solutions PDF  