Updated April 8, 2023

## Excel VBA Round Function

You must have used Excel Round function to round the floating numbers up-to a specific number of decimal places. VBA Round function behaves totally in a different manner than the one you use in Excel. VBA Round function uses “round to even” logic. If the number you are trying to round has the last digit after decimal >= 0.6, VBA Round function rounds it up (Round UP).

If the number you are trying to round have the last digit after decimal <= 0.4, it rounds it down (Round Down). Suppose the decimal part is exactly 0.5 then what does it do? In such cases, it checks with the integer part of the number. If the integer part is even then it rounds down to the even number of the decimal part. If integer part is odd, then it rounds it up to the even number. This method of rounding is also called as “Banker’s rounding”.

### Syntax of Round Function in Excel VBA

VBA Round function has the following syntax:

Where,

**Expression**– The floating number you wanted to round.**Decimal_places**– It’s an optional argument which takes an integer value which specifies the decimal places up-to which the number is supposed to be round. Should always be greater than or equals to zero. If not specified, by default zero is considered. Which means a number is rounded to an integer.

### How to Use Excel VBA Round Function?

We will learn how to use a VBA Round function with few examples in Excel.

#### Example #1 – VBA Round Function to Round a Number

Follow the below steps to use Round function in Excel VBA.

**Step 1:** Insert a new module under Visual Basic Editor (VBE).

**Step 2:** Define a new sub-procedure to store a macro in VBE.

**Code:**

Sub Round_Ex1() End Sub

**Step 3:** Use MsgBox function to be able to pop up a message like this “The Value Rounded is:”

**Code:**

Sub Round_Ex1() MsgBox "The Value Rounded is: " End Sub

**Step 4:** Now, add “& Round (10.9834, 2)” in front of MsgBox command, so that the rounded value will be shown up in the message box along.

**Code:**

Sub Round_Ex1() MsgBox "The Value Rounded is: " & Round(10.9834, 2) End Sub

**Step 5:** Hit F5 or the Run button placed at the uppermost panel to run this code. You will see an output as shown in the below.

#### Example #2 – Round a Variable Using VBA Round

**Step 1:** Define a new sub-procedure in VBE to store a macro.

**Code:**

Sub Round_Ex2() End Sub

**Step 2:** Define a variable named roundNumber as Double which can hold the value of the number to be rounded.

**Code:**

Sub Round_Ex2() Dim roundNumber As Double End Sub

**Step 3:** Assign a number value which is to be rounded to this variable using assignment operator.

**Code:**

Sub Round_Ex2() Dim roundNumber As Double roundNumber = 23.98 End Sub

**Step 4:** Use a combination of MsgBox along with Round function to round this number up-to one decimal point.

**Code:**

Sub Round_Ex2() Dim roundNumber As Double roundNumber = 23.98 MsgBox "The number rounded is: " & Round(roundNumber, 1) End Sub

**Step 5:** Let’s hit F5 or the Run button to run this code and see the output.

You can see an output as shown in the screenshot above. Please note that with the logic of “round to even”, the last decimal is rounded to 10 and the next decimal becomes 10 itself, due to which the number is rounded to the closest even part of the integer (i.e. 24).

#### Example #3 – Round Cell Value Using VBA Round

Suppose the value you wanted to round is stored in one cell of your excel worksheet.

All you want is to round this value up-to two decimal places.

**Step 1:** Define a new sub-procedure in a Module to store the macro.

**Code:**

Sub Round_Ex3() End Sub

**Step 2:** Write command as “Range (“A2”).Value =”. This command works as a location where the output will be stored.

**Code:**

Sub Round_Ex3() Range("A2").Value = End Sub

**Step 3:** Now, use a round function to round the value present in cell A1 and store the result in cell A2. Write the following piece of code: Round (Range (“A1”).Value, 2).

**Code:**

Sub Round_Ex3() Range("A2").Value = Round(Range("A1").Value, 2) End Sub

