Round Up in Excel VBA: Methods, Rules, and Examples

VBA & macros · Updated November 18, 2026

To round numbers up in Excel VBA using Excel’s familiar “away from zero” rule, call Application.WorksheetFunction.RoundUp(number, num_digits). For example, it can turn 2.31 into 2.4 at one decimal place; unlike VBA’s built-in Round function, it does not use banker’s rounding.

What “round up” means in Excel

Excel’s ROUNDUP function rounds a number away from zero at the requested digit. For a positive number, that means increasing its magnitude; for a negative number, it means making it more negative. This is different from rounding toward positive infinity, which would make a negative number less negative.

For instance, rounding 2.31 up to one decimal place gives 2.4, while rounding -2.31 up to one decimal place gives -2.4. At zero decimal places, 4.1 becomes 5 and -4.1 becomes -5. The rule applies even when the discarded digits are small: 7.001 rounded up to two decimal places is 7.01.

In VBA, the direct way to get this Excel behavior is to use the worksheet function through the WorksheetFunction object. It works in desktop Excel for Microsoft 365 on Windows and Mac. Excel for the web can display a workbook containing macros, but it cannot run VBA macros.

Choose the number of digits

The second argument, num_digits, tells Excel which place to round to. A positive value rounds to places right of the decimal point, zero rounds to an integer, and a negative value rounds to places left of the decimal point.

  • num_digits of 2: round up to the nearest hundredth.
  • num_digits of 0: round up to a whole number.
  • num_digits of -1: round up to the nearest ten.
  • num_digits of -2: round up to the nearest hundred.

For example, 126.2 rounded up with -1 becomes 130; -126.2 becomes -130. The method changes the numeric value, not merely how many decimal places Excel displays. If you only change a cell’s number format, the underlying value remains unchanged and calculations continue to use that full value.

Use the worksheet function from VBA

A VBA procedure can call Excel’s implementation directly. This is usually the clearest choice when you want the same behavior as the worksheet ROUNDUP function. The call returns a number, so you can assign it to a variable, write it to a cell, or use it in a larger calculation.

For example, with the value 2.31 stored in cell A1, the following VBA statement rounds it up to one decimal place and puts the result in B1:

Range("B1").Value = Application.WorksheetFunction.RoundUp(Range("A1").Value, 1)

B1 receives 2.4. To round to a whole number, pass 0 as the second argument; to round to the nearest ten, pass -1. Qualifying ranges with a worksheet, such as ThisWorkbook.Worksheets("Sheet1").Range("A1"), is safer in a larger project because it avoids acting on whichever sheet happens to be active.

Round a selected range with a macro

This macro rounds numeric cells in the current selection up to whole numbers. Change places to 1 for one decimal place, -1 for the nearest ten, or another integer as needed. It skips blank cells, text, and error values. It does change the selected cells themselves, including replacing formulas with their rounded results.

Sub RoundSelectionUp()
    Dim target As Range
    Dim cell As Range
    Dim places As Long

    places = 0 ' Change this to set the rounding position.

    If TypeName(Selection) <> "Range" Then Exit Sub
    Set target = Selection

    For Each cell In target.Cells
        If Not IsError(cell.Value2) Then
            If Len(cell.Value2) > 0 And IsNumeric(cell.Value2) Then
                cell.Value = Application.WorksheetFunction.RoundUp( _
                    CDbl(cell.Value2), places)
            End If
        End If
    Next cell
End Sub

Before running any macro that changes cells, save a copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, so a backup is the reliable way to recover the original values or formulas. The macro also treats dates as numbers; avoid including date cells in the selection, since rounding their serial values would change the dates.

Run the macro in desktop Excel

On Windows, open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, paste the procedure, and return to Excel. Select the cells, then run the macro from the editor or from Developer > Macros. The Developer tab may need to be enabled through File > Options > Customize Ribbon.

On a Mac, use Developer > Visual Basic to open the editor; the keyboard shortcut can depend on the keyboard and function-key settings. The VBA procedure itself is generally the same, though Mac keyboard commands and some Excel features differ. Excel for the web does not provide a VBA editor or run VBA; use desktop Excel for this task.

Macro-enabled workbooks should be saved as .xlsm if you want the VBA code to remain in the file. Only enable macros in workbooks you trust. Excel’s security prompts are there to help prevent unsafe code from running; do not lower macro security globally just to make one workbook work.

Worksheet formula versus VBA

If you do not need a macro, a worksheet formula is often simpler and preserves the original value in a separate column. With the number to round in A2, enter this in B2 to round it up to an integer:

=ROUNDUP(A2,0)

With the source number in A2, use this formula to round up to two decimal places:

=ROUNDUP(A2,2)

Copy the formula down alongside the data. If you later need fixed values rather than formulas, copy the result cells and use Paste Special > Values. A VBA macro is more useful when you need to repeat a transformation, process a selected range, or incorporate rounding into a larger automated task. A formula is easier to inspect and recalculate as source values change.

Do not confuse ROUNDUP with VBA Round

The name is a common source of mistakes: VBA’s built-in Round is not equivalent to Excel’s ROUNDUP. VBA Round uses round-half-to-even behavior at exact halfway cases, often called banker’s rounding. For example, a halfway value can round to the nearest even last digit. Excel’s worksheet ROUND uses a different rule for halfway cases, while ROUNDUP always moves away from zero.

There is also a practical type issue: VBA numeric values are stored using numeric data types, and decimal fractions such as 2.675 may not be represented exactly in binary floating-point. If a result appears surprising at a boundary, check the actual stored value and the chosen rounding function. For standard Excel “away from zero” behavior, calling WorksheetFunction.RoundUp is clearer than trying to reproduce it with VBA’s Round.

Common problems and checks

  • The result goes the wrong way for a negative number: Excel’s round-up rule moves away from zero, so -3.2 becomes -4 at zero digits. It does not mean “toward the larger number.”
  • Only the display changed: number formatting changes appearance, not the stored value. Use the function or macro when calculations must use the rounded result.
  • The macro overwrote a formula: writing to cell.Value replaces a formula with its result. Use a separate output range if formulas must be preserved.
  • The procedure stops on a value: invalid input, such as an error value or unsuitable argument, can cause a worksheet-function call to fail. The sample skips error cells and nonnumeric values, but it intentionally does not handle every possible data-cleaning requirement.
  • A number looks unchanged: the selected precision may not affect the displayed digits, or the cell format may hide the difference. Check the value in the formula bar and test a known example.

Frequently Asked Questions

How do I round up to two decimal places in Excel VBA?
Call Application.WorksheetFunction.RoundUp(value, 2). For example, 3.141 becomes 3.15. The second argument specifies the number of decimal places to retain.

Does VBA Round always round up?
No. VBA’s Round uses round-half-to-even at exact halfway cases. Use Excel’s WorksheetFunction.RoundUp when you specifically need numbers to move away from zero.

How can I round up to the nearest 10?
Pass -1 as the digit argument: Application.WorksheetFunction.RoundUp(value, -1). For example, 121 becomes 130 and -121 becomes -130.

Can I round up without changing the original cells?
Yes. Write each rounded result to another range or use a worksheet formula in a separate column. The sample macro writes back into the selection, so it overwrites values and formulas there.

Can I run Excel VBA in a browser?
No. Excel for the web can open and edit many workbook contents, but it cannot run VBA macros. Open the workbook in desktop Excel for Windows or Mac to use the Visual Basic Editor and run the macro.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.