WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA ROUND Function

VBA ROUND Function

Excel VBA Round Function

Round function in VBA is a mathematical function which as the name itself suggests it rounds up or round down the given number to the specific set of decimal places specified by the user, this function uses the logic of round even which means it takes 5 as the reference and any number with last digit after the decimal is below 5 then it is round down and vice versa.

You can round a number to a two-digit decimal, three-digit decimal, or no decimal at all. For example, if you have the number 5.8697. If you round the number to a two-digit decimal, it will be 5.87. If you round to three digits, then it will round to 5.870. If you wish to round to zero, then it will 6.-

In banking numbers, all the decimal places which are less than 0.5 will be rounded down to the previous integer value, and all the decimal places which are greater than or equal to 0.5 will be rounded up to the next integer value.

I hope you have used the ROUND function in the worksheet. In VBA, too, we can use this function, but we have a difference in these functions. We will see the difference between these two functions later in this article.

VBA Round Function

Syntax

Take a look at the syntax of the Round function.

VBA Round Formula

Number: This is the number we are trying to round.

[Number of Digits after Decimal]: How many digits you need after the decimal value.

Examples

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

Assume you have the number 4.534, and you want to round to two digits.

Follow the below steps.

Step 1: Declare the variable as Variant.

Code:

Sub Round_Example1()

  Dim K As Variant

End Sub

VBA Round Example 1

Step 2: For this variable “k,” assign the value through the ROUND function.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code:

Sub Round_Example1()
  Dim K As Variant

  K = Round(

End Sub

VBA Round Example 1-1

Step 3: Number is nothing, but what is the number we are trying to round. In this case, the number is 4.534

Code:

Sub Round_Example1()
  Dim K As Variant

  K = Round(4.534,

End Sub

Example 1-2

Step 4: How many digits we need to round? In this case, we need to round to 2 digits.

Code:

Sub Round_Example1()
  Dim K As Variant

  K = Round(4.534, 2)

End Sub

Example 1-3

Step 5: Now show the variable “k” value in the message box VBA.

Code:

Sub Round_Example1()
  Dim K As Variant

  K = Round(4.534, 2)
  MsgBox K

End Sub

Example 1-4

Run this code and see what we get.

Example 1-5

We got the result as 4.53 when we rounded to 2 digits.

Now I will change the number from 4.534 to 4.535. Look what happens now.

Code:

Sub Round_Example1()
  Dim K As Variant

  K = Round(4.535, 2)
  MsgBox K

End Sub

Now run the code and see what the result is.

Example 1-6

We got the result as 4.54, one decimal higher than the previous value of 4.53. This is because, in this example, we have supplied the number like 4.535, so after the number 3 next number is 5, so it is rounded to the next number, so 3 becomes 4.

Now I will supply the number as 2.452678, and I will try to round to 3 digits.

Code:

Sub Round_Example2()
  Dim K As Variant

  K = Round(2.452678, 3)
  MsgBox K

End Sub

Run this code to see the result.

Example 2

The result is 2.453.

2.452678 Here numbers after the 2nd decimal place are 2678. After number 2, the next number is 6, which is greater than or equal to 5, so it is rounded up to the next decimal number.

Now I will use the same number to round to zero and see what happens.

Code:

Sub Round_Example3()
  Dim K As Variant

  K = Round(2.452678, 0)
  MsgBox K

End Sub

Run the code and see what we get.

Example 3

Since I have used the round to zero, we got the result as 2.

The reason why we got the result as 2 because here, the decimal first number is 4, which is less than the 0.5, so it is rounded down.

Difference Between Excel and VBA ROUND Function

There are mainly 2 differences.

#1 – Syntax of Both the Functions:

If you look at the syntax of both the function, we have a difference here.

Excel Round Syntax: Round (Number, Number of Digits After Decimal)
VBA Round Syntax: Round (Number, [Number of Digits After Decimal])

In excel, both the arguments are mandatory but in VBA second argument is optional.

In VBA, if you ignore the second argument, it takes the default argument as zero, so we will get the whole number.

#2 – Results:

The results given by these two functions are different. Below are a few examples

Results

Recommended Articles

This has been a guide to VBA Round Function. Here we learn how to use the VBA round function (up or down) along with some practical examples and a downloadable excel template. Below are some useful excel articles related to VBA:-

  • Excel VBA End Function
  • DIR VBA Function
  • Worksheet VBA Function
  • MID VBA Function
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Login

Forgot Password?

WallStreetMojo

Download VBA Round Function Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More