+ Reply to Thread
Results 1 to 4 of 4

VBA Not Following Consistent Rounding Rules?

  1. #1
    Registered User
    Join Date
    12-02-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    6

    VBA Not Following Consistent Rounding Rules?

    This Subroutine:
    Please Login or Register  to view this content.
    Seems to behave differently each time it is called from my main Loop. As an example the first loop finds:
    TotalTubes = 6
    TubeRows = 4
    TopRowTubeCount = 2
    Where 6/4 = 1.5 and is rounded up to 2

    The controlling variable in my code for Length is TopRowTubeCount and it is a Public Variable Dimensioned as an Integer

    However on the next loop it finds:
    TotalTubes = 210
    TubeRows = 4
    TopRowTubeCount = 52
    Where 210/4 = 52.5 and is rounded down to 52?

    I even used the round function for the TopRowTubeCount calculation and the erratic behavior continues. I know it's something simple I'm missing and I'm almost embarrassed to start a new thread for this purpose but whatever the cause it is stumping me.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: VBA Not Following Consistent Rounding Rules?

    You're not crazy. Integers in VBA use "Banker's Rounding" according to this MS article.
    How To Implement Custom Rounding Procedures

    Banker's Rounding
    When you add rounded values together, always rounding .5 in the same direction results in a bias that grows with the more numbers you add together. One way to minimize the bias is with banker's rounding.

    Banker's rounding rounds .5 up sometimes and down sometimes. The convention is to round to the nearest even number, so that both 1.5 and 2.5 round to 2, and 3.5 and 4.5 both round to 4. Banker's rounding is symmetric.

    In Visual Basic for Applications, the following numeric functions perform banker's rounding: CByte(), CInt(), CLng(), CCur(), and Round().
    The article offers alternatives.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VBA Not Following Consistent Rounding Rules?

    Hi

    Declaring an Integer variable is always going to avoid decimals and in any case you'll be at the mercy of the problems associated with arithmetic precision. Try Dim as Double and then round at the end.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    12-02-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: VBA Not Following Consistent Rounding Rules?

    Bizarre! I used the AsymArith Function from AlphaFrog's Link and just to be on the safe side dimensioned my numbers as Double and it fixed the issue. For reference the code looks like this:
    Please Login or Register  to view this content.
    It never ceases to amaze (and frustrate) me how taking simple things for granted can really affect how robust code is. Thank you both.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. conditional formatting rules for a cell on sheet 1 based on rules from sheet 2
    By jsard in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 08-15-2013, 09:22 AM
  2. [SOLVED] Outlook 2010 - Creating Rules - Rules Constant
    By Jack7774 in forum Outlook Formatting & Functions
    Replies: 3
    Last Post: 03-18-2013, 03:48 PM
  3. [SOLVED] How can I alter the rounding rules?
    By Greg in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-15-2006, 01:40 AM
  4. Modify Rounding Rules
    By NickDangr in forum Excel General
    Replies: 12
    Last Post: 02-09-2005, 03:06 PM
  5. How do I modify rounding rules in Excel?
    By merritaf in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-26-2005, 06:06 PM

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