**Step 4:** Hit F5 or Run button to run this code and see the output in cell A2 of your worksheet.

#### Example #4 – Round a Number Using VBA RoundUp Function

Suppose, you want to round a number up using VBA. You can do that by using WorksheetFunction.RoundUp function.

**Step 1:** Define a new sub-procedure in Visual Basic Editor that can store your macro.

**Code: **

Sub Round_Ex4() End Sub

**Step 2:** Define two variables, one to hold the number you wanted to round up. And the other to store the roundup result.

**Code:**

Sub Round_Ex4() Dim numToRound As Double Dim numRoundUp As Double End Sub

**Step 3:** Store a value into variable numToRound which you wanted to round up.

**Code:**

Sub Round_Ex4() Dim numToRound As Double Dim numRoundUp As Double numToRound = 12.7543712 End Sub

**Step 4:** Now, use RoundUp to roundup this number and store the result in a numRoundUp variable.

**Code:**

Sub Round_Ex4() Dim numToRound As Double Dim numRoundUp As Double numToRound = 12.7543712 numRoundUp = Application.WorksheetFunction.RoundUp(numToRound, 4) End Sub

**Step 5:** Use MsgBox to show the output under the message box.

**Code:**

Sub Round_Ex4() Dim numToRound As Double Dim numRoundUp As Double numToRound = 12.7543712 numRoundUp = Application.WorksheetFunction.RoundUp(numToRound, 4) MsgBox "The number rounded up is: " & numRoundUp End Sub

**Step 6:** Hit F5 or Run button to run this code and see the output.

#### Example #5 – Round down a Number Using VBA RoundDown Function

**Step 1:** In a new sub-procedure, define two new variables with the name numToRoundDown and roundDownNum. One to hold the value of the number to be rounded down and others to store the output after the number is rounded down.

**Code:**

Sub Round_Ex5() Dim numToRoundDown As Double Dim roundDownNum As Double End Sub

**Step 2:** Assign value you want to be rounded down to the variable named “numToRoundDown”.

**Code:**

Sub Round_Ex5() Dim numToRoundDown As Double Dim roundDownNum As Double numToRoundDown = 7.075711 End Sub

**Step 3:** Now, use RoundDown to roundup this number and store the result in a roundDownNum variable.

**Code:**

Sub Round_Ex5() Dim numToRoundDown As Double Dim roundDownNum As Double numToRoundDown = 7.075711 roundDownNum = Application.WorksheetFunction.RoundDown(numToRoundDown, 4) End Sub

**Step 4:** Use MsgBox function to show up the value of a number rounded down.

**Code:**

Sub Round_Ex5() Dim numToRoundDown As Double Dim roundDownNum As Double numToRoundDown = 7.075711 roundDownNum = Application.WorksheetFunction.RoundDown(numToRoundDown, 4) MsgBox "The number rounded down is: " & roundDownNum End Sub

**Step 5:** Hit F5 or Run button to run this code and see the output as a message box.

This is it in the article. We have captured the concepts of VBA Round, Round Up and Round Down with a handful of examples.

### Things to Remember

- This function uses a Banker’s rounding method to round the numbers which are somewhat different than the actual rounding method.
- This function is used to round a number with a floating point or a number with fixed-decimals up-to specified number of places.
- It means the function will round up or down the number depending upon the number after decimal points.
- Argument decimal_places must be greater than or equal to zero.
- If decimal_places is left blank, it will be considered as zero and the number will then be rounded to the nearest integer.
- If decimal_places is set less than zero, then run time error 5 occurs. “Run-time error ‘5’: Invalid procedure call or argument”.
- It is not really predictable to what this function will round the value when the digit after the decimal is 5.
- If one or both the argument of Round function is non-numeric, then the function will return run time 13 error. “Run-time error ’13’: Type mismatch”.

### Recommended Articles

This has been a guide to VBA Round Function. Here we have discussed how to use Excel VBA Round Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –