Introduction | Example | Tutorial | Applications

Introduction - VBA RoundUp

The VBA RoundUp function is actually not a VBA function at all. It’s an Excel Worksheet Function that you call by using the WorksheetFunction object. By calling the RoundUp worksheet function from VBA, you’ll be able to round up your numbers to however many digits you like, just like in Excel.

Earlier this month, I taught you everything you need to know about the VBA Round function. Today I’m going to show you how to use the RoundUp function in your VBA macros.

Example - VBA RoundUp

VBA RoundUp to Round Numbers Up

Sub VBA_RoundUp()
Dim d1 As Double, d2 As Double
d1 = 1.3
d2 = Application.WorksheetFunction.RoundUp(d1, 0) 'equals 2
End Sub

Make powerful macros with our free VBA Developer Kit

Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.

I'll take a free VBA Developer Kit

This is a basic macro. It sets the variable d1 equal to 1.3, then calls the RoundUp function via the Application.WorksheetFunction object. The final result is that 1.3 is rounded up to 2.

If you’re ready to learn more, the following section shows you how to use RoundUp, including an interesting discussion on the second argument.

Tutorial - VBA RoundUp

RoundUp Arguments

Just like the VBA Round function, the RoundUp function accepts two arguments.

  • The first argument is the number you want to round.
  • The second argument is the number of places you want to round to.

Both arguments are required.

Notice I didn’t say the “number of decimal places” you want to round to when describing the second argument. That’s because the second argument has some interesting behavior.

RoundUp Second Argument

If the second argument equals 0, the number is rounded up to the nearest integer. This what I demonstrated in the example macro.

If the second argument is greater than 0, the number is rounded up to the specified number of decimal places AFTER the decimal.

If the second argument is less than 0, the number is rounded up to the specified digit BEFORE the decimal point.

That sounds unusual, right? Let’s take a look at some examples to clear things up.

More RoundUp Examples

RoundUp After the Decimal Point

Example 1
d2 = Application.WorksheetFunction.RoundUp(2121.3242, 1)

The variable d2 is 2121.4

Example 2
d2 = Application.WorksheetFunction.RoundUp(2121.3242, 2)

The variable d2 is 2121.33.

Example 3
d2 = Application.WorksheetFunction.RoundUp(2121.3242, 3)

The variable d2 is 2121.325.

In each instance, the variable was rounded up to the specified decimal point.

RoundUp Before the Decimal Point

This is where things get interesting.

Example 1
d2 = Application.WorksheetFunction.RoundUp(2121.3242, -1)

The variable d2 is 2130. Let’s explain. Pretend you move the decimal one place to the left, then you round the number to the left of this new decimal point up. In other words, the first digit to the LEFT of the new decimal point is rounded up from 2 to 3, yielding 2130.

Example 2
d2 = Application.WorksheetFunction.RoundUp(2121.3242, -2)

The variable d2 is 2200. The pattern continues. Pretend you move the decimal places two places to the left, then round up to the next integer. The 1 becomes a 2 so your inal answer becomes 2200.

Example 3
d2 = Application.WorksheetFunction.RoundUp(2121.3242, -3)

The variable d2 is 3000.

That’s pretty cool, isn’t it? Since the VBA RoundUp function is really an Excel worksheet function, the same rules apply when you call the RoundUp function in your spreadsheet. Perhaps it’s common knowledge, but I’m here to admit that until recently, I had no idea you could round numbers to the LEFT of the decimal point in Excel.

Application Ideas

I rarely find a need to always round a number up, but I want to know what applications you all are doing where you need to round up. Let me know in the comments section!

That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.