VBA RoundUp

What is VBA RoundUp Function?

The VBA RoundUp function is used to round a number up to a specified number of decimal places. This function is beneficial when you need to ensure that a number is rounded up, regardless of the fractional part. Unlike the standard “Round” function, which rounds to the nearest integer, RoundUp always rounds up.

Let us look at an example where we have the number 9.25. When you execute this code, it initializes two variables, “originalNumber” and “roundedNumber,” where “originalNumber” is set to the value 9.25, representing the number we want to round up. The VBA RoundUp function is then applied with a second argument of 1, indicating that we want to round up to one decimal place.

As a result, the original number 9.25 is rounded up to 9.3. A message box is displayed, presenting the original number and the rounded number in the following format: “Original Number: 9.25” and “Rounded Number: 9.3.”

Note: The error message “VBA RoundUp function not defined” typically occurs when you attempt to use a function that VBA does not recognize.

In VBA, there is no built-in VBA RoundUp function, and you should use “Application.WorksheetFunction.RoundUp” to perform rounding up.

How To Use VBA RoundUp Function?

Step 1: In the Excel workbook, press ALT + F11 to open the Visual Basic for Applications (VBA) editor.

Step 2: Inside the VBA editor, go to Insert > Module to insert a new module where you can write your VBA code.

Step 3: In the newly created module, you can start writing your VBA code, including RoundUp. The basic syntax of the VBA RoundUp function is as follows:

RoundUp(number, decimal_places)

• number: The number you want to round up.
• decimal_places: The number of decimal places to which you want to round up.

Step 4: Save the module, exit the VBA editor, and return to your Excel workbook or the VBA-enabled application where you want to use this code.

Step 5: To run your code: Press ALT + F8 to open the “Macro” dialog. Select the macro you want to run and click “Run.”

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

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

Example #1 – When the Second Argument is Zero

In this example, the VBA RoundUp function rounds the number 12.345 up to the nearest integer, resulting in 13.0.

Step 1: In the new module, we begin by declaring two variables, originalNumber and roundedNumber of the Double VBA data type.

These variables will store the original number and the result of the rounding, respectively.

Step 2: We assign the value 12.345 to the originalNumber variable.

Step 3: Here, we use the RoundUp function with the arguments originalNumber and 0 as the number of decimal places to round to. The result is stored in the roundedNumber variable.

Step 4: Finally, we use a MsgBox VBA function to display the original number and the rounded number.

Step 5: Now save the macro. Upon executing the code, it rounds the original number 12.345 up to the nearest whole number and presents the result as “Original Number: 12.345” and “Rounded Number: 13” in a message box.

Here is the full code:

Sub RoundUpExample1()

    Dim originalNumber As Double

    Dim roundedNumber As Double

      originalNumber = 12.345

    roundedNumber = Application.WorksheetFunction.RoundUp(originalNumber, 0)

      MsgBox “Original Number: ” & originalNumber & vbCrLf & “Rounded Number: ” & roundedNumber

End Sub

Example #2 – When the Second Argument is 1

With a second argument of 1, the VBA RoundUp function rounds the number 12.345 up to one decimal place, yielding 12.4.

Step 1: Like the first example, in each of the following examples, we start by declaring two variables, originalNumber and roundedNumber, both of which are of the Double data type.

These variables serve to store the original number that requires rounding and the result of the rounding operation.

Step 2: This time, we use RoundUp with one as the number of decimal places to round to.

Step 3: Now save the macro. When you run the code, it rounds the original number 12.345 up to one decimal place and displays the result as “Original Number: 12.345” and “Rounded Number: 12.4” in a message box.

Here is the full code:

Sub RoundUpExample2()

    Dim originalNumber As Double

    Dim roundedNumber As Double

        originalNumber = 12.345

    roundedNumber = RoundUp(originalNumber, 1)

        MsgBox “Original Number: ” & originalNumber & vbCrLf & “Rounded Number: ” & roundedNumber

End Sub

Example #3 – When the Second Argument is 2

When 2 is specified as the second argument, the VBA RoundUp function rounds 12.345 up to two decimal places, resulting in 12.35.

Step 1: In this instance, we employ the VBA RoundUp function, specifying a rounding precision of 2 decimal places.

Step 2: Upon executing the code, it performs rounding on the initial number, 12.345, upward to two decimal place and presents the outcome in a message box as “Original Number: 12.345” and “Rounded Number: 12.35.”

Here is the full code:

Sub RoundUpExample3()

    Dim originalNumber As Double

    Dim roundedNumber As Double

        originalNumber = 12.345

    roundedNumber = RoundUp(originalNumber, 2)

       MsgBox “Original Number: ” & originalNumber & vbCrLf & “Rounded Number: ” & roundedNumber

