Excel & VBA Roundup Function

By Darrin Koltow

Learning how to use Excel's "Roundup" function from its built-in programming language VBA, or Visual Basic for Applications, helps you run any Excel function from VBA. This skill is useful because Excel has many functions that VBA doesn't have, and that you may need to apply from a VBA program. Note that the VBA feature that lets you tap into the "Roundup" function won't alert you to an incorrect syntax or argument, as it would when using the standard worksheet interface.

Purpose

The Excel "Roundup" function rounds a number upward to the number of digits you specify. For example, running "Roundup (2.2, 0)" tells Excel you want it to round the number 2.2 up to the next integer. The "0" in the second argument tells Excel you don't want any digits after the decimal point, which is equivalent to saying you want an integer answer.

VBA Round Function

VBA does not have a "Roundup" function built in. The closest function it has is the "Round" function, which rounds up or down depending on whether the ending digit in the number you're rounding is greater than or less than 5. For example, typing "Round (2.2, 0)" in the VBA "Immediate" window results in a 2, instead of the 3 that the Excel "Roundup" function would round up to.

"Formula" Property

Though VBA doesn't have a "Roundup" function of its own, it can use Excel's "Roundup" function. To instruct VBA to do this, set the "Formula" property of the "Range" virtual object to the "Roundup" function. The following example shows you how to do this. Open the VBA development environment by pressing "Alt" and "F11" simultaneously from within Excel. Click the mouse in the "Immediate' window, then type the following statement: "Range ("A1").Formula = "=Roundup (2.2, 0)"." Return to Excel using the "Alt-F11" keypress just described. You'll see the result "3" in cell A1, indicating your statement successfully applied Excel's "Roundup" function.

Interactive Example

You can make an interactive VBA program that accepts from a user the arguments for the "Roundup" function, and displays the result of that function. After opening the VBA development environment, paste the following program into the window:Public Sub roundUpANumber()Dim a1, a2, sa1 = CDbl(InputBox("Enter the number you'd like to round"))a2 = CInt(InputBox("Enter the number of decimals to which you'd like to round the number you just entered."))s = "=Roundup(" & a1 & "," & a2 & ")"Range("A1").Formula = sRange("A1").CalculateMsgBox (Range("A1").Value)End SubRun the program by clicking one of its statements, then clicking the "Run" menu's "Run" command. When Excel prompts you for the function's arguments, type them and press "Enter." Your program will display the result of the "Roundup" function for the arguments you entered.