VBA rounding off?
Hi all. I'm aware that Excel rounds up with the spreadsheet function and uses banker's rounding (to the nearest even number) with the VBA 'Round' function, but can anybody explain why these two expressions produce different results?
You do not have the required permissions to view the files attached to this post.
-
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
It's all down to the way excel does calculations, so round(0.045,2) has no calculation and simply round to the even number cos the last number is a 5. With round(0.9*0.05,2) excel has to do the calculation 09*0.05 first. In order to do calculations excel converts the numbers to a binary format and then calculates the result, this usually leaves a load of trailing zeros and a 1 at the end which excel snips off. With you doing the calc inside the round function that end 1 is seen and the number gets rounded to 0.05, if that makes sense.
It's a known issue with VBA v Excel
This works around it in VBA to force using the Excel function instead
This works around it in VBA to force using the Excel function instead
Code: Select all
? Application.WorksheetFunction.Round( 0.045, 2)
0.05
-
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
If you want to avoid bankers rounding you either need to write your own function or start using the Application.WorksheetFunction roundup and rounddown like foxwood suggests. I always used to wonder why some of my simply calculations wouldn't give exact numbers and had to look up it myself years ago cos it bugged the hell out of me. There's an app I use at the moment that kicks out those ridiculous trailing numbers and it irks me everytime I see them
Thanks, I didn't realise there were round up and down functions.
Now I can see why some of my very old code is littered with Int(x+0.50001)s. Clearly I've had this problem before.
I think the simplest solution is to use excel.application.worksheetfunction as Foxwood suggested.
Now I can see why some of my very old code is littered with Int(x+0.50001)s. Clearly I've had this problem before.
I think the simplest solution is to use excel.application.worksheetfunction as Foxwood suggested.
If you just want to use the rounding in VBA itself then an old school rounding function will do it ...
which yields
Code: Select all
Function MyRound(ByRef x As Double, nDecDigits As Integer) As Double
' 08.01.19 BA forum Derek27
MyRound = Int(((x * (10 ^ (nDecDigits + 1))) + 5.000000001) / 10#) / 10 ^ nDecDigits
End Function
Code: Select all
? MyRound( 0.045, 2 )
0.05
? MyRound( 0.9 * 0.05, 2 )
0.05
? MyRound( 0.044, 2)
0.04
As a footnote, and it's taken me a while to figure it out, I have a textbox on an Access form that contains the value 0.9. The value was entered, not calculated. But when the value of the textbox is assigned to a variable in VBA it receives 0.899999976158142. But when I changed the variable type to currency it worked just fine.
Funny how the simplest things you could work out on a 1960's calculator can be the most time-consuming problems with Microsoft VBA!
Funny how the simplest things you could work out on a 1960's calculator can be the most time-consuming problems with Microsoft VBA!
Yup - that's where your original issue came from - floating point maths.
Currency type limits you to 4 decimal places btw but should calculate as expected.
More than you ever wanted to know about data types ... https://powerspreadsheets.com/vba-data-types/
Currency type limits you to 4 decimal places btw but should calculate as expected.
More than you ever wanted to know about data types ... https://powerspreadsheets.com/vba-data-types/
I've got a lot of code to review.foxwood wrote: ↑Tue Jan 08, 2019 11:30 pmYup - that's where your original issue came from - floating point maths.
Currency type limits you to 4 decimal places btw but should calculate as expected.
More than you ever wanted to know about data types ... https://powerspreadsheets.com/vba-data-types/