End Sub

Example #4 – When the Second Argument is 3

With a second argument of 3, the VBA RoundUp function rounds 12.345 up to three decimal places, giving 12.345.

Step 1: In this case, we employ the VBA RoundUp function with a precision of 3 decimal places for rounding.

Step 2: When the code is executed, it conducts rounding on the starting value, 12.345, in an upward direction, and the result is displayed within a message box, showing “Original Number: 12.345” and “Rounded Number: 12.345,” rounded to three decimal places.

Here is the full code:

Sub RoundUpExample4()

    Dim originalNumber As Double

    Dim roundedNumber As Double

       originalNumber = 12.345

    roundedNumber = RoundUp(originalNumber, 3)

        MsgBox “Original Number: ” & originalNumber & vbCrLf & “Rounded Number: ” & roundedNumber

End Sub

Example #5 – When the Second Argument is -1

When -1 is specified as the second argument, the VBA RoundUp function rounds 12.345 up to the nearest ten, resulting in 20.0.

Step 1: In this scenario, we utilize the VBA RoundUp function while setting the number of decimal places for rounding to -1.

When the second argument in the VBA RoundUp function is set to a negative value, such as -1, it rounds the number up to the nearest multiple of 10 raised to the absolute value of that argument.

In this case, with -1 as the argument, it rounds to the nearest multiple of 10^1, which is 10. So, any number less than 10 would round up to 10.

Step 2: When you execute this code, it rounds the original number 12.345 to the nearest multiple of 10 (in this case, 10) and displays the result as “Original Number: 12.345” and “Rounded Number: 10” in a message box.

Here is the full code:

Sub RoundUpExample5()

    Dim originalNumber As Double

    Dim roundedNumber As Double

       originalNumber = 12.345

    roundedNumber = RoundUp(originalNumber, -1)

       MsgBox “Original Number: ” & originalNumber & vbCrLf & “Rounded Number: ” & roundedNumber

End Sub

Example #6 – When the Second Argument is -2

With a second argument of -2, the VBA RoundUp function rounds 12.345 up to the nearest hundred, yielding 100.0.

Step 1: In this instance, we apply the VBA RoundUp function with -2 specified as the precision for rounding.

When -2 is used as the second argument in the VBA RoundUp function, it rounds the number up to the nearest multiple of 100, as 10 is raised to the absolute value of -2 is 100.

Step 2: When you execute this code, it rounds the original number 12.345 up to the nearest multiple of 100, resulting in “Original Number: 12.345” and “Rounded Number: 100” displayed in a message box.

Here is the full code:

Sub RoundUpExample6()

    Dim originalNumber As Double

    Dim roundedNumber As Double

       originalNumber = 12.345

    roundedNumber = RoundUp(originalNumber, -2)

        MsgBox “Original Number: ” & originalNumber & vbCrLf & “Rounded Number: ” & roundedNumber

End Sub

Example #7 – When the Second Argument is -3:

In this case, the VBA RoundUp function rounds 12.345 up to the nearest thousand, resulting in 1000.0.

Step 1: In this context, we use -3 as the second argument in the VBA RoundUp function, it rounds the number up to the nearest multiple of 1,000, as 10 raised to the absolute value of -3 is 1,000.

Step 2: When you execute this code, it rounds the original number 12.345 up to the nearest multiple of 1,000, resulting in “Original Number: 12.345” and “Rounded Number: 2,000” displayed in a message box.

Here is the full code:

Sub RoundUpExample7()

    Dim originalNumber As Double

    Dim roundedNumber As Double

       originalNumber = 12.345

    roundedNumber = RoundUp(originalNumber, -3)

       MsgBox “Original Number: ” & originalNumber & vbCrLf & “Rounded Number: ” & roundedNumber

End Sub

Important Things To Note

1. The VBA RoundUp function expects a numeric expression as its first argument. Ensure that the variable or value you provide is of a numeric data type (e.g., Double, Integer).
2. When a negative number is the second argument, the RoundUp function rounds it up to the nearest power of 10 corresponding to the absolute value of the second argument.
3. Be aware that the VBA RoundUp function does not provide precise control over rounding. It rounds to the specified number of decimal places but does not guarantee a specific number of decimal places in the result.
4. Consider how the VBA RoundUp function behaves with very large or very small numbers. Rounding can have unexpected results when dealing with extreme values.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to VBA RoundUp. Here we explain the How To Use VBA RoundUp Function along with examples & downloadable excel template. You can learn more from the following articles –

• VBA End
• VBA StrConv
• VBA Like