2

I'm having an issue with the Round function in VBA. I'm aware that it uses Banker's Rounding but the results still seem inconsistent to me:

? Round(1.425, 2)
 1.42 
? Round(2.425, 2)
 2.42 
? Round(3.425, 2)
 3.42 
? Round(4.425, 2)
 4.42 
? Round(5.425, 2)
 5.42 
? Round(6.425, 2)
 6.42 
? Round(7.425, 2)
 7.42 
? Round(8.425, 2)
 8.43 
? Round(9.425, 2)
 9.43 
? Round(10.425, 2)
 10.42 
? Round(11.425, 2)
 11.42 
? Round(12.425, 2)
 12.42

If I understand Banker's Rounding, all of these should round to x.42 but 8.425 and 9.425 don't. This is in Excel 16.66 for Mac but I've verified it in other products as well.

EDIT

I confirmed the same behaviour occurs in C# as well when I use double data types. But when I use decimal, all is right again. Which leads me to believe this is an issue with floating points in VBA.

Kyle Baley
  • 580
  • 1
  • 5
  • 16
  • 1
    yup the same is true in vba: `? Round(cdec(9.425), 2)` returns `9.42` so you probably answered your own question. – Scott Craner Dec 29 '22 at 20:38
  • 1
    Please, check [here](https://en.wikipedia.org/wiki/Rounding) the rounding algorithm/philosophy and see [this](https://stackoverflow.com/questions/61762131/why-does-bankers-rounding-function-in-excel-vba-give-different-results-depending) thread for better understanding of your/VBA rounding problem... – FaneDuru Dec 29 '22 at 20:47
  • `Round` of VBA is known to be buggy. See a lot of examples as well as functions for high-precision rounding in my library at **GitHub**: [VBA.Round](https://github.com/GustavBrock/VBA.Round). – Gustav Dec 29 '22 at 23:00

0 Answers0