VBA RoundUp

VBA RoundUp Function

In Excel, we have round function which is used to round the numbers up to the decimals we choose. Suppose there is a number which is somewhat like this 8.5036 and we want to roundup it to two numbers. There are actually two methods to do in excel, one is the round button we have in excel as shown in the screenshot below and another is the round function. The screenshot for the round button is as follows.

As explained above, we can round numbers in excel with the option of increase and decrease decimal or round function. Similarly, in VBA we also have round the function which is used to round the numbers to specific decimals. Earlier the round function in excel was using the following arguments.

In Excel: Round (Number, Number of digits after decimal)

The number is the number we want to roundup while the number of digits is the amount of digits we want to round. Similarly, the excel VBA roundup is the same as the excel round function. Have a look at it below.

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

The above is the round function in VBA but the roundup function is somewhat different. However, the syntax for round function in VBA and roundup function is similar. It is as follows,

In VBA Roundup is:

Roundup( Number, {Number of digits After Decimal})

RoundUp function is similar to a round function in VBA excel but the difference is that it always roundup the number for the given arguments. Now let us understand the arguments we provide to round function.

We know that number is the number we provide as input which will be round off. The number of digits can be in three scenarios as follows:

• If the number of digits provided is greater than zero then the number is rounded up to the specified decimal place.
• If the number of digits is provided as input is equal to zero then the number is rounded up to its nearest integer.
• Now if the number of digits which is provided as input is less than zero then the number is rounded up to the left of its decimal point.

RoundUp function works very similar to the worksheet function of excel. Now keep in mind as it has decimal values to the output it returns in double. It will be clear with a few examples. We will learn all three aspects of roundup function i.e. less than 0, equal to 0 and greater than 0 in examples. For our examples, we will take input from a user and roundup and display it.

How to RoundUp Using VBA?

Let’s see the examples of RoundUp in Excel VBA.

Example #1 – VBA Roundup

Let us begin with the number of digits provided is less than 0 and see how the results pop-up.

Step 1: To begin with VBA we need to be in VB Editor, we can do that by clicking on the developer’s tab and then on Visual basic as shown in the screenshot below,

Step 2: Now insert a module in the VB Editor where we will begin with VBA Macros, Double Click on the module which will open another window for us where we will write our code,

Step 3: Start by a sub-function as follows.

Code:

Sub Sample()

End Sub

Step 4: Declare two variables as double and one as an integer, one which will store the value for input while one will store the value for output and one will store the value for the number of digits we want to roundup,

Code:

Sub Sample()

Dim A, B As Double
Dim C As Integer

End Sub

Step 5: In variable A store the value of the number which will be rounded up and in C store the value for the number of digits which will be rounded up. Both values we will take as input from a user as follows,

Code:

Sub Sample()

 Dim C As Integer
A = InputBox("Enter a Value", "Value in Decimals")
C = InputBox("Enter number of digits to be rounded up", "Enter less than zero")

End Sub

Step 6: In variable B store the rounded up value of A as follows,

Code:

Sub Sample()

Dim A, B As Double
Dim C As Integer
A = InputBox("Enter a Value", "Value in Decimals")
C = InputBox("Enter number of digits to be rounded up", "Enter less than zero")
B = Application.WorksheetFunction.RoundUp(A, C)

End Sub

Step 7: Now use msgbox function to display the rounded up value of A as follows,

Code:

Sub Sample()

Dim A, B As Double
Dim C As Integer
A = InputBox("Enter a Value", "Value in Decimals")
C = InputBox("Enter number of digits to be rounded up", "Enter less than zero")
B = Application.WorksheetFunction.RoundUp(A, C)
MsgBox B

End Sub

Step 8: Run the above code and give input of A as follows,

Step 9: Click on Ok which will give another input box to give the number of digits to be rounded up, give -1 as input,

Step 10: When we press ok we get the final rounded up result,

Example #2 – VBA Roundup

In this example, we will take input from the user for a number of digits to be rounded up as 0.

Step 1: Add a new module in the VBA, in the module write another sub-function for another macro as follows,

Code:

Sub Sample1()

End Sub

Step 2: Again declare three variables, two of them as double while one as an integer as follows,

Code:

Sub Sample1()

Dim A, B As Double
Dim C As Integer

End Sub

Step 3: We know that A will store the input for the number to be rounded up and B will store the number of digits to be rounded as follows,

Code:

Sub Sample1()

Dim A, B As Double
Dim C As Integer
A = InputBox("Enter a Value", "Value in Decimals")
C = InputBox("Enter number of digits to be rounded up", "Enter equal to zero")

End Sub

Step 4: In variable C we will roundup the input value we took from the user and display it using the msgbox function as follows,

Code:

Sub Sample1()

Dim A, B As Double
Dim C As Integer
A = InputBox("Enter a Value", "Value in Decimals")
C = InputBox("Enter number of digits to be rounded up", "Enter equal to zero")
B = Application.WorksheetFunction.RoundUp(A, C)
MsgBox B

End Sub

Step 5: Now run the above code by hitting F5 and give the input for a number to be rounded up as follows,

Step 6: Click on Ok and provide the number of digits to be rounded up as 0 as follows,

Step 7: When we click on Ok we see the following result,

Example #3 – VBA Roundup

Now we will use the final and last option to provide the number of digits to be greater than zero and see the result.

Step 1: Add a new module and in that add a new sub-function as follows,

Code:

Sub Sample2()

End Sub

Step 2: Now we will again declare three variables, two of them as double and one as an integer. One will store the value for input if the number to be rounded up while another will store the input of a number of digits,

Code:

Sub Sample2()

Dim A, B As Double
Dim C As Integer
A = InputBox("Enter a Value", "Value in Decimals")
C = InputBox("Enter number of digits to be rounded up", "Enter greater than zero")

End Sub

Step 3: In the variable B store the rounded up value of A with roundup function is as follows and use msgbox function to display the result,

Code:

Sub Sample2()

Dim A, B As Double
Dim C As Integer
A = InputBox("Enter a Value", "Value in Decimals")
C = InputBox("Enter number of digits to be rounded up", "Enter greater than zero")
B = Application.WorksheetFunction.RoundUp(A, C)
MsgBox B

End Sub

Step 4: Now we run the code and provide the input for the number to be rounded up,

Step 5: Click on Ok and give the number of digits rounded up as 2,

Step 6: Click Ok to see the final result,

Things to Remember

There are few things which we need to remember about VBA RoundUp.

• It is similar to the roundup in VBA and excel.
• It takes a number of digits as input as less than 0 or greater than 0 or equal to 0.
• It is actually a worksheet function we use it in VBA.
• The output returned by this function is double.

Recommended Articles

This is a guide to VBA Roundup. Here we discuss how to use Excel VBA Roundup along with few practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA While Loop
2. VBA Remove Duplicates
3. VBA Data Types
4. VBA Sleep