VBA ROUND Function

Updated on January 2, 2024

Excel VBA Round Function

The ROUND function in VBA is a mathematical function. As the name suggests, rounds up or 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. Any number with the last digit after the decimal is below 5 then round down and vice versa.

You can round a number to a two-digit decimal, three-digit decimal, or no decimal. 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. Finally, if you wish to round to zero, it will be 6.

In banking numbers, it will round down all the decimal places less than 0.5 to the previous integer value. Likewise, it will round up all the decimal places greater than or equal to 0.5 to the next integer value.

We hope you have used the ROUND function in the worksheetROUND Function In The WorksheetROUND is a built-in Excel function that calculates the round number of a given number using the number of digits as an argument. The number to be rounded up to and the number of digits to be rounded up to are the two arguments to this formula.read more. 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

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA ROUND Function (wallstreetmojo.com)

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 do you need after the decimal value?

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

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

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

Follow the below steps.

Step 1: Declare the variable as Variant.Declare The Variable As Variant.Variant Data Type in VBA is a universal data type that can hold any type of data type, but one must use the word "Variant" when assigning the data type.read more

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.

Code:

Sub Round_Example1()
  Dim K As Variant

  K = Round(

End Sub
VBA Round Example 1-1

Step 3: The 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 do 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 VBAMessage Box VBAVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.

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, we 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 of 4.54, one decimal higher than the previous value of 4.53. In this example, we have supplied the number 4.535, so after the number 3 next number is 5, and it rounds to the next number, so 3 becomes 4.

Now, we will supply the number as 2.452678. We will try to round it 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, the numbers after the second decimal place are 2678. After number 2, the next number is 6, which is greater than or equal to 5, so it rounds up to the next decimal number.

Now, we 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 we used the round to zero, we got the result as 2.

We got the result as 2 because here, the decimal first number is 4, which is less than 0.5, so it rounds down.

Difference Between Excel and VBA ROUND Function

There are mainly 2 differences.

#1 – Syntax of Both the Functions:

If you look at both functions’ syntax, 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 arguments are mandatory. But in VBA, the 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 article has been a guide to VBA ROUND Function. Here, we learn using 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:-