VBA rounding off?

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
User avatar
Derek27
Posts: 23475
Joined: Wed Aug 30, 2017 11:44 am
Location: UK

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?
Capture.PNG
You do not have the required permissions to view the files attached to this post.
spreadbetting
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.
foxwood
Posts: 390
Joined: Mon Jul 23, 2012 2:54 pm

It's a known issue with VBA v Excel

This works around it in VBA to force using the Excel function instead

Code: Select all

? Application.WorksheetFunction.Round( 0.045, 2)
 0.05
User avatar
Derek27
Posts: 23475
Joined: Wed Aug 30, 2017 11:44 am
Location: UK

Thanks guys. I'm actually using Access but at least I know what's going on now. :)
spreadbetting
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 :)
User avatar
Derek27
Posts: 23475
Joined: Wed Aug 30, 2017 11:44 am
Location: UK

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.
foxwood
Posts: 390
Joined: Mon Jul 23, 2012 2:54 pm

If you just want to use the rounding in VBA itself then an old school rounding function will do it ...

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
which yields

Code: Select all

? MyRound( 0.045, 2 )
 0.05 
? MyRound( 0.9 * 0.05, 2 )
 0.05 
? MyRound( 0.044, 2)
 0.04 
 
User avatar
Derek27
Posts: 23475
Joined: Wed Aug 30, 2017 11:44 am
Location: UK

Cheers Foxwood.
User avatar
Derek27
Posts: 23475
Joined: Wed Aug 30, 2017 11:44 am
Location: UK

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!
foxwood
Posts: 390
Joined: Mon Jul 23, 2012 2:54 pm

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/ :lol:
User avatar
Derek27
Posts: 23475
Joined: Wed Aug 30, 2017 11:44 am
Location: UK

foxwood wrote:
Tue Jan 08, 2019 11:30 pm
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/ :lol:
I've got a lot of code to review. :lol:
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”