VBA round() function and worksheetFunction.Round() returning different results

[gtwo]

Does anyone know why the following happens?

In vba...

MsgBox Round(0.25, 1) 'shows 0.2
MsgBox worksheetFunction.Round(0.25, 1) 'shows 0.3

wtf is happening here?

 

[Rick Rothstein]

With one exception, all rounding in VBA, whether explicit (such as the Round function) or implicit (such as assigning a floating point number to an integer variable) use what is known as Banker's Rounding (if the rounding position is exactly a 5, then it is rounded to the nearest even number (in your case, the 2 is the nearest even number). Remember I said there was one exception.... it is the Format function which rounds like Excel does (5's always round up). So, instead of using this...

MsgBox Round(0.25, 1)

use this...

MsgBox Format(0.25, "0.0")

 

[gtwo]

Thank you for the quick answer, I didn't know vba Round function worked like that, and that is a very important thing for what I'm doing. I'll remember that, thank you again!

 

[Rick Rothstein]

You are welcome. By the way, I probably should have mentioned that you can call out directly to Excel's ROUND function using the WorksheetFunction object (but I think the Format function is slightly more efficient) like this...

MsgBox WorksheetFunction.Round(0.25, 1)

Doing it this way is a little more self-documenting as you "know" you are deliberately rounding... that might not be as clear to others reading your code if you use the Format function to do the rounding (although I guess you could add a comment saying so).

 

[gtwo]

I just replaced everything from " Round(" to " WorksheetFunction.Round(", that way it works as I need it, and it was easier to replace everything for sure, I had almost 50 lines of code with with the VBA Round function, and that could have caused some problems. Even though it is only me who currently work in code, maybe one day another person might touch it, and the "Format( n, "0.0")" might look like something else for someone who is not aware of the Banker's Rounding, at least That's what I think.