EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home VBA VBA Resources VBA Math & Trig Functions VBA Round
 

VBA Round

Madhuri Thakur
Article byMadhuri Thakur

Updated April 8, 2023

VBA Round Function in Excel

 

 

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

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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:

Syntax of Round Function

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.

You can download this VBA Round Excel Template here – VBA Round Excel Template

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

Module VBA Round Function

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

Code:

Sub Round_Ex1()

End Sub

Sub Round Example 1

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

Sab Round Example 1.2

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

Sub round Example 1.3

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.

Round Example 1 GIF

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

Function in excel Example 2.2

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

Function in excel Example 2.2

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

Function in excel Example 2.4

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

Function in excel Example2.5

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

Round Example 2.2

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.

image of round function

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

 Function in excel Example 3

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

Function in excel Example 3.2

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

 Function in excel Example 3.3.png

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

Function Example 3 Gif

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

Function in excel Example 4.1.png

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

 Function in excel Example 4.3

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

Function in excel Example 4.4

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

 Function in excel Example 4.5

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

Round Function in excel Example 4.6.png

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

Round Example 4 Gif

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

Round Function in excel Example 5.1.png

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

Round Function in excel Example 5.2

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

Round Function in excel Example 5.3

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

Round Function in excel Example 5.4

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

Round Example 5

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download VBA Round Excel Template

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW