# 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. more rounding:
worksheetfunction.round(56652, -3)
or
56652 – 56652 mod 1000

2. 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.

3. Eric: No, you’re not mistaken. I’m using the term rounding rather carelessly here.

4. Chip says:

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

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

5. Harald Staff says:

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

6. AmonRa says:

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

7. Harald Staff says:

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

8. 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!

9. DaveB says:

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)

10. Hi!

I been using this code snippet for random numbers or a slightly changed one. One solution is to randomly set the number to a negative one or a positive one and the problem with getting negative random numbers is solved! Thanks for a interesting site!

http://vbaexcel.eu/vba-macro-code/rnd-random-function

Posting code? Use <pre> tags for VBA and <code> tags for inline.