How to Round a Number Using VBA

Written by shawn mcclain
  • Share
  • Tweet
  • Share
  • Email

Microsoft Excel 2010 comes bundled with a programming language called Visual Basic for Applications, or VBA. The VBA "Round" function will round numbers to the nearest integer, with the caveat that ".5" will sometimes be rounded up and sometimes be rounded down, in a process called banker's rounding. If you need your VBA code to always round up or round down, you'll have to call the worksheet function, as VBA doesn't have an inherent function to do this.

Skill level:
Moderate

Other People Are Reading

Instructions

    VBA Round Function

  1. 1

    Open the Excel 2010 worksheet that contains the VBA code you want to work with. Press "Alt" and "F11" to launch the VBA console.

  2. 2

    Click the module on the left side of the VBA console that holds the Excel macro you want to edit. Place your cursor on the code on the right side of the window, where you want to round a number.

  3. 3

    Enter the following code into the VBA console:

    x = round(y)

    Where "x" is a variable or function name in your code and "y" is a numeric variable or an actual number. You can also place a comma after "y" and input the number of decimal places you want to round to, so "round(223.446, 2)" will return a value of "223.45."

    Excel Worksheet Function

  1. 1

    Open the Excel 2010 worksheet that you want to work with. Press "Alt" and "F11" to open the VBA console.

  2. 2

    Click the module that you want to work with from the left side of the VBA console. Place your cursor into the code on the right side, wherever you want to round your number.

  3. 3

    Enter the following code into your VBA console:

    x = Application.WorksheetFunction.RoundUp(y, 0)

    In this code, "x" is equal to a variable or function name and "y" is a numeric variable or actual number. Unlike the VBA "Round" function, the number after the "y," which signals the number of decimal places to round to, is not optional. Finally, you can replace "RoundUp" with "RoundDown" if you need to round the number down.

Tips and warnings

  • When testing your code, you can use the "msgbox" function to see whether your rounding is correct. Directly after your rounding function, enter "msgbox x", where "x" is the variable that you rounded. A box will appear on the screen showing you what "x" is equal to, so you can see whether the code is rounding correctly.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.