The VBA function "Round"

[Rank]

Hi!

I discovered this lately and wanted to share it with other VBA programmers!

First, I think that the best way to explain it is to demonstrate what I'm talking about, so, simply copy-paste the code below in a Excel Module and run it:

Sub test()
   Dim d As Double
   Dim i As Integer
   
   'VBA round function
   For i = 1 To 5
      d = i + 0.5
      MsgBox "VBA Round of " & d & " is " & Round(d, 0)
   Next i
   
End Sub

You will see that 1.5=2; 2.5=2; 3.5=4 and 4.5=4...
since when round of 2.5 is 2 ????

Ok, then, now, replace the "Round" by "Application.WorksheetFunction.Round":

   'Worksheet round function
   For i = 1 To 5
      d = i + 0.5
      MsgBox "Excel Round of " & d & " is " & Application.WorksheetFunction.Round(d, 0)
   Next i

Now, you see that 1.5=2 and 2.5=3...
that is correct!

I've also checked VB6 and there's the same problem...


So, if you're using that function, be aware that it appears to be uncorrect...

 

[SydneyGeek]

Thanks for the heads-up.

I think I read somewhere that ROUND in VBA rounds towards 0 (ie down for +ve, up for -ve) whereas in Excel it always rounds up. I'll see if I can track the reference down.

Denis