Negative Rounding in VBA

You can use negative numbers in the ROUND worksheet function to make numbers less precise, i.e.

=ROUND(56652,-3)

returns 57,000

I just learned that the Round method in VBA doesn’t have that feature. “Invalid procedure call or argument” it tells me. I could use the WorksheetFunction object and the Round method that comes with it, but instead I used integer division:

Me.tbxContract.Text = Format((Val(Me.tbxContract.Text) 1000) * 1000, “$#,##0.00”)
Posted in Uncategorized

10 thoughts on “Negative Rounding in VBA

  1. Unless I’m mistaken, I don’t believe your integer division is rounding – it’s truncating. So, when given 56652 is will return $56,000, not $57,000.

  2. Another option I’ve used that does actual rounding, not truncation:

    round((56662/1000),3)*1000

  3. I like functions for those things.

    Function VBRound(D As Double, L As Long) As Double
    Dim x As Double
    x = 10 ^ L
    VBRound = Int(D * x + 0.5) / x
    End Function

    Best wishes Harald

  4. If it only was that easy

    Harald’s function introduces a 6% upward bias

    We have 10 digits of which 0 doesn’t need rounding
    That leaves 9 digits which introduce an error when rounded.
    1 to 4 we round down
    6 to 9 we round up
    5 we have to assign a 50/50 chance to either up or down.

    Assuming a uniform distribution of digits we can look at the digit before the 5 and round up when this is an even digit or round down when this is an odd digit.

    Yes, I am aware that most of us remember rounding up from 5 from school math, but it introduces an upward bias (assuming uniformly distributed digits)

    Real life data may show different distributions of digits, leading the interesting discussions about pricing practices in supermarkets when at the till there will be a rounding to multiples of 5 cents.
    Of course there isn;t a problem in this case when we assume unifrom distributions.

    Cheers

    And thanks for this blog Dick

  5. Hi AmonRa

    My function introduces nada, it it a ROUND replacement. What you talk about is known as “bankers rounding” and if we want bankers rounding and think that ROUND stinks then we write a function that does bankers rounding instead of the stupid ROUND things. Like

    Function VBRound(D As Double, L As Long) As Double
    Dim x As Double
    x = 10 ^ L
    VBRound = CLng(D * x) / x
    End Function

    Best wishes Harald

  6. rounding! so many issues with roundng, there is a good few artiles on MSDN about it, VB and employ diffrent rounding methods, so just be careful!

  7. Harald’s formula is the one I tend to use but be aware it needs ammending if you are rounding negative numbers (instead of adding 0.5 you need to subtract it)

Leave a Reply

Your email address will not be published. Required fields are marked *