+ Reply to Thread
Results 1 to 5 of 5

Worksheet rounding vs VBA rounding

  1. #1
    Simon Cleal
    Guest

    Worksheet rounding vs VBA rounding

    Excel 2K

    The VBA Round function uses 'Bankers rounding' (half the time the .5 is
    rounded up, half the time down).

    The worksheet ROUND() function uses 'normal rounding' (.5 is always rounded
    up)

    Is there an easy way to make the VBA rounding the same as the worksheet
    rounding

    Thanks in advance
    Simon


  2. #2
    Xcelion
    Guest

    RE: Worksheet rounding vs VBA rounding

    Hi Simon

    You could use the same worksheet round function invoked using
    WorksheetFunction.Round(arg1,arg2) in your VBA code

    Thanks
    Xcelion




    "Simon Cleal" wrote:

    > Excel 2K
    >
    > The VBA Round function uses 'Bankers rounding' (half the time the .5 is
    > rounded up, half the time down).
    >
    > The worksheet ROUND() function uses 'normal rounding' (.5 is always rounded
    > up)
    >
    > Is there an easy way to make the VBA rounding the same as the worksheet
    > rounding
    >
    > Thanks in advance
    > Simon
    >


  3. #3
    Bob Phillips
    Guest

    Re: Worksheet rounding vs VBA rounding

    Or you could cut your own

    Function myRound(num, Optional places As Long = 0)
    If Int(num) Mod 2 = 0 Then
    myRound = Round(num + 1 / (10 ^ places), places) - 1 / (10 ^ places)
    Else
    myRound = Round(num, places)
    End If
    End Function


    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Xcelion" <[email protected]> wrote in message
    news:[email protected]...
    > Hi Simon
    >
    > You could use the same worksheet round function invoked using
    > WorksheetFunction.Round(arg1,arg2) in your VBA code
    >
    > Thanks
    > Xcelion
    >
    >
    >
    >
    > "Simon Cleal" wrote:
    >
    > > Excel 2K
    > >
    > > The VBA Round function uses 'Bankers rounding' (half the time the .5 is
    > > rounded up, half the time down).
    > >
    > > The worksheet ROUND() function uses 'normal rounding' (.5 is always

    rounded
    > > up)
    > >
    > > Is there an easy way to make the VBA rounding the same as the worksheet
    > > rounding
    > >
    > > Thanks in advance
    > > Simon
    > >




  4. #4
    Simon Cleal
    Guest

    RE: Worksheet rounding vs VBA rounding

    Xcelion & Bob - Thanks for the alternative solutions



  5. #5
    okaizawa
    Guest

    Re: Worksheet rounding vs VBA rounding

    Hi,

    Format function also does the arithmetic rounding like the worksheet
    ROUND function. (except in a particular environment)

    MsgBox CDbl(Format(2.5, "0")) 'shows 3

    The Format() Function Gives Different Results in Windows XP Than in
    Windows 2000
    http://support.microsoft.com/kb/321047/en-us/


    the accuracy of round function for decimal number is not so good.

    MsgBox WorksheetFunction.Round(9000.92585, 4) 'shows 9000.9258
    MsgBox Round(0.00015, 4) 'shows 0.0001

    if you want such accuracy, you should think about a method that fits the
    range of numerical data. (as we won't test functions with all number.)
    for instance, if the minimum change is 0.00001,
    Int((9000.92585 + 0.00001 / 10) * 10000 + 0.5) / 10000 is sure to return
    9000.9259 even though a number has a small rounding error.

    --
    HTH,

    okaizawa

    Simon Cleal wrote:
    > Excel 2K
    >
    > The VBA Round function uses 'Bankers rounding' (half the time the .5 is
    > rounded up, half the time down).
    >
    > The worksheet ROUND() function uses 'normal rounding' (.5 is always rounded
    > up)
    >
    > Is there an easy way to make the VBA rounding the same as the worksheet
    > rounding
    >
    > Thanks in advance
    > Simon
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1