VBA Round

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 –

1. VBA On Error
2. VBA Number Format
3. VBA VLOOKUP
4. VBA Function